SSMS Tools Pack
Posted on October 20, 2010
(Last modified on January 2, 2022)
| 1 minutes
| 181 words
| Adrian Hills
Today I downloaded and installed the SSMS Tools Pack, a free add-in for SQL Server Management Studio developed by Mladen Prajdić (Blog | Twitter).
Late to the party…and not even fashionably late Yes I know, I know - everyone uses the SSMS Tools Pack and I’m well aware I’m late to the party! Fail whale on my part. It’s one of those things I bookmarked a long time ago and just never got round to actually trying out.
[Read More]Long time no post
Posted on October 18, 2010
(Last modified on January 2, 2022)
| 2 minutes
| 237 words
| Adrian Hills
It’s been a while since my last blog post, with good reason as September saw the arrival of my first child - a beautiful baby girl. It did mean I had to miss SQLBits 7 up in York, but of course that pales into insignificance when comparing to becoming a Dad for the first time! Besides, there’s always SQLBits 8…(cough bring it to the south coast! cough). Now I’m back at work after paternity leave and I’m starting to get back in the swing of things.
[Read More]Passing a TABLE variable into dynamic SQL
Posted on August 10, 2010
(Last modified on January 2, 2022)
| 1 minutes
| 140 words
| Adrian Hills
A question popped up on StackOverflow today, asking how to pass a TABLE variable into a dynamic SQL statement in SQL Server 2008. I’ve previously blogged about table-valued parameters, comparing the approach of passing in a TABLE of values to a stored procedure to the techniques you’d have had to use in earlier versions of SQL Server (e.g. CSV or XML), but this specific question is worth a quick follow-up.
[Read More]Validating an SQL query programmatically, with gotcha
Posted on July 1, 2010
(Last modified on January 2, 2022)
| 2 minutes
| 343 words
| Adrian Hills
If you want to check the validity of a TSQL statement programmatically, you can make use of the SET NOEXEC statement. As this MSDN reference states, SET NOEXEC ON will compile the query but won’t actually execute it. This is ideal (well, nearly….) if perhaps you have a dynamically generated statement that you want to check is valid before executing it.
Example time SET NOEXEC ON GO SELECT TOP * FROM sys.
[Read More]The SQL Server MasterClass Experience
Posted on June 18, 2010
(Last modified on January 2, 2022)
| 4 minutes
| 824 words
| Adrian Hills
SQL Server MasterClass 2010 was a very high quality, one day event presented by Paul S. Randal (Twitter | Blog) and Kimberly L. Tripp (Twitter | Blog) - not that they need any introduction of course. In fact if you don’t know of them, then perhaps you should close down your SSMS window and switch off your machine - after reading my blog of course :). Maybe uninstall SQL Server too…
[Read More]SQL Server XML datatype with CDATA
Posted on June 14, 2010
(Last modified on January 2, 2022)
| 1 minutes
| 75 words
| Adrian Hills
So today I learnt something new - it turns out the XML datatype in SQL Server does not preserve CDATA sections.
e.g.
DECLARE @XML XML SET @XML = '<Test><NodeA><![CDATA[Testing cdata section <woop!>]]></NodeA></Test>' SELECT @XML Results ------------------------------------------------------------------ <Test><NodeA>Testing cdata section <woop!></NodeA></Test> After a quick dig around, I found this MS Connect case. I personally would like to see it accept whatever you pass in without silently altering it, as long as it’s well-formed XML of course.
[Read More]Optimising wildcard prefixed LIKE conditions
Posted on May 26, 2010
(Last modified on January 2, 2022)
| 3 minutes
| 562 words
| Adrian Hills
Suppose you want to write a query to find all products in your database that have a name beginning with “Long-Sleeve”. You’d more than likely use something like below (examples based on AdventureWorks LT sample database):
SELECT name FROM SalesLT.Product WHERE name LIKE 'Long-Sleeve%' This produces a good execution plan, performing an index seek on the nonclustered index that exists on the name column. If you look at the seek operation, you’ll see the query optimiser has done a good job of ensuring an index can be used by looking at the seek predicate:
[Read More]Rise of the SQL Server DevBA
Posted on March 4, 2010
(Last modified on January 2, 2022)
| 3 minutes
| 608 words
| Adrian Hills
Something that has got me thinking recently is the distinction between an SQL Server Developer and a DBA. I imagine that most people would describe themselves as one or the other exclusively. My CV for example says I’m a developer - that is what I am. I would never market myself as a DBA; aside from the fact I just don’t have the full skillset of a DBA, it would just be an insult to the real DBAs out there whose knowledge and experience in that arena far outweighs mine.
[Read More]Queue table processing in SQL Server
Posted on March 1, 2010
(Last modified on January 2, 2022)
| 2 minutes
| 342 words
| Adrian Hills
Implementing SQL Server queue table processing logic is something I keep meaning to blog about and finally I’ve got round to it thanks to my memory being jogged by StackOverflow questions I’ve recently participated in, including this one. The scenario is you queue up records in a database table, each representing a piece of work needing to be done. You then want to have processes that periodically poll this table to pick up the next item of work from the queue and process them.
[Read More]Changing primary key index structure
Posted on March 1, 2010
(Last modified on January 2, 2022)
| 2 minutes
| 276 words
| Adrian Hills
Changing the structure of a primary key constraint index from nonclustered to clustered (or from clustered to nonclustered) is not necessarily as straight forward as it first seems. The process of changing it over, involves the constraint being dropped and then recreated. This could potentially cause a problem if you’re making the change on a table whilst there could be activity against it.
Example
TableX was originally created as below:
[Read More]