:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

  • Jan 23 / 2009
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

T-SQL script to list all languages and their IDs in SQL Server 2005 and SQL Server 2008

A DBA may be required LanguageID to query in system catalog and resources. For example in SQL Server 2000, to get complete default list of SQL Server error number, severity level and error description you have to use following command

/*
Get default list of SQL Server error numbers
and descriptions in SQL Server 2000
*/

USE Master
SELECT * FROM SysMessages
GO

While to get same list in SQL Server 2005 or 2008, you have to modify the command as

/*
Get default list of SQL Server error numbers
and descriptions in SQL Server 2005 and SQL Server 2008
*/

USE Master
SELECT * FROM SysMessages
WHERE MsgLangId = 1033
GO

So we have to provide the language ID in some cases. To get list of languages along with ID use following script

/*
T-SQL Script to list all languages
and their IDs in SQL Server 2005 and SQL Server 2008
*/

USE Master
SELECT MsgLangId, Alias
FROM Sys.SysLanguages
GO

LanguageList
You may further choose use full parameters from Sys.Languages table.

  • Nov 15 / 2008
  • 0
dbDigger, T-SQL Enhancements, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

Insert multiple rows by using a single T-SQL insert statement

Until SQL Server 2000 T-SQL, we have to use multiple insert statements for inserting multiple rows of data in a table. For SQL Server 2005 T-SQL we can insert multiple rows by using a single insert statement. And for SQL Server 2008 T-SQL we have two methods to insert multiple rows by using a single insert statement.
In following example we will go through the statements used in SQL Server 200 T-SQL, SQL Server 2005 T-SQL, SQL Server 2008 T-SQL to insert multiple rows by using single insert statement.

 --For SQL server 2000 and Above  
 USE DBName  
 GO  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FirstValue',1)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('SecondValue',2)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('ThirdValue',3)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FourthValue',4)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FifthValue',5)  
 GO  


 -- For SQL Server 2005 and Above  
 USE DBName  
 GO  
 INSERT INTO MyTable (col1, col2)  
 SELECT 'FirstValue' ,1  
 UNION ALL  
 SELECT 'SecondValue' ,2  
 UNION ALL  
 SELECT 'ThirdValue' ,3  
 UNION ALL  
 SELECT 'FourthValue' ,4  
 UNION ALL  
 SELECT 'FifthValue' ,5  
 GO  


 -- For SQL Server 2008 and Above  
 USE DBName  
 GO  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FirstValue',1),  
 ('SecondValue',2),  
 ('ThirdValue',3),  
 ('FourthValue',4),  
 ('FifthValue',5)  
 GO  
  • Nov 05 / 2008
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Beginner SQL Server Cheat Sheet for DB Administration

 -- Create PKey constrainton existing column  
 ALTER TABLE TableName  
 ADD CONSTRAINT PKeyName  
 PRIMARY KEY CLUSTERED (PKeyColumn)  
 GO  


 -- Create Fkey Constraint  
 ALTER TABLE FKeyTable ADD CONSTRAINT  
 FKeyName FOREIGN KEY (FKeyColumn)  
 REFERENCES PKeyTable (PKeyColumn)  
 ON UPDATE NO ACTION  
 ON DELETE NO ACTION  
 GO  


 -- Create check constraint  
 ALTER TABLE TableName WITH NOCHECK ADD CONSTRAINT  
 ConstraintName CHECK ( ConstraintExpression)  
 GO  


 -- Create Unique key constraint  
 ALTER TABLE TableName ADD CONSTRAINT  
 UKeyName UNIQUE NONCLUSTERED (col1,col2)  
 GO  


 -- Craete clustered/non clustered index  
 CREATE CLUSTERED INDEX IndexName ON TableName  
 (ColumnsHere) ON [PRIMARY]  
 GO  


 --Retreive data from excel file  
 select [Server], [Orphaned Logins]  
 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',  
 'Data Source= 'Path here';Extended Properties=Excel 8.0')...[Sheet1$]  
 order by [Orphaned Logins] asc

  
 -- Info about locks  
 sp_lock  
 SELECT * FROM sys.dm_tran_locks  
 SELECT * FROM sys.dm_exec_requests  


 -- Track active trans on SQL Server 2005 or 2008  
 SELECT * FROM sys.dm_tran_session_transactions  


 ---------List columns in table  
 SELECT Column_Name, Data_Type  
 FROM information_schema.columns  
 WHERE table_name = 'authors'  


 ----Current Connections to a DB  
 SELECT hostname, program_name,loginame,  
 cpu, physical_io, memusage, login_time,  
 last_batch, [status]  
 FROM master.dbo.sysprocesses  
 WHERE db_name(dbid) = 'DBNameHere'  
 ORDER BY hostname  


 -- identify longest open transaction  
 DBCC OPENTRAN('DBNameHere')  
 GO  


 -- get info about object  
 sp_help ObjectNameHere  


 -- Get list of all table valued UDF in DB  
 SELECT name, crdate  
 FROM sys.sysobjects  
 WHERE XTYPE = 'U'  
 --AF = Aggregate function (CLR)  
 --C = CHECK constraint  
 --D = Default or DEFAULT constraint  
 --F = FOREIGN KEY constraint  
 --L = Log  
 --FN = Scalar function  
 --FS = Assembly (CLR) scalar-function  
 --FT = Assembly (CLR) table-valued function  
 --IF = In-lined table-function  
 --IT = Internal table  
 --P = Stored procedure  
 --PC = Assembly (CLR) stored-procedure  
 --PK = PRIMARY KEY constraint (type is K)  
 --RF = Replication filter stored procedure  
 --S = System table  
 --SN = Synonym  
 --SQ = Service queue  
 --TA = Assembly (CLR) DML trigger  
 --TF = Table function  
 --TR = SQL DML Trigger  
 --TT = Table type  
 --U = User table  
 --UQ = UNIQUE constraint (type is K)  
 --V = View  
 --X = Extended stored procedure 
  • Oct 22 / 2008
  • 4
Date and Time, dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Get years, month and days difference togeather between two dates

Suppose you have two dates and you are required to get difference in following form
00 years, 00 months and 00 days.
Normally we may get year, month, days etc alone through
datediff(yy/mm/dd, firstDate, secondDate) function. But in this case we are required to get Year, month and days difference is required together.
So get date in above format use following T-SQL script

 
SELECT '2007-04-25' as [FirstDate], getdate() as [SecondDate],  
 CONVERT(varchar(6),datediff(yy,'2007-04-25',getdate())) +  
 ' Years, '+  
 CONVERT(varchar(4),datediff(mm, '2007-04-25',getdate())%12)+  
 ' Months and '+  
 CONVERT(varchar(4),datediff(dd, '2007-04-25',getdate())%30)+  
 ' Days' as [RequiredDifference]  
 GO  
  • Oct 15 / 2008
  • 0
dbDigger, Null Values, T-SQL Tips and Tricks

Using NULL in UNION clause of T-SQL

Question: What is the output of the following query?

 
SELECT 1  
 UNION  
 SELECT NULL  
 UNION  
 SELECT '1'  

Answer: NULL, 1

Explanation: UNION only takes distinct values and ‘1’ is implicitly converted to 1. So the NULL comes first and then 1 comes.

Ref: Union –

Note: The Question of day is taken from SQLServercentral.com. I want to compile selected QOD.

  • Oct 14 / 2008
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Basic T-SQL Refresher for preliminary learners

During the early days when i planned to permanently enter into Database Administration field, i searched a lot of preliminary books and code snippets. As T-SQL is core of SQL Server DBA, i spend most of time to cover the concepts and to gain as much skill as possible. During that time i prepared some code refreshers that i usually consulted later for practice of T-SQL. So primarily following code and explanation is for beginners and field switchers those want to strat careers in Database Administration. Most of concepts here will be helpful in iinterview questions and day to day T-SQL paradigm. I will try to update this post later also when required.
Code used here is run under pubs database and is compatible to SQL Server 2000 and SQL Server 2005.

 
Select 'Wellcome to T-SQL'  

 

Concatenation and alias
Concatenation is done using ‘+’. Alias is also used in two ways. Alias can be used in ‘select’ and ‘from’ statement

 
USE pubs  
 GO  
 SELECT au_fname+au_lname fullName, [STATE]+','+zip AS location  
 FROM authors auth  

Here in above script concatenation is used in select list. Alias is used in select list (fullname) and in from clause (auth)

 

Comparison on string data
Comparison operators can also be used with text/string data

 
SELECT [STATE]  
 FROM authors  
 WHERE [STATE] > 'ma'  

Above script will compare the given strings according to alphabetical order. It will return states that start with alphabets after ma.

 

IN operator
IN operator may be used instead of multiple OR

 
SELECT au_fname,au_lname, [STATE]  
 FROM authors  
 WHERE STATE IN ('TN','OR','MI','MD')  

 

Between Operator
Between operator may be used instead of > and <>

SELECT price  
 FROM titles  
 WHERE price BETWEEN 10 AND 20  

 

Wild cards
T-SQL uses two wild cards
‘%’ is used multiple match and ‘_’ is used for single char match

 
SELECT au_fname, au_lname  
 FROM authors  
 WHERE au_fname LIKE 'Abr%'  

Above script will look for names with Abr as first three characters and any characters after these three chars.

 
SELECT au_fname, au_lname  
 FROM authors  
 WHERE au_fname LIKE 'Ab_aham'  

Above script will look for names starting with Ab and ending with aham, and any character there in place of _.

 

Escape character
If we have to find % inside data, then use escape character. Normally @ is used

 
SELECT notes  
 FROM titles  
 WHERE notes LIKE '%@%%' ESCAPE '@'  

 

Pattern matching
Pattern matching may be used when we have a number of expected variations for search

 
SELECT au_lname, au_fname  
 FROM authors  
 WHERE au_fname LIKE '[l,m,s]%'  

Suppose we want to retrieve four letter name whose first char is capital and other are small

 
SELECT au_lname, au_fname  
 FROM authors  
 WHERE au_lname LIKE '[A-Z][a-z][a-z][a-z]'  

Suppose in a case sensitive column we want to retrieve book with title
“life without fear”, but do not know that without is capital or small

 
SELECT title_id, title  
 FROM titles  
 WHERE title LIKE '%[Ww][Ii][Tt][Hh][Oo][Uu][Tt]%'  

We want to retreive just those authors whose first name is 4 char long

 
SELECT au_fname  
 FROM authors  
 WHERE au_fname LIKE '____'  

We can use negate by using <>, !=, ^, not

 
SELECT au_fname  
 FROM authors  
 WHERE au_fname LIKE '[^][^][^][^]'  

You have a column that accepts just 6 chars while inserting data you have to be careful for signs and digits use ‘[A-z][A-z][A-z][A-z][A-z][A-z]’

 

Order by
Order by may be used with column names or numbers. Also we may use calculations for order criteria. Nulls will be placed at start or end of list as spcified by DBMS

 
SELECT au_fname+au_lname name  
 FROM authors  
 ORDER BY name  

 

Distinct
Distinct is used just after select key word. Only one distinct may be there per SQL statement. Will be applied to all columns in select list
Distinct performs like group by except it does not sort the data

 
SELECT DISTINCT [TYPE]  
 FROM titles  

 

group by
Group by works same as distinct. True power comes with aggregate functions like count(). An aggregate can not be specified in group by clause. when using group with aggregate, u have to specify all non aggregate. clolumns in group by clause

 
SELECT [STATE], COUNT(*)  
 FROM authors  
 GROUP BY [STATE]  

 

Having

  • Having clause works like where with a fundamental difference.
  • Where clause defines set of data on which grouping is perfomed.
  • Having clause defines which groups are going to be return to user. Having clause generally contains aggregates as part of selection criteria

 

Compute and compute by
Compute is used to generate summary data. It is backword compatibility. Analysis services roll up is advance to it. Following restrictions are applied.

  • U can use columns in select list only.
  • U must order by compute by column.
  • U can use any aggregate function except count(*).
  • Columns listed after compute by should be identical to those ordered in select clause
Consult us to explore the Databases. Contact us