Posts by :
- It’s like Identity but not
- Can reuse values once a limit has been reached
- Sequence can be “shared” between many tables as it is it’s own object
- Has to be assigned to a schema
- Inserting conflict – what if 2 transactions ask for the next sequence (NEXT VALUE FOR) and both try and commit the same “OrderID”?
- IDENTITY columns can’t be updated “accidentally” … other then the use of a trigger I don’t see how you could prevent an update to a column referencing a sequence
- Reusing sequences just sounds dangerous
#OKPASS – I’m Leaving on a Jetplane
May 15th, 2012
OK so this will be another first for me…. I’ve done plenty of remote presentations (via LiveMeeting) for PASS Chapters around the world but this will be the first time I’ve actually flown to another PASS Chapter (OKPASS) and presented live in person! 
Something that I’ve always envied about speakers in the US is how easy it is for them to travel (either by plane or driving) to other PASS chapters that are nearby. From where I live (Edmonton) the ONLY PASS Chapter that is not an minimum of a 10 hour drive is Calgary PASS (Where I’ll be presenting in July) so this is really exciting for me.
PASS is trying to help Regional Mentors get out to the chapters in their region for more “face time” by (currently on a trial basis) allocating some budget money for regional mentors travel expenses. I think this is an amazing opportunity for Regional Mentors to get out to a chapter that they haven’t been to before, do a presentation, and get some face to face contact. I know I wouldn’t of been able to present in person for OKPASS if it wasn’t for this so I’m really excited (and thankful) for the opportunity!
Meeting details below:
Date: May 26th 2012
Time: 3:00 pm – 5:00 pm
Location: Landmark II Building, Suite #702, 1708 Dolphin Ave. Kelowna, B.C.
Speaker: Colin Stasiuk
Topic: SQL 2012 – When Worlds Collide
Session Abstract:
This intro to SQL Server 2012 presentation is going to look at some of the new features and functionality for not only the DBAs (EDMPASS) but also the developers (EDMUG). For the developers in the audience we’re going to discuss and demo new features like paging, result sets, sequence numbers and throws and for the DBAs in the room we’ll discuss AlwaysOn, contained databases, and user defined server roles. Hopefully this will help get you prepared and excited for the upcoming newly released version – SQL Server 2012.
Presenter Information: Colin Stasiuk ( blog | twitter )
Colin Stasiuk is an MCP, MCTS SQL 2005/2008, MCITP DBDEV, and MCITP DBA. Currently, he is an independent consultant contracted to Invidi Technologies Corporation and also provides remote database support and monitoring through his SQL Wingmen service offering. Colin is an accomplished Microsoft SQL Server DBA who has been working with SQL Server since 1996. He is the owner of Benchmark IT Consulting and is always willing to lend a hand with questions in many SQL Server community forums and via Twitter. His specialties include SQL Server Administration, Performance Tuning, Security, Best Practice / Standards, Upgrades, and Consolidation. Colin is the Regional Mentor for PASS Canada, President and founder of EDMPASS, and co-author of Pro SQL Server 2008 Policy-Based Management.
If you’d like to chat with me about this or anything else (SQL or other) please leave a comment or hit me up on my Twitter: @ColinStasiuk
List All Statistics For a Column – Update
April 10th, 2012Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN ColumnName failed because one or more objects access this column.
So what is a nice quick and easy way to list all the statistics that are dependant on that column?
My googling skills are obviously subpar this morning cause I couldn’t find anything to help me and it seems to me that this would be something that lots of people have run into. This is what I came up with:
DECLARE @SearchColumnName nvarchar(100) SELECT @SearchColumnName = 'SearchColumn' SELECT OBJECT_NAME(A.object_id) as 'TableName', C.stats_column_id AS 'StatisticID', B.name as 'StatisticName', COL_NAME(C.object_id, C.column_id) AS [Name] FROM sys.tables AS A INNER JOIN sys.stats B ON B.object_id = A.object_id INNER JOIN sys.stats_columns C ON C.stats_id = B.stats_id AND C.object_id = B.object_id WHERE COL_NAME(C.object_id, C.column_id) = @SearchColumnName
I dunno… seems like there should be a sp_ListAllStatisticsForAColumn system stored proc or something LOL.
If you have something easier/better please post it in a comment!!
UPDATE:
Change your WHERE Clause to this:
WHERE COL_NAME(C.object_id, C.column_id) = @SearchColumnName AND B.name NOT IN (Select ISNULL(name,'') from sys.indexes)
If you want to filter out the statistics that are created based on indexes (which can’t be dropped anyways)
Enjoy!!
EDMPASS – I’m Speaking on SQL 2012
March 14th, 2012On March 29th 2012 the Edmonton Chapter of PASS is having it’s next meeting. Details below:
http://www.eventbrite.com/event/3046360747
Please be sure to not only click the “Add to my calendar” but also the “Register” button so that we can plan accordingly for food and drinks.
Date: March 29th 2012
Time: 5:00 pm – 7:00 pm
Location: 3rd floor 10180-101 st. (Manulife Place – King Edward Room)
Speaker: Colin Stasiuk
Topic: SQL 2012 – When Worlds Collide
Session Abstract:
This intro to SQL Server 2012 presentation is going to look at some of the new features and functionality for not only the DBAs (EDMPASS) but also the developers (EDMUG). For the developers in the audience we’re going to discuss and demo new features like paging, result sets, sequence numbers and throws and for the DBAs in the room we’ll discuss AlwaysOn, contained databases, and user defined server roles. Hopefully this will help get you prepared and excited for the upcoming newly released version – SQL Server 2012.
Presenter Information: Colin Stasiuk ( blog | twitter )
Colin Stasiuk is an MCP, MCTS SQL 2005/2008, MCITP DBDEV, and MCITP DBA. Currently, he is an independent consultant contracted to Invidi Technologies Corporation and also provides remote database support and monitoring through his SQL Wingmen service offering. Colin is an accomplished Microsoft SQL Server DBA who has been working with SQL Server since 1996. He is the owner of Benchmark IT Consulting and is always willing to lend a hand with questions in many SQL Server community forums and via Twitter. His specialties include SQL Server Administration, Performance Tuning, Security, Best Practice / Standards, Upgrades, and Consolidation. Colin is the Regional Mentor for PASS Canada, President and founder of EDMPASS, and co-author of Pro SQL Server 2008 Policy-Based Management.
Agenda:
5:00 pm – Pizza and Socializing
5:30 pm – Sponsor Presentation
5:45 pm – Feature Presentation
7:00 pm – Wrap Up and Draws
If you haven’t signed up already at EDMPASS.com please do so now to receive meeting notifications, news, and updates from EDMPASS.
Hope to see you there.
ATTENDEE LIVEMEETING LINK – (coming soon)
If you’d like to chat with me about this or anything else (SQL or other) please leave a comment or hit me up on my Twitter: @ColinStasiuk
What Is My Restore Doing?
February 16th, 2012I love that I can still learn cool new stuff with SQL Server even after 15 years
I was trying to troubleshoot a problem and got to the point where I was questioning everything I knew about backups/restores because I just couldn’t explain what was going on and why a restore was taking so long to finish.
Then I came across this little nugget of awesomeness to help me along the way:
DBCC TRACEON (3004, 3605, -1)
Don’t worry little birdies I’ll feed ya…
Trace Flag 3004 – This gives you a very detailed explanation as to what your restores (and backups) are doing during execution
Trace Flag 3605 – This will write out the results of your Mgmt Studio query to the SQL Server Log rather than to the results window
The results will look something like this (click to enlarge):
To disable (turn off) these trace flags:
DBCC TRACEOFF (3004, 3605, -1)
If you’d like to chat with me about this or anything else (SQL or other) please leave a comment or hit me up on my Twitter: @ColinStasiuk
SEQUENCE… explain why and win a prize
February 6th, 2012In SQL Server 2012 there is a new feature that should be very familiar to Oracle folks called SEQUENCE.
(my wife LOVES this game)
“Creates a sequence object and specifies its properties. A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted. Sequences, unlike identity columns, are not associated with specific tables. Applications refer to a sequence object to retrieve its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.”
So what did I get from this:
There are plenty of great examples on how to create and reference a SEQUENCE on the MSDN link above but where I’m struggling is why I would want to use this new functionality?
Some problems/questions I have right off the bat:
I did a quick test of the “Inserting Conflict” issue and created a SEQUENCE:
CREATE SEQUENCE dbo.TestSequence1 AS INT START WITH 1 INCREMENT BY 1
Then I opened 2 mgmt studio query windows and ran the following:
DECLARE @NextSequence AS INT BEGIN TRAN SELECT @NextSequence = NEXT VALUE FOR dbo.TestSequence1 SELECT @NextSequence
and I’m happy to report that getting the “NEXT VALUE” increments the sequence so as long as you do this within a transaction you should be good to go without worrying about an INSERT conflict.
So where does that leave me?
It leaves me still asking WHY… so where is where the “win a prize” comes in…
HOW TO WIN A PRIZE
In the comments section if you can give me a reason or a use-case as to why/how you would use this over IDENTITY you could win a prize
Now the winner is going to be the reason/use-case/comment that best explains to ME how/why you would use SEQUENCE and why it would be a better solution than an IDENTITY.
Contest end date of: Feb 10th
If you’d like to chat with me about this or anything else (SQL or other) please leave a comment or hit me up on my Twitter: @ColinStasiuk
