Get Default Locations for Data, Log and Backup folders (all) 

declare @DefaultData nvarchar(512)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefaultData output

declare @DefaultLog nvarchar(512)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultLog output

declare @DefaultBackup nvarchar(512)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackup output

declare @MasterData nvarchar(512)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterData output

select @MasterData=substring(@MasterData, 3, 255)

select @MasterData=substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData)))

declare @MasterLog nvarchar(512)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg2', @MasterLog output

select @MasterLog=substring(@MasterLog, 3, 255)

select @MasterLog=substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog)))

select

    isnull(@DefaultData, @MasterData) DefaultData,

    isnull(@DefaultLog, @MasterLog) DefaultLog,

    isnull(@DefaultBackup, @MasterLog) DefaultBackup

https://stackoverflow.com/questions/1883071/how-do-i-find-the-data-directory-for-a-sql-server-instance


Get Default Locations for Data, Log and Backup folders (2012+)

select

    InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'),

    InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath')

Get most recent record

SELECT m1.date, m1.name, m1.qty, m1.created

FROM table AS m1

JOIN (

   SELECT date, name, MAX(created) AS created

   FROM table

   GROUP BY date, name

) AS m2 ON m1.date = m2.date AND m1.name = m2.name AND m1.created = m2.created

Identity INSERT ON OFF

SET IDENTITY_INSERT sometableWithIdentity ON

INSERT INTO sometableWithIdentity

    (IdentityColumn, col2, col3, ...)

VALUES

    (AnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT sometableWithIdentity OFF


https://stackoverflow.com/questions/7063501/how-to-turn-identity-insert-on-and-off-using-sql-server-2008


.