SET TRANSACTION ISOLATION LEVEL

This statement is used to set the isolation level for either a connection or a stored procedure. The most typical use I’ve seen is at the top of a stored procedure in order to avoid locking and deadlocks. This is a cleaner alternative to using WITH (NOLOCK) hints on tables. If you set the isolation level within a procedure, the isolation level will revert to its previous level once the procedure finishes. The syntax is: [cc lang=”sql”] SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; [/cc] The available options are: READ UNCOMMITTED – Allows dirty reads, meaning data can be read from a transaction that is not yet complete. READ COMMITTED – Only reads data from transactions that have already been committed.  Current transactions must wait for transactions referencing common resources to finish. REPEATABLE READ – Data that is being read is exclusively locked until the transaction completes. SNAPSHOT – The default for Oracle.  This level allows all data to be read without using locks by maintaining a snapshot of all the data being modified in “versioning” tables.  This is the only isolation level not lock based. SERIALIZABLE – Data that is being read is exclusive locked and inserts are prevented within this range until the transaction completes. Example This statement needs to be placed before the transactions who isolation you want to modify. [cc lang=”sql”] SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT firstname ,salary FROM dbo.employee e JOIN salary s ON s.employeeid = s.employeeid; [/cc] The comparable method using hints would […]

Continue reading ...

How to Create a Type2 SCD (Slowly Changing Dimension)

This article could just as well be called creating a historical snapshot table. This type of table is also referenced as a dimension depending on what kind of data repository it’s located in. Personally, I prefer to keep a historical snapshot table in a normalized data store that contains history. This normalized data store is typically the first stopping point from the source system. It is useful because it keeps historical snapshots of what the data looked like in the source system at any point in time. To get started, let’s create a history table we will use to store the historical values. From above, we see that we have 4 additional columns: Person_HistoryID – this is a surrogate key specific to our new table. ChkSum – contains a CHECKSUM of all the columns used compare data discrepencies. DateTime_From – the beginning date in which this record is effective. DateTime_To – the ending date in which this record is no longer effective. First, let’s create our sample source table and populate it with some data [cc lang=”sql”] CREATE TABLE Person( PersonID int IDENTITY(1,1) NOT NULL, Title nvarchar(8) NULL, FirstName nvarchar(50) NOT NULL, MiddleName nvarchar(50) NULL, LastName nvarchar(50) NOT NULL, EmailAddress nvarchar(50) NULL, Phone nvarchar(25) NULL, ModifiedDate datetime NOT NULL ) SET IDENTITY_INSERT [dbo].[Person] ON INSERT [dbo].[Person] ([PersonID], [Title], [FirstName], [MiddleName], [LastName], [EmailAddress], [Phone], [ModifiedDate]) VALUES (1, N’Mr.’, N’Gustavo’, NULL, N’Achong’, N’[email protected]’, N’398-555-0132′, CAST(0x000096560110E30E AS DateTime)) INSERT [dbo].[Person] ([PersonID], [Title], [FirstName], [MiddleName], [LastName], [EmailAddress], [Phone], [ModifiedDate]) VALUES (2, N’Ms.’, N’Catherine’, N’R.’, […]

Continue reading ...

Using DBCC CHECKIDENT to Reseed a Table After Delete

I imagine you are just looking for simple syntax in order to reseed the identity column of a table you just deleted from. Here is the quick version: [cc lang=”sql”] DBCC CHECKIDENT(‘##reseed_example’, RESEED, @max_seed) [/cc] And here is an extended example: [cc lang=”sql”] — populate a table with identity SELECT ID = IDENTITY(int,1,1) ,name INTO ##reseed_example FROM dbo.sysobjects — delete some records DELETE FROM ##reseed_example WHERE ID > 5 — find the current max identity DECLARE @max_seed int = ISNULL((SELECT MAX(ID) FROM ##reseed_example),0) — use the current max as the seed DBCC CHECKIDENT(‘##reseed_example’, RESEED, @max_seed) — let’s test INSERT INTO ##reseed_example ( name ) SELECT ‘newobject’ — done SELECT * FROM ##reseed_example [/cc] it should be noted that in order to use DBCC CHECKIDENT you need to be dbo (db_owner). This does present an issue sometimes because often the reason the user is performing a delete instead of a truncate is because they do not have dbo rights. To overcome this, you can will need to create a procedure that that uses: WITH EXECUTE AS ‘dbo’. And reseed from there.

Continue reading ...

CROSS APPLY Explained

My first introduction to the APPLY operator was using the DMVs. For quite a while after first being introduced, I didn’t understand it or see a use for it. While it is undeniable that it is has some required uses when dealing with table valued functions, it’s other uses evaded me for a while. Luckily, I started seeing some code that used it outside of table valued functions. It finally struck me that it could be used as a replacement for correlated sub queries and derived tables. That’s what we’ll discuss today. I never liked correlated subqueries because it always seemed like adding full blown queries in the select list was confusing and improper. [cc lang=”sql”] SELECT SalesOrderID = soh.SalesOrderID ,OrderDate = soh.OrderDate ,MaxUnitPrice = (SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID) FROM AdventureWorks.Sales.SalesOrderHeader AS soh [/cc] It always seemed to me that these operations should go below the FROM clause. So to get around this, I would typically create a derived table. Which didn’t completely feel right either, but it was still just a bit cleaner: [cc lang=”sql”] SELECT soh.SalesOrderID ,soh.OrderDate ,sod.max_unit_price FROM AdventureWorks.Sales.SalesOrderHeader AS soh JOIN ( SELECT max_unit_price = MAX(sod.UnitPrice), SalesOrderID FROM Sales.SalesOrderDetail AS sod GROUP BY sod.SalesOrderID ) sod ON sod.SalesOrderID = soh.SalesOrderID [/cc] What made this ugly was the need to use the GROUP BY clause because we could not correlate. Also, even though SQL almost always generates the same execution plan as a correlated sub query, there were times when the logic […]

Continue reading ...

Indexed Views

To explain what an indexed view is, let’s first look at what constitutes a view. A view may sound like a fancy elaborate thing, however all it is, is a saved SELECT statement, nothing else. It is not explicitly compiled, nor does it contain any data. When you select from a view, it goes to the underlying tables and retrieves the data at the time it is called. An indexed view on the other hand, is a normal view that takes a copy of the underlying data it points to, and stores it locally. This way, the joins and aggregations that are processed at run-time in a normal view, are already materialized, so when queried, it’s as fast as querying a normal table. Therefore, another name for indexed view is “materialized view”. This is what it’s called in Oracle. Creating To make a normal view an indexed view, you need to do two things. First you need to enable SCHEMABINDING for the view. Schemabinding essentially locks the underlying DDL schemas for the tables that the view references. This prevents any DDL changes from being made to the referenced tables. If you want to make a change to the tables, you need to drop the view first. Let’s create this new view using the AdventureWorks Database: [cc lang=”sql”] CREATE VIEW Sales.OrderTotals WITH SCHEMABINDING AS SELECT SalesOrderID = soh.SalesOrderID, OrderTotal = SUM(sod.UnitPrice), OrderCount = COUNT_BIG(*) FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID GROUP BY soh.SalesOrderID [/cc] Okay, we’re half […]

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!
css.php