Someone asked me the other day what blogs I follow. I guess I don’t follow too many, but if I had to recommend some, Aaron Bertrand has a really good blog and covers some good stuff. Tim Mitchell is a good friend and someone I consider a cool dude, and Jamie Thomson always posts some great things for the community. I guess I also go over to SQLSkills.com every so often…otherwise I don’t follow too many blogs. I use them as references like most folks, so I don’t get up in the morning and peruse their pages per se. Blogs that contain notes from the field, or other development experiences are good references, but if I want information, I still recommend MSDN or TechNet for the definitive and authoritative word on SQL Server.
Anyway, I happened to catch one of a little series on Aaron’s blog on “Bad Habits to Kick” this morning. It’s a good series and I recommend that you check it out. I add one that he did not mention. Here is his code:
USE test
GO
SELECT 1 AS x; -- #1
SELECT x = 1; -- #2
SELECT 1 'x'; -- #3
SELECT 1 x; -- #4
Here’s one that I really don’t care to see:
SELECT 1 "x"
Mostly, though, #4 is one I really have a problem with. I really don’t like alias just sort of stuck out there; it makes the code really hard to read without an AS. This convention screams the 2-4 year TSQL guy in my opinion. I used to do little ‘short cuts’ like these, but that was back in the day. A seasoned TSQL coder a more strict set of coding standards to avoid little errors later on down the road. And, enjoy less-frequent errors in his or her code as well.
Here is an argument to add an AS anytime a column is referenced, even if the alias is the same name as the column. Consider the following:
DROP VIEW vwMyView
GO
CREATE VIEW vwMyView
AS
SELECT TABLE_CATALOG
,TABLE_NAME
,ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
GO
Looks pretty good, but what if someone added a column but did not add aliases:
ALTER VIEW vwMyView
AS
SELECT TABLE_CATALOG
,TABLE_NAME
,ORDINAL_POSITION
COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
GO
SELECT * FROM vwMyView
Notice that it accidentally thinks that the third column, ORIDINAL_POSITION, alias is actually a column that was added to the SELECT statement. The programmer forgot to add a comma so it aliases the column as COLUMN_DEFAULT. I have run into this before (just last week) with a view that included many columns. If AS was used for each, it probably would not have happened:
Little things like this keep the phone from ringing at 2:00 am in the morning, and because as data folks we have to deal with problems all of the time, creating even more problems is something I try to avoid.
Thanks for reading, Lee
----------------------------
Reference: http://sqlblog.com/blogs/aaron_bertrand/archive/2012/01/23/bad-habits-to-kick-using-as-instead-of-for-column-aliases.aspx
Posted in:
Beginner ,
TSQL Tags:
804026e4-6ab6-4834-8e15-5b74e7363c7b|0|.0
An idea came over me last night…back over two years ago I wrote a few blogs on table variables, and was using one the other day. I remembered that they have a problem in some instances with transactions, and could possibly not be ‘rolled back’. It states in Books Online that the duration is short, but it doesn’t make mention of the possibility of persisting values if a rollback occurred. In the below example, you see that the value inserted into a table variable rolls back on an insert, but in this situation you can see that you would not want to SELECT or report from a table variable, or use one as an OUTPUT variable to a stored procedure. Let’s check it out. Run the following - let’s create a table: CREATE TABLE CheckingAccount
(id int identity (1,1) NOT NULL PRIMARY KEY
,fkCustomerID int NOT NULL
,Balance money NOT NULL DEFAULT (0)
,LastTransDate datetime2 DEFAULT GETDATE()
);
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Now we’ll create a stored procedure to set up the experiment:
CREATE PROCEDURE proc_InsertDeposit
@fkCustomerID int
,@Amt money
,@AmtProcessed money OUTPUT
AS
SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN
DECLARE @insertDeposit TABLE (fkCustomerID int, Amt money)
INSERT @insertDeposit values (@fkCustomerID, @Amt)
-- DO SOME OTHER PROCESSING
INSERT INTO CheckingAccount (fkCustomerID, Balance)
SELECT fkCustomerID, Amt
FROM @insertDeposit
ROLLBACK
SELECT @AmtProcessed = Amt from @insertDeposit
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
ROLLBACK TRAN
END CATCH
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Notice that if you run this procedure the rollback will remove the record from the CheckingAccount table via the ROLLBACK. However, the @AmtProcessed will get populated and not roll back because the ROLLBACK will not affect the table variable @insertDeposit. That’s the good news – I was thinking that it may actually not roll this back because the value is still available in the table variable. As you can see, however, I have an output variable based on the value @AmountProcessed; this will get returned even though the entire transaction has been rolled back and exited. Not good. Run the following:
SELECT * FROM CheckingAccount;
GO
DECLARE @AmountTransactionProcessed money
EXEC dbo.proc_InsertDeposit @fkCustomerID = 1, @Amt =5000, @AmtProcessed = @AmountTransactionProcessed OUTPUT
SELECT @AmountTransactionProcessed
GO
SELECT * FROM CheckingAccount;
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
So, for this example, it’s pretty easy to see that this isn’t a great way to get a return value. Let’s look at SQL Server Books Online for SQL Server 2008 R2 and look at example c in the reference to DECLARE @local_variable (Transact-SQL). Make the change to their code by adding a BEGIN TRAN as I have done here. I have added the code to mimic the table in the AdventureWorks database in case you do not have that installed on your computer or workstation:
CREATE DATABASE AdventureWorks
GO
USE AdventureWorks
go
CREATE SCHEMA HumanResources
go
DROP TABLE HumanResources.Employee
GO
CREATE TABLE HumanResources.Employee
(EmpID int
,BusinessEntityID int
,VacationHours int
,ModifiedDate datetime
)
GO
INSERT HumanResources.Employee
SELECT 1,100,20,GETDATE()
GO
BEGIN TRAN
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE()
OUTPUT inserted.BusinessEntityID,
deleted.VacationHours,
inserted.VacationHours,
inserted.ModifiedDate
INTO @MyTableVar;
ROLLBACK
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
In both examples, the value or values from the table variable persists beyond the end of the transaction rollback and is reported, although we know that it this case it becomes invalid. Be careful using output values with table variables in your coding.
Happy Table Variabling!
Lee Everest
------------------------
A picture of my ex-wife for you
8a4d9ffb-c7e4-4f85-bca4-925c03bf3568|0|.0
Just something a bit strange, when you insert a GUID into SQL Server, the letters are lower case. See the following. Fire up Visual Studio 2010 and run the following c# console app in debug mode:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer;
using System.Data.SqlClient;
namespace GUIDTest
{
class Program
{
static void Main(string[] args)
{
using (SqlConnection myConnection =
new SqlConnection("integrated security=SSPI;" +
"password=password;server=;" +
"Trusted_Connection=yes;" +
"database=test; " +
"connection timeout=30"))
{
try
{
myConnection.Open();
Guid param = Guid.NewGuid();
SqlCommand myCommand
= new SqlCommand ("INSERT INTO GUIDTest (id, guidcol) Values (1, @param)" , myConnection);
myCommand.Parameters.AddWithValue("@param", param);
myCommand.ExecuteNonQuery();
SqlCommand myCommand2
= new SqlCommand("SELECT guidcol FROM GUIDTest WHERE guidcol = '" + param + "'", myConnection);
// var value = myCommand2.ExecuteScalar();
Guid value = (Guid) myCommand2.ExecuteScalar();
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
}
}
}
}
Notice that the GUID string has letters and numbers, all letters are lower case for sure. If I select back out the same GUID that I generated and inserted, it looks like the statement still returns lower case letters in the GUID:
Now go into SQL Server and take a look at the representation for the GUID that is returned back.
Notice that the letters within the uniqueidentifier are capital letters now. Someone here suggested that the string representation is caps; however, if I cast the value to a uniqueidentifier I still get the same behavior
I’ve not worked much with GUIDS, and never really wanted to, but because I’m working on a database where they’re used quite a bit I have to learn to deal with them. Unfortunately one of the databases here is full of them…still looking for the idiot who designed this so I can punch them LOL. Goes to show that you have to learn it all because you never know what awaits you in a given environment.
This little phenomena could cause problems of course, and we had one here. We’re encrypting components within a credit card table, including the credit card and the account number, which is of uniqueidentifier type. So, what arose was that SQL Server dishes up the caps, but Visual Studio doesn’t, and in the encrypt/decrypt process, we found the obvious that these took on different values, so we had to make changes to the representation of the GUID. This may be all over Google, but I searched a few times and didn’t find much on it.
Thanks, Lee -------------------------
Maybe because everyone else is smart enough to stay away from the damn things
9c30c713-7be2-4188-93ea-631c59a57460|2|5.0
Sequences in SQL Server 2012 are new, but sequences have been an ANSI standard for a long time. I first worked with sequences in Oracle 8.0 back in 2001. Applications back then in SQL Server 6.5 and 7 oftentimes created a sequence table; each time you wanted an ‘identity’, you went to a homespun sequence table and added the value 1, for instance, to the current value, and then inserted. Oftentimes you had to do it in a certain way so that another transaction didn’t insert the same sequence number, or attempt to any way. So for instance, you fetch a sequence from a table, insert the value, and then go back and update the sequence table with the value that was inserted. Not terribly efficient or stealthy-sounding, huh? So enter Identity and all of its wonder and problems.
And, for SQL Server 2012, back to the future with Sequences. I’ve been dorking with them this morning, please follow along. I’ve created a database called Company:
CREATE SCHEMA Orders;
GO
DROP TABLE Orders.TblOrders;
GO
CREATE TABLE Orders.TblOrders
(PKOrderID int PRIMARY KEY
,FKCustomerID varchar(40) NOT NULL
,OrderQuantity int
,OrderDate datetime2 NOT NULL DEFAULT
GETDATE()
);
GO
CREATE SEQUENCE Orders.SqnceTblOrders
START WITH 1
INCREMENT
BY 1;
GO
I’ve created a schema, table, and a sequence with the above. Check Books Online for all of the sequence options. The essentials for making this thing work appear to be START WITH and INCREMENT BY.
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 133);
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity )
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 345);
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 111);
GO
SELECT * FROM Orders.TblOrders;
GO
First thing that I thought of was…remove the NEXT VALUE and simply throw it in a declare:
DECLARE @seqValue int = NEXT VALUE FOR Orders.SqnceTblOrders
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (@seqValue, 1, 111);
GO
SELECT * FROM Orders.TblOrders; GO
One thing that I don’t like is that sequence works like IDENTITY for values in a transaction; if it’s rolled back, when you insert again, it picks back up with the next value. Nope, don’t like that behavior. Run the following:
BEGIN TRANSACTION
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 133);
ROLLBACK
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 133);
SELECT * FROM Orders.TblOrders;
GO
If, however, you implement the above declare you could possibly save the value if some sort of retry logic is maintained:
DECLARE @seqValue int = NEXT VALUE FOR Orders.SqnceTblOrders
BEGIN TRANSACTION trnOrderInsert
INSERT Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (@seqValue, 1, 111);
SELECT * FROM Orders.TblOrders;
ROLLBACK TRAN trnOrderInsert
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (@seqValue, 1, 111);
SELECT * FROM Orders.TblOrders;
GO
How about more than one on a table? Looks like it works…
ALTER TABLE Orders.TblOrders
ADD RowNumber INT;
CREATE SEQUENCE Orders.SqnceRowNumber
START WITH 1
INCREMENT BY 1;
GO
DECLARE @seqValue int = NEXT VALUE FOR Orders.SqnceTblOrders;
DECLARE @RowNumber int = NEXT VALUE FOR Orders.SqnceRowNumber;
INSERT Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity, RowNumber)
VALUES (@seqValue, 1, 111, @RowNumber);
GO
SELECT * FROM Orders.TblOrders;
GO
Maybe as a constraint:
CREATE SEQUENCE Orders.SqnceSequence3
START WITH 1
INCREMENT
BY 1;
GO
ALTER TABLE Orders.TblOrders
ADD SequenceWithConstraint INT DEFAULT NEXT VALUE FOR Orders.SqnceSequence3;
GO
Now insert some data…looks like as a default constraint might be a cool way to throw a sequence in, but again this will lose a bit of control if you are going to use it with multiple tables via a variable as shown above.
DECLARE @seqValue int = NEXT VALUE FOR Orders.SqnceTblOrders;
DECLARE @RowNumber int = NEXT VALUE FOR Orders.SqnceRowNumber;
INSERT Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity, RowNumber)
VALUES (@seqValue, 1, 111, @RowNumber);
GO
SELECT * FROM Orders.TblOrders;
GO
One thing to consider maybe…it appears that there is no checking or pairing for a sequence – Books online states the following:
Sequences, unlike identity columns, are not associated with specific tables.
This means that outside of an application you better darn sure be careful which sequence you’re calling, because you can call whichever sequence that you may have previously created:
DROP TABLE #Test
GO
CREATE TABLE #Test
(ID int)
GO
INSERT INTO #Test (ID) VALUES (NEXT VALUE FOR Orders.SqnceTblOrders);
GO
SELECT * FROM #Test;
GO
There’s a big section in BOL with caching. Maybe we’ll look at that one next week.
Thanks for reading,
Lee
-----------------------------
Sequences and IDENTITY…have to keep giving Joe Celko something to bitch about, right?
Posted in:
SQL Server 2012 ,
TSQL Tags:
fc9b15d0-4063-4d89-aaec-13ea74539d11|0|.0
Making changes to an existing table – in this case NULLability – with indexes on the table and you may get the following error:
Msg 5074, Level 16, State 1, Line 2 The index 'idx_test' is dependent on column 'isDeleted'. Msg 4922, Level 16, State 9, Line 2 ALTER TABLE ALTER COLUMN isDeleted failed because one or more objects access this column.
In this instance, you’ll have to drop the index(es) referencing the column before you can alter the column. If you try to disable the index via ALTER INDEX indexname on table DISABLE, you’ll get the same error…no can do. Have to get rid of it first. Here’s an example:
DROP TABLE idxTest
GO
CREATE TABLE idxTest
(ID INT
,isDeleted BIT NULL
)
GO
INSERT idxTest VALUES (1,0)
GO
CREATE INDEX idx_test ON idxTest (ID, isdeleted)
GO
--ALTER TABLE idxTest
-- ALTER COLUMN isDeleted BIT NOT null
GO
DROP INDEX idxTest.idx_test
GO
ALTER TABLE idxTest
ALTER COLUMN isDeleted BIT NOT null
GO
CREATE INDEX idx_test ON idxTest (ID, isdeleted)
GO
Notice that I’ve commented out the ALTER TABLE, but you can uncomment and give it a try.
Thanks for reading, Lee
----------------------
40c699d0-a99d-4bec-b880-0b1d3be3cfb9|0|.0
Here are a few gotchas for IDENTITY property on columns…hopefully you find one that you didn’t know about. Did you know that if you try to set IDENTITY_INSERT on a table variable, it won’t work: DECLARE @tbl TABLE(id INT IDENTITY, DATA CHAR(1))
INSERT @tbl (id, data) VALUES (1,'a')
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Msg 1077, Level 16, State 1, Line 2 INSERT into an identity column not allowed on table variables.
The action does work on temp tables of course, both local and global temporary tables. If you try to set IDENTITY_INSERT for more than one table at a time it gives you an error:
USE tempdb
GO
IF OBJECT_ID('test1', 'U') IS NOT NULL
DROP TABLE test1
CREATE TABLE test1 (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY)
GO
IF OBJECT_ID('test2', 'U') IS NOT NULL
DROP TABLE test2
GO
CREATE TABLE test2 (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY)
GO
SET IDENTITY_INSERT test1 ON
GO
SET IDENTITY_INSERT test2 ON
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'tempdb.dbo.test1'. Cannot perform SET operation for table 'test2'.
Notice that if you SET IDENTITY_INSERT ON, you can’t tell if it’s already on; highlight the following and run this statement. I use the GO 100 to execute the batch 100 times:
SET IDENTITY_INSERT test1 ON
GO 100
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Beginning execution loop
Batch execution completed 100 times.
There’s nothing that indicates to me…”Hey you idiot, it’s already turned on”. Same way for off:
SET IDENTITY_INSERT test1 OFF
GO 100
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Beginning execution loop
Batch execution completed 100 times.
Same thing here. You don’t know whether it’s on or off by just running the command to set it ON or OFF.
Something to remember about IDENTITY_INSERT – don’t use this in some sort of online processing routine unless you are using it with temporary tables, because it only works for one table in the database. In tempdb, however, you can create tables within each SPID as well as use IDENTITY_INSERT in each as well. In other words, for temp tables, each connection allows you to use IDENTITY_INSERT. This may be handy for someone down the road.
I’ve looked high and low for any metadata or function that tells you what table IDENTITY_INSERT is set to within a database; can’t find one. I ran Extended Properties and didn’t' see it, and Googled it up as well. A few posts out there are also looking for one, but nobody seemed to find one either. Apparently there isn’t one, but I wrote a little procedure that might help tell you which table has the property set to ON. Run this:
CREATE TABLE t1 (id INT IDENTITY(1,1))
GO
SET IDENTITY_INSERT t1 on
GO
Now create the following. I’m capturing the message into a variable.
DROP PROC sp
GO
CREATE PROCEDURE sp
AS
IF OBJECT_ID ('dummy') IS NOT NULL
DROP TABLE Dummy
CREATE TABLE Dummy (ID INT IDENTITY)
BEGIN TRY
SET IDENTITY_INSERT Dummy ON
IF @@ERROR =0
SELECT 'No user table in database ' + DB_NAME() + ' is set to on.'
END TRY
BEGIN CATCH
DECLARE @St VARCHAR (MAX)=ERROR_MESSAGE()
SELECT 'Table with ident on is >>> : ' +
SUBSTRING(@St, PATINDEX ('%table%',@st ) , PATINDEX ('%Cannot%',@st )-PATINDEX ('%table%',@st))
END CATCH
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Now run this to see how it works:
EXEC dbo.sp
SET IDENTITY_INSERT t1 ON
GO
EXEC dbo.sp
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
This little proc tells you which table has it turned on; if it’s off it lets you know as well.
Finally, run this:
DROP TABLE t1
GO
CREATE TABLE t1 (id INT IDENTITY(1,1), Data CHAR(1))
GO
SET IDENTITY_INSERT t1 ON
GO
INSERT t1 (ID, Data) VALUES (-2000000, 'a')
SET IDENTITY_INSERT t1 OFF
GO
INSERT t1 (data) VALUES ('b')
GO
SELECT * FROM t1
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Notice that when you continue with IDENTITY insert, the next value inserted is 2, although you would think it would be a 1. Inserting –2000000 explicitly changes the order, and identity picks back up with 2.
Lee
-----------------------
ef1c57b8-95de-4c43-a65f-815768f14a54|0|.0
I noticed something strange in a trigger the other day…it had a reference to $rowguid; the reason it was strange to me was because I 1) know to never, ever use a uniqueidentifier on any of my tables, 2) know to never, ever put a trigger on a table, so therefore 3) never heard of the keyword. Well, maybe a trigger on occasion, but very rarely. Triggers are seldom needed, and if you’re on a team building a custom app, the business objects folks should be doing any triggering action in the middle layer, and not in the database in my opinion. Now, I’m not saying here that all stored procedures and logic need to be removed from the data layer. No sir. But that’s for another blog and discussion somewhere else down the line. What I am saying here is that I’ve been the data dude on custom app-dev projects over the years, and I tend to stay away from triggers if at all possible. Just my opinion of course, you use them as you see fit. In order to get the return GUID or uniqueidentifier that was just inserted into a table from a trigger, run the following example: USE tempdb
DROP TABLE triggerTest
GO
CREATE TABLE triggerTest
(ID INT
,UniqueIdent uniqueidentifier ROWGUIDCOL DEFAULT NEWID()
)
GO
CREATE TABLE logTable
(id INT IDENTITY
,TableName VARCHAR (100)
,UniqueIdent UNIQUEIDENTIFIER
,Date datetime DEFAULT GETDATE()
)
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Notice the ROWGUIDCOL – that is a necessary attribute to get the return uniqueidentifier back from the trigger. Check out Books Online for further reading on this.
Here’s the trigger example for this table:
CREATE TRIGGER [dbo].triggerTestInsert
ON dbo.triggerTest
WITH EXECUTE AS
CALLER AFTER INSERT
AS
SET NOCOUNT ON
BEGIN
INSERT dbo.logTable SELECT 'triggerTest', $rowguid, GETDATE() FROM inserted
END
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Insert a record and you will get the guid back from it. Again, check the insert and notice now that I am inserting a $rowguid value into the table; this represents the GUID that was inserted by the NEWID() function.
INSERT triggerTest (ID) VALUES (1)
GO
SELECT *
FROM logTable
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Looks pretty good. So in order to get a return value for the uniqueidentifier that was just inserted, as you would with SCOPE_IDENTITY() for IDENTITY, for instance, use the ROWGUIDCOL along with $rowguid to fetch the guid.
Thanks for reading,
Lee
----------------------
3d72d29d-4379-4d29-aceb-c22dc2096e60|0|.0
Fresh off of reading the great blog by the kind folks over at SQLCat, I thought first in using their discovery and experimentation for a slightly more practical use…trying to implement the consistency and richness of HASHBYTES over BINARY_CHECKSUM() for comparing rows in a table. (Their example is fine, but I’m not doing a lot of sharding and cloud stuff these days). Because BINARY_CHECKSUM() isn’t all that in SQL Server, this might work perfectly for this type of operation, so let’s check it out. Run the following script:USE tempdb
IF OBJECT_ID('HashbyteTest') IS NOT NULL
DROP TABLE dbo.HashbyteTest
GO
IF OBJECT_ID('HashbyteTest2') IS NOT NULL
DROP TABLE dbo.HashbyteTest2
GO
SELECT fileid, groupid, size, maxsize
INTO dbo.HashbyteTest
FROM sys.sysfiles
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Now I’m going to add a computed column using HASHBYTE, and create another table to do our data compare. Remember that SELECT INTO doesn’t take computed columns and push them forward, so I’m going to have to script it out to get HashbyteTest2 table like I want it:
ALTER TABLE HashbyteTest
ADD ColumnHash AS
HashBytes('MD5', CAST(fileid AS VARCHAR(4000)))
+HashBytes('MD5', CAST(groupid AS VARCHAR(4000)))
+HashBytes('MD5', CAST(size AS VARCHAR(4000)))
+HashBytes('MD5', CAST(maxsize AS VARCHAR(4000)))
GO
CREATE TABLE [dbo].[HashbyteTest2](
[fileid] [smallint] NULL,
[groupid] [smallint] NULL,
[size] [int] NOT NULL,
[maxsize] [int] NOT NULL,
[ColumnHash] AS
(((hashbytes('MD5',CONVERT([varchar](4000),[fileid],0))
+hashbytes('MD5',CONVERT([varchar](4000),[groupid],0)))
+hashbytes('MD5',CONVERT([varchar](4000),[size],0)))
+hashbytes('MD5',CONVERT([varchar](4000),[maxsize],0)))
) ON [PRIMARY]
GO
INSERT dbo.HashbyteTest2 (fileid, groupid, size, maxsize)
SELECT fileid ,groupid ,size ,maxsize
FROM dbo.HashbyteTest
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
I’m just adding the hashbyte result together. Also note that the data type for the column converts to VARBINARY, so if you decide to do this some other way using variables, you can use VARBINARY to stuff the values in.
Do our cool compare from a previous blog post and see how this one works using the ColumnHash:
SELECT MIN(fileid) AS fileid, ColumnHash
FROM
(SELECT fileid, ColumnHash
FROM dbo.HashbyteTest
UNION ALL
SELECT fileid, ColumnHash
FROM dbo.HashbyteTest2) X
GROUP BY ColumnHash
HAVING COUNT(1)=1
These are the same. So let’s update the data and then run the same script again:
UPDATE dbo.HashbyteTest2
SET groupid=-500
WHERE fileid=2
GO
The actual value changed, which you can see if you run only the Hashbyte function for the groupid column. Check the below comparison where I”ve highlighted the groupid hash:
F89CC14862CB876184FCDFD776B5C3DCB5E21872DAB26657D5FF90F89CC14862CCFCD208495D565EF66E7DFF9F98764DA26657D5FF90
Update it back and then run the function – looks pretty good again to me.
UPDATE dbo.HashbyteTest2
SET groupid=0
WHERE fileid=2
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Thanks for reading,Lee
----------------------
http://blogs.msdn.com/b/sqlcat/archive/2011/11/28/writing-new-hash-functions-for-sql-server.aspx
a8fffc51-d38a-4de5-81eb-b1bdcc45aa98|0|.0
I was geeking last weekend, no wait, actually three weeks ago, and then last night and tonight…it doesn’t really matter, with CASCADE DELETES in SQL Server 2008 R2, and was curious about whether or not I could trace Cascade Referential Integrity in SQL Server Profiler. Do you use Cascade Deletes in your foreign keys? Me neither that much, but I find them sort of fascinating. I should run a test comparing them to coded deletes – I bet they would leave delete statements in the dust. Anytime that SQL Server can do something natively, I’m putting my money on the engine that it can process faster whatever you or I might be trying to do in code. Create the tables to set up our little experimentation: /****** Object: Table [dbo].[tbl_1] Script Date: 11/15/2011 20:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_1](
[Id1] [int] NOT NULL,
[Value1] [varchar](20) NULL,
CONSTRAINT [PK_tbl_1] PRIMARY KEY CLUSTERED
(
[Id1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_2] Script Date: 11/15/2011 20:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_2](
[Id2] [int] NOT NULL,
[Id1] [int] NULL,
[Value2] [varchar](20) NULL,
CONSTRAINT [PK_tbl_2] PRIMARY KEY CLUSTERED
(
[Id2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_3] Script Date: 11/15/2011 20:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_3](
[Id3] [int] NOT NULL,
[Id2] [int] NULL,
[Value3] [varchar](20) NULL,
CONSTRAINT [PK_tbl_3] PRIMARY KEY CLUSTERED
(
[Id3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_4] Script Date: 11/15/2011 20:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_4](
[Id4] [int] NOT NULL,
[Id3] [int] NULL,
[Id2] [int] NULL,
[Value4] [varchar](20) NULL,
CONSTRAINT [PK_tbl_4] PRIMARY KEY CLUSTERED
(
[Id4] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: ForeignKey [FK_tbl_2_tbl_1] Script Date: 11/15/2011 20:57:06 ******/
ALTER TABLE [dbo].[tbl_2] WITH CHECK ADD CONSTRAINT [FK_tbl_2_tbl_1] FOREIGN KEY([Id1])
REFERENCES [dbo].[tbl_1] ([Id1])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_2] CHECK CONSTRAINT [FK_tbl_2_tbl_1]
GO
/****** Object: ForeignKey [FK_tbl_3_tbl_2] Script Date: 11/15/2011 20:57:06 ******/
ALTER TABLE [dbo].[tbl_3] WITH CHECK ADD CONSTRAINT [FK_tbl_3_tbl_2] FOREIGN KEY([Id2])
REFERENCES [dbo].[tbl_2] ([Id2])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_3] CHECK CONSTRAINT [FK_tbl_3_tbl_2]
GO
/****** Object: ForeignKey [FK_tbl_4_tbl_3] Script Date: 11/15/2011 20:57:06 ******/
ALTER TABLE [dbo].[tbl_4] WITH CHECK ADD CONSTRAINT [FK_tbl_4_tbl_3] FOREIGN KEY([Id3])
REFERENCES [dbo].[tbl_3] ([Id3])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_4] CHECK CONSTRAINT [FK_tbl_4_tbl_3]
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
I’ve created four tables, all with a PK and an FK that include DELETE CASCADE; so what’s happening here is that when I delete a record in tbl_1, the foreign keys through to tbl_4 will delete the associated foreign key records. Rather than write some trigger or delete the associated rows in some other fashion, I’m letting SQL Server do it.
Compile this procedure (it’s complicated I know right?) and then take a look at the execution plan. If you forgot, just highlight the stored procedure name in a management studio query window and type control + L:
DROP PROCEDURE proc_cascadetest
GO
CREATE PROCEDURE proc_cascadetest
AS
DELETE tbl_1 WHERE Id1=1
GO
Notice that the execution plan shows the CASCADE DELETE action for the stored procedure; each step for the foreign key tables is clearly found in the plan. This sort of coincides with what Microsoft said awhile back in the following post on Microsoft Support.
You cannot trace cascading deletes and updates in the context of regular events such asTSQL:StmtCompleted and TSQL:BatchCompleted. However, when you execute a statement that performs cascading updates or deletes in SQL Server Profiler, you can trace these cascade actions in the execution plan.
So, these in fact cannot be traced in SQL Profiler. Then, I remember the hundreds or maybe thousands of blogs by Jonathan Keyhaias on the internet about Extended Events in SQL Server 2008 R2. I think this dude maybe invented them and put them into the SQL Engine or something, I’m not quite sure. Since these are supposed to be the all-powerful, all-knowing for internals or so I’m told or led to believe, I thought I’d give them a shot and see if they could serve up the SQL text since profiler can’t. Let’s give it a go. Run the following:
First get the data into our tables…all that I’m doing here is creating the FK equal to the PK + 1, with the FK inserted alongside. Run the script and then go look at the data:
DECLARE @t1_insert int
,@t2_insert int
,@t3_insert int
,@t4_insert intSET @t1_insert=1
SET @t2_insert = @t1_insert+1
SET @t3_insert = @t2_insert+1
SET @t4_insert = @t3_insert+1
WHILE (@t1_insert < 5)
BEGIN
INSERT tbl_1 VALUES (@t1_insert, 'tbl1 val')
INSERT tbl_2 VALUES (@t2_insert, @t1_insert, 'tbl2 val')
INSERT tbl_3 VALUES (@t3_insert, @t2_insert, 'tbl3 val')
INSERT tbl_4 VALUES (@t4_insert,@t3_insert, @t2_insert, 'tbl4 val')
SET @t1_insert+=1
SET @t2_insert+=1
SET @t3_insert+=1
SET @t4_insert+=1
ENDGO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Run this code now to start up extended events session. Make sure and change the sqlserver.database_id for your particular database ID. And, don’t run the script below all at once – maybe just the first part to create the session, then run the stored procedure, and then the part to kill the session:
-- Create the event session and add some events
DROP EVENT SESSION track_sprocs ON SERVER
GO
CREATE EVENT SESSION track_sprocs ON SERVER
ADD EVENT sqlserver.module_end (ACTION (sqlserver.tsql_stack, sqlserver.sql_text) WHERE sqlserver.database_id=6),
ADD EVENT sqlserver.sql_statement_starting (ACTION (sqlserver.tsql_stack, sqlserver.sql_text) WHERE sqlserver.database_id=6),
ADD EVENT sqlserver.sql_statement_completed (ACTION (sqlserver.tsql_stack, sqlserver.sql_text) WHERE sqlserver.database_id=6)
ADD TARGET package0.ring_buffer WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)
GO
-- Start the session
ALTER EVENT SESSION track_sprocs ON SERVER
STATE = START
GO
-- Add another event
ALTER EVENT SESSION track_sprocs ON SERVER
ADD EVENT sqlserver.lock_released
-- Run the test procedure
EXEC proc_cascadetest
GO
-- Drop the events now
ALTER EVENT SESSION track_sprocs ON SERVER
DROP EVENT sqlserver.module_end,
DROP EVENT sqlserver.sql_statement_completed,
DROP EVENT sqlserver.lock_released,
DROP EVENT sqlserver.sql_statement_starting
GODROP EVENT SESSION track_sprocs ON SERVER GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Now run either or both of the following: I don’t see the code that shows me that the delete has happened in the CASCADE action that I coded. WTF?
SELECT top 1000
event_xml.value('(./@name)', 'varchar(100)') as [event_name],
event_xml.value('(./data[@name="object_name"]/value)[1]', 'varchar(255)') as [object_name],
CAST(event_xml.value('(./action[@name="tsql_stack"]/value)[1]','varchar(MAX)') as XML) as [stack_xml],
event_xml.value('(./action[@name="sql_text"]/value)[1]', 'varchar(max)') as [sql_text]
FROM #xml_event_data
CROSS APPLY xml_data.nodes('//event') n (event_xml)
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
SELECT TOP 20 SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text,*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY Creation_Time DESC
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Both images above clearly show that the code that does the RI isn’t in the session that I ran.
My conclusion – none. I tried adding several events in my Extended Event session (is this the same as what people are calling “xevents”?) and couldn’t get anything to pop out. Anyone have the phone number to SQLSkills.com?
Thanks for reading,
Lee Everest
-------------------------
I laid an egg on this one – you try it and give me a shout if you figure it out. I’m gonna go drink a beer. Bye.
fc762b93-24c3-446e-b377-2999b6d84729|0|.0
Dropping indexes when bulk loading a table is a rule #1 kind of deal, beginner stuff – if you remove the indexes, you’ll be better off nine times out of ten. Dropping a clustered index makes sense on a natural key, because worst case SQL Server will have to deal with reordering the key on the fly. For nonclustered indexes, well, this is one that a lot of folks don’t consider; sometimes, the results are dramatic. Myself and fellow associate Harry Doan ran into this the other day, where the developer left indexes on an empty table in an SSIS process. In this blog I’m going to demo a scenario where I keep the nonclustered indexes in place, and then drop them for a data load. Next time I will test the scenario when the table has n number of rows already in the table. One consideration for dropping nonclustered indexes on an empty table is the answer to the question…”What’s faster – loading a table with the indexes, or loading the table without the indexes, and then rebuilding indexes after the data has been landed?” For my test, I have a super-slammin’ box (Dell R900 with 200GB RAM). Run the following code: USE tempdb
GO
DROP TABLE TestNC
GO
CREATE TABLE TestNC(
ID int IDENTITY (0,1) NOT NULL
,Col1 varchar (4)
,Col2 varchar (10)
,Col3 int
,Col4 int
,Col5 varchar (7)
)
GO
CREATE NONCLUSTERED INDEX idx1 ON TestNC (Col1, Col2, Col3)
GO
CREATE NONCLUSTERED INDEX idx2 on TestNC (Col4, Col5)
GO
DROP TABLE #data
GO
SELECT x.C1, x.C2, ROW_NUMBER() OVER (ORDER BY C3) AS C3, ROW_NUMBER() OVER (ORDER BY c4) as C4, c5
INTO #data
FROM
(SELECT TOP 10000000 SPACE(4) AS C1
,'AAAAAAAAAA' AS C2
,0 AS C3
,1 AS C4
,'AAAAAAA' AS C5
FROM sys.syscolumns AS a, sys.syscolumns AS b, sys.syscolumns as c
)x
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
So now the table is ready, with indexes built and data staged to load. Run this script now:
INSERT INTO TestNC WITH (TABLOCKX)
SELECT TOP 6000000 * FROM #data
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Now drop the table, and recreate without the indexes, then load the table. Finally, go rebuild the indexes. Which is faster? A typical scenario such as this one with a decent-sized box will – most of the time – work as such. The increase here is pretty dramatic because I’m using a strong SQL Server.
By the way, just the other day, someone else blogged about this, but really didn’t run a test like we have here. One thing that they mentioned is disabling rather than dropping indexes “is better than dropping indexes.” Of course, when I read something like this, I have to go find out for myself, because more often than not proclaiming absolutes in SQL Server will more often than not come back and bite you in the behind. This is not trying to call someone out – we have too many SQL commandos and ninjas in world already trying to show someone up and flex their ego. Ego, man I left mine behind many years ago, and I’m much happier today. If you have one, my advice to you is lose it. Egos are for classless losers and punks.
Additional code:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TestNC]') AND name = N'idx1')
DROP INDEX [idx1] ON [dbo].[TestNC] WITH ( ONLINE = OFF )
GO
CREATE NONCLUSTERED INDEX [idx1] ON [dbo].[TestNC]
(
[Col1] ASC,
[Col2] ASC,
[Col3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [tempdb]
GO
/****** Object: Index [idx2] Script Date: 10/04/2011 16:43:06 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TestNC]') AND name = N'idx2')
DROP INDEX [idx2] ON [dbo].[TestNC] WITH ( ONLINE = OFF )
GO
USE [tempdb]
GO
/****** Object: Index [idx2] Script Date: 10/04/2011 16:43:06 ******/
CREATE NONCLUSTERED INDEX [idx2] ON [dbo].[TestNC]
(
[Col4] ASC,
[Col5] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER INDEX idx1
ON TestNC DISABLE;
ALTER INDEX idx2
ON TestNC DISABLE;
ALTER INDEX idx1
ON TestNC REBUILD
ALTER INDEX idx2
ON TestNC REBUILD
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Here are my results, including the ALTERs rather than drops. Try for yourself…don’t believe me like I didn’t believe that dude.
TEST RUN
Indexes insert time index time (idx1/2) index action total
---------------------------------- ----------------------- ---------------------- ------ ------
Indexes on table 2:28 n/a 2:28
Not on table 0:06 :31+:01 drop 0:38
Not on table 0:06 :32+:02 disable 0:40
So based on this minimal test, a load with indexes too two additional minutes, and the disable as opposed to the drop index too two seconds longer. Try some tests for yourself and see what you can come up with. Next time I’m going to look at the loading chart in Book Online and “see for myself”.
Lee
-------------------------
Refs and other readings
http://blogs.technet.com/b/sql_server_isv/archive/2011/10/04/redprairie-removing-unneeded-table-indices.aspx
http://msdn.microsoft.com/en-us/library/dd425070(v=sql.100).aspx
http://msdn.microsoft.com/en-us/library/ms177445.aspx
Posted in:
TSQL ,
Beginner Tags:
843c8f38-cdf8-40fb-a03b-9b132aed03ef|3|5.0
My friend Jamie Thomson, kick-ass SQL consultant, the “SSIS Junkie”, and super nice dude, just retweeted the following SQLMag.com article, very interesting to me: Semicolon: By Itzik Ben-GanNot terminating T-SQL statements with a semicolon is on a deprecation path. http://bit.ly/mPKMnN Check out the blog post that they refer to. If you recall, this feature (semicolon) was destined to terminate all TSQL statements as a requirement; in Books Online, it is mentioned here that it will be required in a future version, and most folks thought that the future version was the next version. Apparently not. I work with a guy who has done a great job adding the semicolon to all of his code – I haven’t started doing it yet, so I guess I have a little while to get used to doing it. Remember an old post that I had with the following query: SELECT object_name, instance_name, cntr_typeFROM sys.dm_os_performance_countersWHERE OBJECT_NAME = 'SQLServer:Deprecated Features 'ORDER BY 2; – :)
Let’s run this thing in Denali and see wut up. Results below.
Thanks,
Lee
----------------------------------
# and ## as table names…removing that one still upsets me
Deprecated in Denali
'#' and '##' as the name of temporary tables and stored procedures '::' function calling syntax '@' and names that start with '@@' as Transact-SQL identifiers ADDING TAPE DEVICE ALL Permission ALTER DATABASE WITH TORN_PAGE_DETECTION ALTER LOGIN WITH SET CREDENTIAL Azeri_Cyrillic_90 Azeri_Latin_90 BACKUP DATABASE or LOG TO TAPE Create/alter SOAP endpoint CREATE_DROP_DEFAULT CREATE_DROP_RULE Data types: text ntext or image Database compatibility level 100 Database compatibility level 90 DATABASEPROPERTYEX('IsFullTextEnabled') DBCC [UN]PINTABLE DBCC DBREINDEX DBCC INDEXDEFRAG DBCC SHOWCONTIG DBCC_EXTENTINFO DBCC_IND DEFAULT keyword as a default value Deprecated encryption algorithm DESX algorithm dm_fts_active_catalogs dm_fts_active_catalogs.is_paused dm_fts_active_catalogs.previous_status dm_fts_active_catalogs.previous_status_description dm_fts_active_catalogs.row_count_in_thousands dm_fts_active_catalogs.status dm_fts_active_catalogs.status_description dm_fts_active_catalogs.worker_count dm_fts_memory_buffers dm_fts_memory_buffers.row_count DROP INDEX with two-part name endpoint_webmethods EXTPROP_LEVEL0TYPE EXTPROP_LEVEL0USER FILE_ID fn_get_sql fn_servershareddrives fn_trace_geteventinfo fn_trace_getfilterinfo fn_trace_getinfo fn_trace_gettable fn_virtualservernodes fulltext_catalogs fulltext_catalogs.data_space_id fulltext_catalogs.file_id fulltext_catalogs.path FULLTEXTCATALOGPROPERTY('LogSize') FULLTEXTCATALOGPROPERTY('PopulateStatus') FULLTEXTSERVICEPROPERTY('ConnectTimeout') FULLTEXTSERVICEPROPERTY('DataTimeout') FULLTEXTSERVICEPROPERTY('ResourceUsage') GROUP BY ALL Hindi HOLDLOCK table hint without parenthesis IDENTITYCOL IN PATH Index view select list without COUNT_BIG(*) INDEX_OPTION INDEXKEY_PROPERTY Indirect TVF hints INSERT NULL into TIMESTAMP columns INSERT_HINTS Korean_Wansung_Unicode Lithuanian_Classic Macedonian MODIFY FILEGROUP READONLY MODIFY FILEGROUP READWRITE More than two-part column name Multiple table hints without comma NOLOCK or READUNCOMMITTED in UPDATE or DELETE Numbered stored procedures numbered_procedure_parameters numbered_procedures objidupdate Old NEAR Syntax OLEDB for ad hoc connections PERMISSIONS READTEXT REMSERVER RESTORE DATABASE or LOG WITH MEDIAPASSWORD RESTORE DATABASE or LOG WITH PASSWORD Returning results from trigger ROWGUIDCOL SET ANSI_NULLS OFF SET ANSI_PADDING OFF SET CONCAT_NULL_YIELDS_NULL OFF SET ERRLVL SET FMTONLY ON SET OFFSETS SET REMOTE_PROC_TRANSACTIONS SET ROWCOUNT SETUSER soap_endpoints sp_addapprole sp_addextendedproc sp_addlogin sp_addremotelogin sp_addrole sp_addrolemember sp_addserver sp_addsrvrolemember sp_addtype sp_adduser sp_approlepassword sp_attach_db sp_attach_single_file_db sp_bindefault sp_bindrule sp_bindsession sp_certify_removable sp_change_users_login sp_changedbowner sp_changeobjectowner sp_configure 'affinity mask' sp_configure 'affinity64 mask' sp_configure 'allow updates' sp_configure 'c2 audit mode' sp_configure 'default trace enabled' sp_configure 'disallow results from triggers' sp_configure 'ft crawl bandwidth (max)' sp_configure 'ft crawl bandwidth (min)' sp_configure 'ft notify bandwidth (max)' sp_configure 'ft notify bandwidth (min)' sp_configure 'locks' sp_configure 'open objects' sp_configure 'priority boost' sp_configure 'remote proc trans' sp_configure 'set working set size' sp_create_removable sp_db_increased_partitions sp_db_vardecimal_storage_format sp_dbcmptlevel sp_dbfixedrolepermission sp_dbremove sp_defaultdb sp_defaultlanguage sp_denylogin sp_depends sp_detach_db @keepfulltextindexfile sp_dropapprole sp_dropextendedproc sp_droplogin sp_dropremotelogin sp_droprole sp_droprolemember sp_dropsrvrolemember sp_droptype sp_dropuser sp_estimated_rowsize_reduction_for_vardecimal sp_fulltext_catalog sp_fulltext_column sp_fulltext_database sp_fulltext_service @action=clean_up sp_fulltext_service @action=connect_timeout sp_fulltext_service @action=data_timeout sp_fulltext_service @action=resource_usage sp_fulltext_table sp_getbindtoken sp_grantdbaccess sp_grantlogin sp_help_fulltext_catalog_components sp_help_fulltext_catalogs sp_help_fulltext_catalogs_cursor sp_help_fulltext_columns sp_help_fulltext_columns_cursor sp_help_fulltext_tables sp_help_fulltext_tables_cursor sp_helpdevice sp_helpextendedproc sp_helpremotelogin sp_indexoption sp_lock sp_password sp_remoteoption sp_renamedb sp_resetstatus sp_revokedbaccess sp_revokelogin sp_srvrolepermission sp_trace_create sp_trace_getdata sp_trace_setevent sp_trace_setfilter sp_trace_setstatus sp_unbindefault sp_unbindrule SQL_AltDiction_CP1253_CS_AS sql_dependencies String literals as column aliases sysaltfiles syscacheobjects syscolumns syscomments sysconfigures sysconstraints syscurconfigs sysdatabases sysdepends sysdevices sysfilegroups sysfiles sysforeignkeys sysfulltextcatalogs sysindexes sysindexkeys syslockinfo syslogins sysmembers sysmessages sysobjects sysoledbusers sysopentapes sysperfinfo syspermissions sysprocesses sysprotects sysreferences sysremotelogins sysservers systypes sysusers Table hint without WITH Text in row table option TEXTPTR TEXTVALID TIMESTAMP UPDATETEXT or WRITETEXT USER_ID Using OLEDB for linked servers Vardecimal storage format XMLDATA XP_API xp_grantlogin xp_loginconfig xp_revokelogin
Posted in:
TSQL ,
CTP Tags:
2cec3997-12df-4da7-8317-db4abe4218d0|1|4.0
Someone just asked me if there's a built-in function in TSQL that takes a string and changes the first letter of each word to upper case. I told them hell I don't know but I think not; here's a way to do it without a bunch of char functions...use a CLR UDF. One line and all works pretty well:
using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Globalization;
public partial class UserDefinedFunctions
{ [Microsoft.SqlServer.Server.SqlFunction]
public static SqlString UpperFirst(string value) {
return new SqlString(CultureInfo.CurrentCulture.TextInfo.ToTitleCase(value));
}};
sp_configure 'clr enable',1GORECONFIGUREGOSELECT dbo.UpperFirst('TSQL upper case first letter of each word')GO
Posted in:
.Net ,
Beginner ,
TSQL Tags:
3f2f2e00-057b-4ade-8943-a7e629fa9c8f|0|.0
We had a failure today and it brought up a couple of interesting points. I'll put this into my beginner category, but you might take something away from this post regardless. Run the following script:
USE tempdb;GODROP TABLE tbl_TestGOCREATE TABLE tbl_Test (ID int IDENTITY(1,1) NOT NULL PRIMARY KEY ,charCol char(2) )GO
INSERT INTO tbl_Test (charCol) VALUES (1)GOINSERT INTO tbl_Test (charCol) VALUES (2)GOINSERT INTO tbl_Test (charCol) VALUES ('3a')GO
This is all fine - love row constructors - but go ahead and try this one:
INSERT INTO tbl_Test (charCol) VALUES (1), (2), ('3a')GO
Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value '3a' to data type int.
How about this one?
INSERT INTO tbl_Test (charCol) VALUES ('a')GOINSERT INTO tbl_Test (charCol) VALUES ('b')GOINSERT INTO tbl_Test (charCol) VALUES (3)GOINSERT INTO tbl_Test (charCol) VALUES ('a'), ('b'), (3)GO
Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value 'a' to data type int.
I wouldn't swear to it, but I'm guess that what is happening here is that data type precedence is causing this failure. Despite the order of INSERT and types, the higher precedence integer causes this to fail when inserting via a row constructor.
Which brings up another point - check this one out:
USE tempdb;GODROP TABLE tbl_TestGOCREATE TABLE tbl_Test (ID int IDENTITY(1,1) NOT NULL PRIMARY KEY ,charCol char(2) )GOINSERT INTO tbl_Test (charCol) VALUES (11)GOINSERT INTO tbl_Test (charCol) VALUES (22)GOINSERT INTO tbl_Test (charCol) VALUES ('AA')GO
SELECT MAX(ID) as charColFROM tbl_TestWHERE charCol=11GROUP BY ID
Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value 'AA' to data type int.
In this case, we needed to use test the data type in the WHERE clause beforehand, or checking the type before inserting into the table. Also, it's good practice to not let the implict conversion from int to char kick in; if you are inserting an integer into a char field, consider putting the tick marks '' around the integer, depening on the action that you want the process to take downstream.
Lee
----------------------
http://msdn.microsoft.com/en-us/library/ms174335.aspx
Posted in:
Beginner ,
TSQL Tags:
43e39ba3-bdea-4850-bb0c-bb2df0c73600|0|.0
I see all over Google that TSQL’s COLUMNS_UPDATED() doesn’t work correctly; there are even comments in Books Online that users are having problems with it, saying that it either has problems with SQL 2005 forward (“The COLUMNS_UPDATED() does not work in the procedure called in a trigger since SQL 2005”), or it only works with a certain number of columns (“Doesn't work correctly with tables with more columns”). These are recent posts by the way, both in June 2011. Actually it is working, and it isn’t. Run the following script: SET NOCOUNT ON
IF OBJECT_ID('TriggerTest') IS NOT NULL
DROP TABLE TriggerTest;
GO
CREATE TABLE TriggerTest
(UpdateCol1 int default 0 --1
,UpdateCol2 int default 0 --2
,UpdateCol3 int default 0 --4
,UpdateCol4 int default 0 --8
,UpdateCol5 int default 0 --16
,UpdateCol6 int default 0 --32
,UpdateCol7 int default 0 --64
,UpdateCol8 int default 0 --128
--,UpdateCol9 int default 0 --256
--,UpdateCol10 int default 0
--,UpdateCol11 int default 0
--,UpdateCol12 int default 0
--,UpdateCol13 int default 0
--,UpdateCol14 int default 0
--,UpdateCol15 int default 0
--,UpdateCol16 int default 0
--,UpdateCol17 int default 0
--,UpdateCol18 int default 0
--,UpdateCol19 int default 0
--,UpdateCol20 int default 0
--,UpdateCol21 int default 0
--,UpdateCol22 int default 0
--,UpdateCol23 int default 0
--,UpdateCol24 int default 0
--,UpdateCol25 int default 0
--,UpdateCol26 int default 0
--,UpdateCol27 int default 0
--,UpdateCol28 int default 0
--,UpdateCol29 int default 0
--,UpdateCol30 int default 0
);
GO
INSERT TriggerTest (UpdateCol1) VALUES (0)
GO
IF OBJECT_ID ('trColumnsUpdateTest', 'tr') IS NOT NULL
DROP TRIGGER trColumnsUpdateTest;
GO
CREATE TRIGGER trColumnsUpdateTest
ON TriggerTest
FOR UPDATE
AS
BEGIN
PRINT COLUMNS_UPDATED()
PRINT CONVERT(INT, COLUMNS_UPDATED())
IF COLUMNS_UPDATED() & 128= 128
PRINT 'UPDATED'
END
GO
DECLARE @col INT=1
WHILE @col <= 8
BEGIN
DECLARE @str varchar (max) = 'UPDATE TriggerTest SET UpdateCol' +CAST(@col AS varchar (20))+ '=999';
IF @col=8
EXEC (@str)
SET @col = @col+1
END
GO
SELECT * FROM TriggerTest
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
I have coded for an update in the trigger to print the mask as well as a message if the column was updated; in position eight, above, the mask value of type int is 128. Here is the output:
0x80
128
UPDATED
No problem here, the column is updated. Now run the script but 1) uncomment column nine above, 2) change the following so you update column 9 and see what the mask value is for that position:
IF COLUMNS_UPDATED() & 256= 256 PRINT 'UPDATED'
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
DECLARE @col INT=1
WHILE @col <= 9
BEGIN
DECLARE @str varchar (max) = 'UPDATE TriggerTest SET UpdateCol'+CAST(@col AS varchar (20))
+ '=999';
IF @col=9
EXEC (@str)
SET @col = @col+1
END
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Here are the results:
0x0001
1
So, the column is updated, but you don’t get the ‘UPDATED’ message because the value of the mask did not move by a power of 2. It changed. Check out the attachment that I have included, where I printed the bit mask value for 8,9,10,20, and 30 column tables, and notice the value of the mask actually changes for each iteration.
Let’s look at one column – column 3 – for a table in the attachment.
You can see here that if you were wanting to pluck out whether or not column ordinal 3 was updated if, for a nine column table, you would have to mask 1024, but if you added 10 columns to the table you would have to mask 262144. Let’s do that one. Uncomment the script above so that 20 columns are created, and run the following ALTER:
IF COLUMNS_UPDATED() & 262144= 262144 PRINT 'UPDATED'
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
DECLARE @col INT=1
WHILE @col <= 20
BEGIN
DECLARE @str varchar (max) = 'UPDATE TriggerTest SET UpdateCol'+CAST(@col AS varchar (20))
+ '=999';
IF @col=20
EXEC (@str)
SET @col = @col+1
END
GO
0x040000
262144
UPDATED
So the issue is how Microsoft built the masking mechanism. What they instructed you to do you can’t do with a table having more than eight columns. The values 0x0400 is not the same value as 0x040000, which isn’t the same as 0x04000000. I guess I would have counted from the right, and done 0x000000000004; the problem with this, of course is that you will eventually run out of space to mask columns, and I think these days they let you create 1024 columns in a table, and that would be a problem, because at column number 123 you would need to mask 5316911983139663500000000000000000000.0, and that’s the largest number of a decimal (38,0) in SQL Server, nowhere close to 1024 columns. Given this info, it would be just as easy to skip COLUMNS_UPDATED() and just use the UPDATE().
Thanks,
Lee Everest
---------------------------
So I wonder how long takes for this to show up on Pinal Dave’s site
File with COLUMNS_UPDATE() File
f2096ef6-7a45-487a-a345-258930999738|1|5.0
When doing a right-click >> Edit Top xxx Rows you may want to add a NULL value in an attribute sometime or another.
To do this you can:
type CTL + 0 when in the column and a NULL will appear.
Now the little-known and totally interesting one...type NULL in CAPS and you'll get a NULL in the column. Capital letters will appear, and then you are finished typing NULL the letters will turn into italics, letting you know that it's a NULL and not NULL. Sweet!
You'll only get these interesting factoids here at my blog - why look elsewhere?
Lee Everest
---------------
Now you're asking - why the hell would you want to do this?
1642ef2e-d05d-4e24-b6f4-281507fd5e20|0|.0
If you use a lot of temporary tables, you may get the silly error, above. Run the following in a query window:
USE Test
DROP TABLE #myTable
GO
CREATE TABLE #myTable (ID int NOT NULL)
GO
ALTER TABLE #myTable
ADD CONSTRAINT pk_myTable PRIMARY KEY (ID)
GO
INSERT INTO #myTable VALUES (1)
GO
(1 row(s) affected)
Now crack open another query window and run the same snippet. Here’s what you’ll get:
Msg 2714, Level 16, State 4, Line 1 There is already an object named 'pk_myTable' in the database. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.
(1 row(s) affected)
There interesting thing here was pointed out to me by my stealth-DBA associate Harrison Doan today, because I was debugging a process while in another spid the actual production process was dying. Nice, right? What’s happening here is that while some smart guy knew to create temporary tables with a guid and long line of underscores attached to the back of a table, some bozo decided NOT to do same with constraints, hence the error. As you know you can’t have the same constraint name twice in a database.
Here’s the view from sys.sysobjects – you can see that one constraint gets created, but the other doesn’t. Note the parent object:
Here’s the kicker. Open the image again and notice the table #108B795B. I have no idea what it is, some table created by SQL Server. Looks closely right below this entry…the idiot attached that GUID to the back of the PK, but didn’t bother to in our case! WTH??? You can see the primary key for the above table, PK__#108B795__727E83EB1273C1CD, and the nice GUID-y looking value affixed to the PK name. Oh thank you.
Come on, Microsoft, help a brother out!!! Harry actually submitted this to them (Microsoft) a few years ago, and they voted and decided to forego this fix I guess.
Thanks for reading,
Lee
-----------------------------
They were probably to busy building Notification Services.
f0409102-2165-4326-ad27-948cf7abd120|0|.0
You should check out Creating Compressed Tables and Indexes if you haven’t already for some big space savings on your SQL Servers. Space is cheap, true, but if you deal with a wasteful lot, you may not have the option to add a terabyte here and a terabyte there whenever somebody wants one, or an email warning gets issued from MOM, SCOM, or some other tool saying that the server is out of space. Here’s a script that you could use to compress tables. I whipped this up in about 5 minutes, so if there’s a bug or something that doesn’t work as you like, then change it as you see fit. I use a ## table so I can see if from other windows/spids, of course. Really impressed with how much space it can save. DECLARE @dbname1 sysname = DB_NAME() --Enabling databases for the vardecimal storage format is only necessary in SQL Server 2005. EXEC sp_db_vardecimal_storage_format @dbname = @dbname1, @vardecimal_storage_format = 'ON'; GO SET NOCOUNT ON IF OBJECT_ID ('tempdb..##Compress') IS NOT NULL DROP TABLE ##Compress GO CREATE TABLE ##Compress (ID int identity , tablename sysname , [rows] int , sStatus tinyint default 0) INSERT INTO ##Compress (tablename, [rows]) SELECT TABLE_SCHEMA+'.'+TABLE_NAME, [rows] FROM INFORMATION_SCHEMA.TABLES s INNER JOIN (SELECT DISTINCT OBJECT_NAME(id) as id, [rows] FROM SYS.sysindexes) indexes ON s.TABLE_NAME = indexes.id ORDER BY 2 DECLARE @i int; DECLARE @tablename sysname; DECLARE @msg varchar (1000); DECLARE @runningCounter int; SELECT @tablename = tablename FROM ##Compress WHERE id=(SELECT MIN(ID) FROM ##Compress) SELECT @runningCounter= COUNT(1) FROM ##Compress SET @i=(SELECT MIN(ID) FROM ##Compress) SET @msg='' WHILE (@runningCounter>0) BEGIN EXEC sp_tableoption @tablename, 'vardecimal storage format', 'ON'; EXEC ('ALTER TABLE ' + @tablename + ' REBUILD WITH (DATA_COMPRESSION = PAGE)'); UPDATE ##Compress SET sStatus=1 WHERE ID = @i SELECT @i +=1 SELECT @runningCounter-=1 SELECT @tablename = tablename FROM ##Compress WHERE ID=@i AND sStatus=0 SELECT @msg = CHAR(13) + 'TABLE: ' + @tablename + ' completed at [' + CAST(GETDATE() as varchar (22)) + ']. Table count remain: ' + CAST(@runningCounter as varchar (10)) RAISERROR (@msg, 10,2) WITH NOWAIT IF (@runningCounter = 0) PRINT CHAR(13) + '>>>>>>>>>>>>>>>>>>>>>> DONE! <<<<<<<<<<<<<<<<<<<<<<<<' END GO Here’s the output I got after running it on a (very) small database: TABLE: HumanResources.Employee completed at [May 18 2011 9:46PM]. Table count remain: 3 TABLE: dbo.TLOG_TEST completed at [May 18 2011 9:46PM]. Table count remain: 2 TABLE: dbo.tbl completed at [May 18 2011 9:47PM]. Table count remain: 1 TABLE: dbo.ztbl completed at [May 18 2011 9:47PM]. Table count remain: 0 >>>>>>>>>>>>>>>>>>>>>> DONE! <<<<<<<<<<<<<<<<<<<<<<<< Thanks for reading, Lee --------------------------
abb8d3e3-aa12-4caf-9e69-18066df9e442|0|.0