SQL

Replacing Enters

REPLACE(REPLACE(REPLACE(address, CHAR(13),' '), CHAR(10),' '), CHAR(9),' ')

Truncate Table

TRUNCATE TABLE TableName

Backup by using Query command

backup database OnTheBeachTransfers to disk='D:\Backup\Transfers20141023.bak'

To Get File Locations

EXEC sp_HelpDB 'DatabasaeName'

To Create Database Snapshot

 SELECT * FROM sys.database_files WHERE type_desc<>'LOG'

CREATE DATEABAE DatabaseName_ss ON 
(
NAME = DataabseName,     --> Read name from the command above
FILENAME = 'D:\Data\DatabaseName_ss.ss'
) AS SNAPSHOT OF DatabaseName;
GO

To Prove it's created

SELECT * FROM sys.databases

To Find out who are using database

EXEC sp_Who2

Get SPID from that query above and

To Kill SPID

KILL 41

Identity Insert

SET IDENTITY_INSERT [dbo].[SuperHeroes] ON
GO
INSERT INTO [dbo].[SuperHeroes] ( [SuperHeroID], [SuperHeroName], [FirstName], [LastName] )
SELECT [SuperHeroID] + 100, [SuperHeroName], [FirstName], [LastName]
FROM [dbo].[SuperHeroes]
WHERE [SuperHeroID] < 100
GO

SET IDENTITY_INSERT [dbo].[SuperHeroes] OFF
GO

Unfortunately there isn't any IDENTITY_UPDATE command you should insert new lines with desired id and delete the other one .

To Restore DB from Snapshot

USE [master]
RESTORE DATABASE DagaseName FROM 
DATABASE_SNAPSHOT = 'DatabaseName_ss';
GO

To Delete Snapshot

DROP DATABASE DatabaseName_ss

Rename Database with exclusively lock

USE [master];
GO
ALTER DATABASE foo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_renamedb N'foo', N'bar';

ALTER DATABASE foo 
SET MULTI_USER;
GO

Listing All Tables in a DB

SELECT id, name 
FROM SysObjects 
WHERE type = 'U' 
AND name LIKE '%Amen%'
ORDER BY name

Listing All Columns in a DB

SELECT Table_name, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS 	
WHERE COLUMN_NAME LIKE  '%Ameni%'
ORDER BY ORDINAL_POSITION

Setting query timers

SET STATISTICS IO ON    
SET STATISTICS TIME ON

Killing Users

exec sp_who
kill XXX

Find ByteSize of All the Tables in Database

SELECT CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables'
   ELSE ISNULL(sob.name, 'unknown') END AS Table_name,
   SUM(sys.length) AS Byte_LengthFROM sysobjects sob, syscolumns sysWHERE sob.xtype='u' AND sys.id=sob.idGROUP BY sob.nameWITH CUBE

Installing SQL Server Data Tools

[1]http://go.microsoft.com/fwlink/?LinkID=241405

Explanation [2]http://www.codeproject.com/Articles/451750/Installing-SQL-Server-Data-Tools-SSDT

Get Cumulative Totals - Running Totals

SELECT id,month
 , Amount
 , SUM(Amount) OVER (ORDER BY id) as total_sum
FROM bill