31May 2012 by Grant Fritchey 21 Comments

Never, Ever Use Clustered Indexes

SQL Server
This whole concept of the clustered index as a foundational structure within SQL Server is just plain nuts. Sure, I get the concept that if a table has a clustered index, then that index actually becomes the table. When you create a clustered index on a table, the data is now stored at the leaf level of the Balanced Tree (b-tree) page distribution for that index, and I understand that retrieving the data using a seek on that index is going be extremely fast because no additional reads are necessary. Unlike what would happen with a non-clustered index on a heap table. Yes, I get that if I store my data in a heap, the only way to access the data is through the Index Allocation Mapping (IAM)  pages that…
Read More
28May 2012 by Grant Fritchey 3 Comments

Rolling out Windows 8 Client: First Impressions

Misc
A virus found it’s way on to my system through a trojan horse, past all the security & anti-virus software. I went through the process of cleaning up, but I kept finding pieces of functionality that had been negatively impacted. Further, I was getting lots of Blue Screen of Death (BSOD) crashes. This was all on my beautiful Lenovo W510 that I use constantly and that I need for demo’s while on the road. It needed a rebuild. When it came to doing the work, I planned on a Windows 7 install. It had been working on the machine for about 18 months without any issues at all so I figured I’d just go back to it… But, Windows 8 is coming out. So, for deep technical reasons that I’ll…
Read More
21May 2012 by Grant Fritchey 11 Comments

Is Dynamic T-SQL a Good Design Pattern?

T-SQL
In a recent discussion it was suggested to me that not only is dynamic T-SQL useful for things like catch-all queries or some really hard to solve problems involving variable table lists, but is, in fact, a perfectly acceptable design pattern for all queries against a database. Note, in this case, we’re not talking about an ORM tool which takes control of the system through parameterized queries, but rather an intentional choice to build nothing but dynamic T-SQL directly on the system. To me, this was immediately problematic. I absolutely agree, you’re going to have dynamic T-SQL for some of those odd-ball catch-all search queries. But to simply expand that out to include all your queries is nuts. There really is a reason that stored procedures exist, and it’s not…
Read More
16May 2012 by Grant Fritchey 18 Comments

Don’t You Know Who I Am?

PASS, Professional Development
I’m happy to say that for most of you out there, the answer to this question is “no.” That’s as it should be. I’m not anyone all that special. I present technical sessions at various events from local user group meetings to SQL Saturday’s to international events like the PASS Summit and 24 Hours of PASS. Why? Not because the attendees know my name, that’s for sure. It’s because of a combination of at least two of these three things: The organizers might know my name or may know of my books or the fact that I’m working for a vendor that sponsors their event I’ve submitted sessions that seem to be of interest to the people who might be attending the event being organized I’ve got a track record…
Read More
07May 2012 by Grant Fritchey 1 Comment

Which SELECT * Is Better?

SQL Server, T-SQL
The short answer is, of course, none of them, but testing is the only way to be sure. I was asked, what happens when you run ‘SELECT *’ against a clustered index, a non-clustered index, and a columnstore index. The answer is somewhat dependent on whether or not you have a WHERE clause and whether or not the indexes are selective (well, the clustered & non-clustered indexes, columnstore is a little different). Let’s start with the simplest: SELECT    * FROM    Production.ProductListPriceHistory AS plph; This query results in a clustered index scan and 5 logical reads. To do the same thing with a non-clustered index… well, we’ll have to cheat and it’ll look silly, but let’s be fair. Here’s my new index: CREATE NONCLUSTERED INDEX TestIndex ON Production.ProductListPriceHistory (ProductID,StartDate,EndDate,ListPrice,ModifiedDate); When I…
Read More