ITDevSpace - Development Tips & Tricks

SQL Server Isolation Levels By Example

PRINT ' CREATE TABLE ' GO IF EXISTS ( SELECT * FROM dbo.sysobjects where id = object_id(N' [Table1] ') and OBJECTPROPERTY(id, N' IsUserTable ') = 1) DROP TABLE [ Table 1] GO CREATE TABLE [dbo].[ Table 1]( [Id] [ uniqueidentifier ] NOT NULL , [Name] [ varchar ](50) NULL , [DateUpdated] [ datetime ] NULL , CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [Id] ) ) GO GRANT SELECT , INSERT , UPDATE , DELETE ON [ Table 1] TO [SampleUser] GO PRINT ' CREATE STORED PROCEDURE ' GO IF EXISTS ( SELECT * FROM dbo.sysobjects where id = object_id(N' [StoredProcedure1] ') and OBJECTPROPERTY(id, N' IsProcedure ') = 1) DROP PROCEDURE [dbo].[StoredProcedure1] GO CREATE PROCEDURE [dbo].[StoredProcedure1] AS SET NOCOUNT ON UPDATE dbo. Table 1 SET [DateUpdated]=GETDATE() GO GRANT EXECUTE ON [StoredProcedure1] TO [SampleUser] GO PRINT ' CREATE VIEW ' GO ...

* Windows Authentication " Persist Security Info=False; Integrated Security=SSPI; Server={0}; Database={1}; Connect Timeout={2}; Application Name={3} " * SQL Server Authentication " Persist Security Info=False; Server={0}; Database={1}; Uid={2}; Pwd={3}; Connect Timeout={4}; Application Name={5} "

Install VS2010 SP1 and SQL CE Tools for Visual Studio Now you can add SQL Server Compact 4.0 Local Database to your project and connect to that database with ADO.NET Entity Data Model (Entity Framework 4). More info on ScottGu’s Blog

DECLARE @Products TABLE ( Product varchar (50), Category varchar (50), Price float ) INSERT INTO @Products SELECT ' Sugar ',' Groceries ',2.35 UNION SELECT ' Bread ',' Groceries ',1.09 UNION SELECT ' Ecomonist ',' Magazines ',4.99 UNION SELECT ' FT ',' Magazines ',2.99 SELECT Product,Category, SUM (Price) AS Price, COUNT (*) AS [ Count ] FROM @Products GROUP BY GROUPING SETS((Product),(Category),()) ORDER BY Product DESC , Category DESC , Price DESC , [ Count ] DESC SELECT Product,[Range], SUM (Price) AS Price, AVG (Price) AS AvgPrice FROM ( SELECT Product, Price, [Range] = CASE WHEN Price BETWEEN 0 AND 1 THEN ' 1$ ' WHEN Price BETWEEN 1 AND 2 THEN ' 2$ ' WHEN Price BETWEEN 2 AND 3 THEN ' 3$ ' WHEN Price BETWEEN 3 AND 4 THEN ' 4$ ' WHEN Price BETWEEN 4 AND 5 THEN ' 5$ ' END FROM @Products ) a...

DECLARE @ sp_helprotect TABLE ( [Owner] VARCHAR (255), [Object] VARCHAR (255) NULL , [Grantee] VARCHAR (255) NULL , [Grantor] VARCHAR (255) NULL , [ProtectType] VARCHAR (255) NULL , [ Action ] VARCHAR (255) NULL , [ Column ] VARCHAR (255) NULL ) INSERT @ sp_helprotect EXEC sp_helprotect SELECT * FROM @ sp_helprotect WHERE [Object] = ' table-name ' ORDER BY [Object], [Grantee], [ Action ]

This is improved version of the script from 2009 using try/catch syntax USE [MyDatabase] GO PRINT ' DROP PROCEDURE [usp_mysproc] ' GO if exists ( select * from dbo.sysobjects where id = object_id(N' [dbo].[usp_mysproc] ') and OBJECTPROPERTY(id, N' IsProcedure ') = 1) drop procedure [dbo].[usp_mysproc] GO PRINT ' CREATE PROCEDURE [usp_mysproc] ' GO CREATE PROCEDURE [dbo].[usp_mysproc] ( @RaiseError bit = 0 ) AS BEGIN TRY BEGIN TRAN IF @RaiseError = 1 BEGIN DECLARE @ TEMP TABLE ( Column 1 varchar (2)) INSERT INTO @ TEMP ( Column 1) VALUES (' 123 ') END COMMIT TRAN END TRY BEGIN CATCH DECLARE @ErrorInfo VARCHAR (4000) SET @ErrorInfo = ' ERROR [ '+LTRIM(STR(ERROR_NUMBER()))+' ][ '+ERROR_PROCEDURE()+' : '+LTRIM(STR(ERROR_LINE()))+' ] - ' + ERROR_MESSAGE() PRINT CONVERT ( VARCHAR ,GETDATE(),21) + ' : '+@ErrorInfo ROLLBACK...

select StoredProcedureName = p.name from sys.sql_modules sm inner join sys.procedures p on sm.[object_id] = p.[object_id] where [definition] like ' %Database_Object_Name% '

sp_helprotect [object_name] Returns a report with information about user permissions for an object, or statement permissions, in the current database. sp_helptext [view_name] Prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, computed column, or view. Full list of System Stored Procedures

C:\>sqlcmd -SLOCALHOST\SQLEXPRESS -Q " Select 'Hello World' "

DECLARE @sp_who2 TABLE ( SPID INT , Status VARCHAR (255) NULL , Login SYSNAME NULL , HostName SYSNAME NULL , BlkBy SYSNAME NULL , DBName SYSNAME NULL , Command VARCHAR (255) NULL , CPUTime INT NULL , DiskIO INT NULL , LastBatch VARCHAR (255) NULL , ProgramName VARCHAR (255) NULL , SPID2 INT , REQUESTID INT ) INSERT @sp_who2 EXEC sp_who2 SELECT * FROM @sp_who2 WHERE Status > 'BACKGROUND'

This is an example of how to use try/catch functionality in SQL Server 2005 to manage transactions. USE [MyDatabase] PRINT 'Moving Data' GO BEGIN TRY BEGIN TRAN PRINT 'Inserting..' INSERT INTO table2(Id,ParentId,Name) SELECT t1.Id, t1.ParentId, t1.Name FROM Table1 t1 WHERE t1.ParentId = 1 PRINT 'Deleting..' DELETE FROM [Table1] i WHERE t1.ParentId = 1 COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN END CATCH GO

This is the fastest way to compare 2 tables in SQL Server. Generate hash for both tables and see if it's the same. SELECT checksum_agg(checksum (*)) FROM table 1

This system tables allow you to see the list of views, tables, stored procedures in the database as well as information like creation, modify date. SELECT * FROM MyDb.sys.views SELECT * FROM MyDb.sys.tables SELECT * FROM MyDb.sys.procedures