Select the word and hit CTRL+SHIFT+U the SSMS immediately changes the selected word to UPPER CASE. Similar to convert cases to lower case hit CTRL+SHIFT+L.

Displaying Current Date and Time

SELECT CURRENT_TIMESTAMP
GO
SELECT {fn NOW()}
GO
SELECT GETDATE()
GO

CAST and Convert

Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression/ColumnName [ , style ] )

Shrinking Truncate Log File

USE DatabaseName
GO
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
GO

Restore Database Backup using T-Sql

Step 1: Retrive the Logical file name of the database from backup.

RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
GO

Step 2: Use the values in the LogicalName Column in following Step.

----Make Database to single user Mode

ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database

RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser mode.

If error occurs please execute following command it will convert database in multi user.*/

ALTER DATABASE YourDB SET MULTI_USER
GO

DECLARE @UserRoleName Table (UserID varchar(50), UserRole varchar(100))
INSERT INTO @UserRoleName
SELECT 'Vasanth','DotNet Developer' UNION ALL
SELECT 'Vasanth','Sql Developer' UNION ALL
SELECT 'Jayaseelan','DotNet Developer' UNION ALL
SELECT 'Jayaseelan','Appl Deployer' UNION ALL
SELECT 'Vinoth','DotNet Developer'

SELECT * FROM @UserRoleName



SELECT UserID, CAST(UserRole AS VARCHAR(100)) AS RoleName FROM @UserRoleName FOR XML PATH('')



SELECT UserID, STUFF((SELECT CAST(t2.UserRole AS VARCHAR(100)) AS RoleName FROM @UserRoleName t2 WHERE t1.Userid = t2.userID
FOR XML PATH('')),1,0,'') AS RoleDescription
FROM @UserRoleName t1 GROUP BY UserID



SELECT UserID, STUFF((SELECT ', ' + CAST(t2.UserRole as varchar(100)) FROM @UserRoleName t2 WHERE t1.Userid = t2.userID
FOR XML PATH('')),1,1,'') Role
FROM @UserRoleName t1 GROUP BY UserID

STUFF - Delete a specified length of characters and insert another set of characters at a specified starting point. eg.

SELECT STUFF('VASANTH', 3, 2, 'XYZ')

3 - Starting Position from where the Replace Starts
1 - upto Which position it needs to be replaced from '3'
'XYZ' - Replacing characters in that position

Result is : VAXYZNTH

REPLACE - Replace all occurrences of the second given string expression in the first string expression with a third expression. eg.

SELECT REPLACE('VASANTH', 'A', 'KZT')

Result is : VKZTSKZTNTH

1. Intellisence Enhancements (IDE, Lookup will come automatically)
2. Declaration and Initialization in Same Line (Instead of DECLARE and SET in seperate Statement).
3. Compound Operators : (+=, -+, *=, /=....)
4. Multiple Value Insert in single Insert Statement
5. Grouping Sets (Grouping in Different Select stament is merged with one).
6. User-Defined Table Type and Table-Value Parameter (TVP)
7. New Date and Time Data Types
8. New Datatype DateTime2, DateTimeOffSet
9. HirerachyID

Ref : http://www.sqlservercentral.com/articles/SQL+Server+2008/65539/

Declare @Sql nvarchar(max)
Declare @cnt varchar(10)
Declare @TableName as varchar(50)
SET @TableName = 'temp'

SET @Sql = 'Select @Cnt = count(*) From ' + @TableName
--PRINT @Sql
EXEC sp_executeSql @Sql, N'@Cnt varchar(10) output', @Cnt output
Select @Cnt