SQL Features & Versions

Sql Server 2000 :

• Version 8
• Query analyser & enterprise manager are seperate
• We can create 65535 databases only
• Datetime datatype used for both date & time

Sql Server 2005 :

• Version 9
• Query analyser & enterprise manager are combined as SSMS
• XML datatype is introduced
• We can create max (2^20-1) databases
• Ranking functions (Row_Number, Rank, Dense_Rank, Ntile for paging)
• varchar(max) datatype
• Exception Handling (Try catch block)
• Database mirroring
• CTE (Common table expression)
• Pivot, Unpivot
• Cube, Rollup, Grouping set
• Synonyms
• Bulk copy insert
• DDL triggers
• Table fragmentation
• Full text search
• Can compress the table & indexes  (In sql 2005 SP2)
• SSIS introduced
• Table datatype introduced

Sql Server 2008 :

• Version 10
• XML datatype used
• Initialize variables (Declare & initialize variable in single statement)
• Compound assignment operators (+=, -=, *=, /=, %=)
• Enhanced convert function (Conversion between binary & hexadecimal)
• Merge statement
• Filtered Index
• Change data capture
• Can encrypt entire db in 2008 introduced
• PBM (Policy based management) introduced
• CMS (Centralized management server) introduced
• Table datatype available
• Date, time,geospatial,timestamp introduced for different date time format

Sql Server 2008 R2 :

• Version 10.50
• PowerPivot for sharepoint, excel
• Multi-server administration & data-tier application
• Master data services introduced
• Extended protection to connect database engine

Sql Server 2012 :

• Version 11
• AlwaysOn Availability Groups HADR (High Availability and Disaster Recovery)
• Column store Index introduced
• User-defined server roles
• Sql Server data tool for BI
• Order by clause with Offset/fetch option
• New features to sql try_convert(), format()
• In-memory OLTP introduced

Sql Server 2014 :

• Version 12
• In-memory OLTP extended
• Column store Index extended
• Resource governor for I/O & I/O control
• Incremental statistics
• Most other features are related to Sql Azure

Sql Server 2016 :

• Version CTP3.2
• Integration with Hadoop
• Data masking
• Live execution plan
• Row level security
• Non Clustered Index Key length in SQL Server 2016 CTP 3.0 has been increased from 900 bytes to 1700 bytes

SQL Versions & Builds : 

	RTM (no SP)	SP1	SP2	SP3	SP4
SQL Server 2017      codename vNext	14.0.1000.169 *new
SQL Server 2016	13.0.1601.5	13.0.4001.0 or 13.1.4001.0	13.0.5026.0 or 13.2.5026.0
SQL Server 2014	12.0.2000.8	12.0.4100.1 or 12.1.4100.1	12.0.5000.0 or 12.2.5000.0
SQL Server 2012      codename Denali	11.0.2100.60	11.0.3000.0 or 11.1.3000.0	11.0.5058.0 or 11.2.5058.0	11.0.6020.0 or 11.3.6020.0	11.0.7001.0 or 11.4.7001.0
SQL Server 2008 R2      codename Kilimanjaro	10.50.1600.1	10.50.2500.0 or 10.51.2500.0	10.50.4000.0 or 10.52.4000.0	10.50.6000.34 or 10.53.6000.34
SQL Server 2008      codename Katmai	10.0.1600.22	10.0.2531.0 or 10.1.2531.0	10.0.4000.0 or 10.2.4000.0	10.0.5500.0 or 10.3.5500.0	10.0.6000.29 or 10.4.6000.29
SQL Server 2005      codename Yukon	9.0.1399.06	9.0.2047	9.0.3042	9.0.4035	9.0.5000
SQL Server 2000      codename Shiloh	8.0.194	8.0.384	8.0.532	8.0.760	8.0.2039
SQL Server 7.0      codename Sphinx	7.0.623	7.0.699	7.0.842	7.0.961	7.0.1063

All SQLServer service packs are cumulative, meaning that each new service pack contains all the fixes that are included with previous service packs and any new fixes.

Reference : 

SQL Server Builds

Wikipedia

Learn sql tips

windowsitpro

mcpmag

C-SharpCorner

infoworld

Recover Deleted data on Sql Server

Hi Today I will tell you how to recover the data in case data is deleted 
Please follow step by step & execute next step when first step will be finished.

Step 1 : Create a new fresh database and a table inside this database

USE master

GO

CREATE DATABASE TestDb
GO

USE TestDb
GO

CREATE TABLE TestTable (
 id INT identity(1, 1)
 ,NAME VARCHAR(50)
 ,Value INT
 ,ValueAt DATETIME DEFAULT(GetDate())
 )
GO

Step 2 : Create a Full Backup of this database

BACKUP DATABASE TestDb TO DISK = 'D:\TestDb_Full.bak'
WITH init
 ,format
 ,stats = 10
GO

Step 3 : Follow below DML On table dbo.TestTable

USE TestDb
GO

INSERT INTO TestTable ( NAME,Value)
SELECT 'Vimal' ,50
UNION
SELECT 'Kamal' ,250
UNION
SELECT 'Raj' ,1200
UNION
SELECT 'Swastika',150
UNION
SELECT 'Sibin' ,125
UNION
SELECT 'Manas' ,560
UNION
SELECT 'Amit' ,3200
GO

SELECT * FROM TestTable
GO

DELETE FROM TestTable WHERE id > 5
GO

SELECT * FROM TestTable
GO

 Step 4 : As data has been deleted. Now its time to recover that data.

Note : Here database is new & so as table. It is easy to recover. If you know the time of deleted data or approximate time you can recover data. But you will find it difficult if time range is too long or you forgot the time.
SELECT [Current LSN]
 ,[Transaction ID]
 ,[Operation]
 ,[Context]
 ,[AllocUnitName]
FROM fn_dblog(NULL, NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS'
 AND [AllocUnitName] = 'dbo.TestTable'

SELECT [Current LSN]
 ,[Operation]
 ,[Transaction ID]
 ,[Begin Time]
 ,[Transaction Name]
 ,[Transaction SID]
 ,[AllocUnitName]
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:00000343'--<<Paste the TransactionID
 AND [Operation] = 'LOP_BEGIN_XACT'

--Copy CurrentLSN 00000021:000000b0:0001
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x00000021', 1)) --Same Value
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x000000b0', 1)) --Total 10 digit preceeded by 0
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1)) --Total 5 digit preceeded by 0

-->33000000017600001
SELECT Cast(CONVERT(INT, CONVERT(VARBINARY, '0x00000021', 1)) AS VARCHAR(5)) + RIGHT('0000000000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x000000b0', 1)) AS NVARCHAR), 10) + RIGHT('00000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1)) AS NVARCHAR), 5) AS 'MarkPoint'
GO

Step 5 : Take a log backup now. For this recovery model of database should be Full or Bulk-logged(Right click on Database>>Properties>>Options>>Recovery Model)

BACKUP log TestDb TO DISK = 'D:\TestDb_log.trn'
GO

Step 6 : Restore Full backup of database
-- Starting first with restoring the FULL BACKUP with NORECOVERY
RESTORE filelistonly
FROM DISK = 'D:\TestDb_Full.bak';

RESTORE DATABASE [TestDb_New]
FROM DISK = 'D:\TestDb_Full.bak'
WITH MOVE 'TestDb' TO 'C:\TestDb.mdf'
 ,MOVE 'TestDb_log' TO 'C:\TestDb_log.ldf'
 ,REPLACE
 ,NORECOVERY;
GO

Step 7 : Restore log backup of database like below
RESTORE LOG TestDb_New
FROM DISK = 'D:\TestDb_log.trn'
WITH STOPBEFOREMARK = 'lsn:33000000017600001'
GO

Step 8 : Check the table data

USE TestDb_New
GO
SELECT * FROM dbo.TestTable

--Export the deleted rows from this table to original table

Step 9 : Drop tables

USE master
GO
DROP DATABASE TestDb
DROP DATABASE TestDb_New

-->The End<--

Note : Step 4 time range
SELECT [Current LSN]
 ,[Operation]
 ,[Transaction ID]
 ,[Begin Time]
 ,[Transaction Name]
 ,[Transaction SID]
 ,[AllocUnitName]
FROM fn_dblog(NULL, NULL)
WHERE --[Operation] = 'LOP_BEGIN_XACT'
 --and 
 [Begin Time] BETWEEN '2015/07/17 15:30:00:000'
  AND '2015/07/17 16:00:00:000'

Store a file & Retrive it in Sql Server

/* 
Created By : Vimal Lohani on 12-May-2015
*/
--Create Table 
create table #FileSaveTest (Files varbinary(max), name varchar(200))

--Insert file & information

insert into #FileSaveTest (Files, name)
select img.*, 'abc.jpg'
from openrowset(bulk 'D:\b\abc.jpg', Single_Blob) img

--Show table data
select *
from #FileSaveTest

--Configure advance options

sp_configure 'show advanced options', 1;
go

reconfigure;
go

sp_configure 'Ole Automation Procedures', 1;
go

reconfigure;
go

--Retrieve file at a location (Path)   
declare @FileData varbinary(max);

select @FileData = (
  select convert(varbinary(max), Files, 1)
  from #FileSaveTest
  );

declare @Path nvarchar(200)

select @Path = 'D:\a a\';

declare @Filename nvarchar(1024);

select @Filename = (
  select name
  from #FileSaveTest
  );

declare @FullPathToOutputFile nvarchar(2048);

select @FullPathToOutputFile = @Path + '\' + @Filename;

declare @ObjectToken int

exec sp_OACreate 'ADODB.Stream', @ObjectToken output;

exec sp_OASetProperty @ObjectToken, 'Type', 1;

exec sp_OAMethod @ObjectToken, 'Open';

exec sp_OAMethod @ObjectToken, 'Write', null, @FileData;

exec sp_OAMethod @ObjectToken, 'SaveToFile', null, @FullPathToOutputFile, 2;

exec sp_OAMethod @ObjectToken, 'Close';

exec sp_OADestroy @ObjectToken;

sp_configure 'Ole Automation Procedures', 0;
go

reconfigure;
go

sp_configure 'show advanced options', 0;
go

reconfigure;
go

--Drop table
drop table #FileSaveTest

Detach & Attach Procedure

/*
-->Created By- Vimal Lohani on 30-APR-2015
-->For successful run, Your sql server service account should have permission to old location & new location
-->Exec uspDetachAttach @DbName=<Database Name>, @NewLocationPath=<new Location for database like 'D:\Data'>
*/
CREATE PROCEDURE uspDetachAttach @DbName VARCHAR(200)
 ,@NewLocationPath VARCHAR(2000)
AS
BEGIN
 SET NOCOUNT ON

 DECLARE @Sql VARCHAR(max)
 DECLARE @OldLocation VARCHAR(2000)
 DECLARE @Size FLOAT
 DECLARE @max INT

 --Getting Database & Drive information
 SELECT ROW_NUMBER() OVER (
   ORDER BY fs.database_id
   ) 'RowNo'
  ,fs.database_id
  ,fs.size * 8.0 / 1024 size
  ,fs.physical_name 'OldLocation'
  ,reverse(left(reverse(fs.physical_name), charindex('\', reverse(fs.physical_name)) - 1)) 'FileName'
 INTO #temp
 FROM sys.databases db
 INNER JOIN sys.master_files fs ON fs.database_id = db.database_id
 WHERE fs.database_id NOT IN (
   1
   ,2
   ,3
   ,4
   ) --Not considered system databases
  AND db.NAME = @DbName

 SELECT @max = max(RowNo)
 FROM #temp

 IF (
   (
    SELECT Count(*)
    FROM #temp
    ) = 0
   )
 BEGIN
  PRINT 'database not exist so return'

  RETURN
 END
 ELSE
 BEGIN
  SELECT @Size = Sum(Size)
  FROM #temp

  DECLARE @drive TABLE (
   drive VARCHAR(2)
   ,MBfree FLOAT
   )

  INSERT INTO @drive
  EXEC master..xp_fixeddrives

  --Select * from @drive
  IF (
    (
     SELECT MBfree
     FROM @drive
     WHERE drive = SUBSTRING(@NewLocationPath, 1, 1)
     ) < (
     SELECT @Size
     )
    )
  BEGIN
   PRINT 'Size not available so return'

   RETURN
  END
  ELSE
  BEGIN
   --Detach database files
   SET @Sql = 'USE MASTER;
           ALTER DATABASE ' + @DbName + '
           SET SINGLE_USER
           WITH ROLLBACK IMMEDIATE;
           EXEC MASTER.dbo.sp_detach_db @dbname = N''' + @DbName + '''
           '

   EXEC (@Sql)

   -- Move MDF File from Loc1 to Loc 2
   DECLARE @file VARCHAR(2000)

   EXEC master.dbo.sp_configure 'show advanced options'
    ,1

   RECONFIGURE

   EXEC master.dbo.sp_configure 'xp_cmdshell'
    ,1

   RECONFIGURE

   IF ((Substring(REVERSE(@NewLocationPath), 1, 1)) <> '\')
    SET @NewLocationPath = @NewLocationPath + '\'
   SET @NewLocationPath = @NewLocationPath + @DbName + '\'

   EXEC master.dbo.xp_create_subdir @NewLocationPath

   WHILE (@max > 0)
   BEGIN
    SELECT @file = 'move "' + Cast(OldLocation AS VARCHAR(1000))
    FROM #temp
    WHERE RowNo = @max

    SELECT @file = @file + '" "' + @NewLocationPath + (
      SELECT FileName
      FROM #temp
      WHERE RowNo = @max
      )

    SET @file = @file + '"'

    PRINT @file

    EXEC master.dbo.xp_cmdshell @file

    SET @file = ''
    SET @max = @max - 1
   END

   -- Re-Attached DB
   SELECT @file = IsNull(@file, '') + '( FILENAME = N''' + @NewLocationPath + '' + FileName + ''' ),'
   FROM #temp

   SELECT @file = 'CREATE DATABASE ' + @DbName + ' ON ' + SUBSTRING(@file, 1, LEN(@file) - 1) + 'FOR ATTACH'

   PRINT @file

   EXEC (@file)

   SET @file = 'ALTER DATABASE ' + @DbName + ' SET MULTI_USER;'

   EXEC (@file)

   EXEC master.dbo.sp_configure 'xp_cmdshell'
    ,0

   RECONFIGURE

   EXEC master.dbo.sp_configure 'show advanced options'
    ,0

   RECONFIGURE
  END
 END

 DROP TABLE #temp
END

Comma separated values from sql table :

--Create temp table & Insert data-----
Create table #temp(id int, tech nvarchar(10))
Insert into #temp(id,tech) values(12, 'c#'),(12,'java'),(13,'sql'),(13,'Oracle'),(13,'Mysql'),(15,'hadoop')
Select * from #temp
--------------------------------------
--Process data------------------------
;WITH cte AS
(
SELECT DISTINCT id 
FROM #temp
)
SELECT Id, STUFF((SELECT ', ' + CAST(t2.tech AS NVARCHAR(MAX))
     FROM #temp t2 WHERE t1.ID = t2.ID
     FOR XML PATH('')),1,1,'') skills
into #temp1
FROM cte t1
--Getting Result---------------------
Select * from #temp1
--Drop temp tables-------------------
Drop table #temp
Drop table #temp1
-------------------------------------

sp_MSforeachtable Stored Procedure

sp_MSforeachtable stored procedure provided by Microsoft, is used for smooth processing on all tables of a single database. So why to use complex query when we have already that procedure.

How and when to use sp_MSFooreachtable :

Parameters

sp_MSforeachtable @command1, @replacechar, @command2,  @command3, @whereand, @precommand, @postcommand

• @command1 nvarchar(2000) – is the first command to be executed
• @replacechar nchar(1) – is a character in the command string that will be replaced with the table name being processed (default character is “?”)
• @command2 nvarchar(2000)-  additional command that can be run for each table. It runs after @ command1
• @command2 nvarchar(2000)-  another additional command that can be run for each table. It runs after @ comman2
• @whereand nvarchar(2000)- you can specify the where clause in this parameter
• @precommand nvarchar(2000) -  specifies a command to be run prior to processing any table
• @postcommand nvarchar(2000)- command to be executed  after all commands have been processed

Eg 1 : Retriving all tables starting with 'c' and their row count CREATE TABLE  #rowcount (tablename varchar(128), cnt int) EXEC sp_MSforeachtable  @command1 = 'Insert into #rowcount select ''?'',               count(*) from ?',  @whereand = 'and o.name like ''p%''',  @postcommand = 'SELECT * from #rowcount' DROP TABLE  #rowcount Eg 2:  Disabling all constraints on all tables

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Eg 3:  Enabling all constraints on all tables

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Eg 4:  Deleting rows from all tables

EXEC sp_MSForEachTable 'DELETE FROM ?'

Eg 5:  Reseed identity column

If any table doesn't contain identity column, it raise error and as a quality of stored procedure it will move on EXEC sp_MSforeachtable 'DBCC CHECKIDENT ( ''?'', RESEED, 0)' Eg 6:   Rebuild clustered index/all non clusted indexes on all the tables EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)'