OK so you know that a table can either be a heap or it can have a clustered index. The first question of course, is which should I use?
If you have any doubt at all, put a clustered index on the table. Most SQL server tables will end up performing better that way. There are a few scenarios like log files where heaps will be preferable but if you’re not sure, start with a clustered index.
2019-01-07
I’ve also tackled a number of non-technical and non-scientific books recently. On that I’d heard good things about was Shrill (Notes from a loud woman) by Lindy West.
First note about this book is that it’s full of NSFW (not suitable for work) words, so be warned if that would offend you.
As well as being loud, Lindy has spent her life being big. I’d say that’s shaped her world view as much as anything else. Having been big much of my own life, I can directly relate to so many things she says.
2019-01-04
SQL Server execution plans can become quite large. That makes them hard to navigate because you are endlessly scrolling around the results pane in SQL Server Management Studio (SSMS).
The pane does have some zoom features. Note that if I right-click in the whitespace, I get these options:
So I can zoom in and out, set a custom zoom level, or zoom until the entire plan fits. Generally though, that would make the plan too small to read, as soon as you have a complicated plan.
2019-01-03
In our free SDU Tools for developers and DBAs, there’s a tool that makes it easy to create a script for creating SQL Server database users. No surprise, it’s called ScriptDatabaseUsers.
One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.
But if you just want to create a script, that works too.
2019-01-02
Hi Folks,
Just a short note today to wish everyone a happy new year. I know that new year is a time when many people make resolutions, with the intention of changing something about themselves during the upcoming year. It makes it a time full of hope for fresh starts.
I don’t tend to make too many resolutions as I think that needs to happen on an ongoing basis rather than once per year, but I understand why people do.
2019-01-01
One data type that’s often been quite controversial is the bit data type. So a bit is where we have an individual value, which is either a zero or one. They’re the only values allowed unless it’s NULL.
Always keep in mind when you’re working with bits in SQL server that they’re aren’t just 2 potential states, there are three. It can be zero, or one, or it can be NULL. That is, it can have no value at all.
2018-12-31
Another classic book that I’ve gone through again lately is The Selfish Gene by Richard Dawkins. I listened to the 40th Anniversary Edition on Audible as he was narrating it himself, and I particularly wanted to listen to the 40th Anniversary Edition to see how his own thoughts had changed over time.
Dawkins can be a polarizing figure. I’m mindful of how his work would have been received at the time it was written. I understand many of the comments that people make on him, but I find the vast majority are either misunderstanding him (you need to take him very literally but I find people read into his words, things that he’s not actually saying), or are feeling like their deep-held beliefs are being severely challenged. I have no doubt that the latter hurts.
2018-12-28
In an earlier post, I described how query plans could be saved as .sqlplan file, shared, and loaded again in SQL Server Management Studio (SSMS). It’s also possible to extract them out of SQL Server Profiler or Extended Events Profiler.
This is useful, but the same applies to deadlock graphs. SQL Server 2005 added Deadlock graph as a type of event in SQL Server Profiler. (It’s also part of Extended Events Profiler).
2018-12-27
I’m often wondering about how large different objects in my databases are. There are reports to provide that info, but I often want it in a programmatic form. One of our free SDU Tools for developers and DBAs does just that. It’s ListUserTableAndIndexSizes.
You can see how to execute it in the main image above. The procedure takes these parameters:
@DatabaseName sysname - This is the database to process @SchemasToList nvarchar(max) -> ‘ALL’ or comma-delimited list of schemas to list @TablesToList nvarchar(max) -> ‘ALL’ or comma-delimited list of tables to list @ExcludeEmptyIndexes bit -> 0 for list all, 1 for don’t list empty objects @ExcludeTableStructure bit -> 0 for list all, 1 for don’t list base table (clustered index or heap) @IsOutputOrderedBySize bit -> 0 for alphabetical, 1 for size descending
2018-12-26
Hi folks,
It’s been a big year again and the number of readers just keeps increasing quite fast. So pleased to have you all here. You make it all worthwhile. Thanks to so many who reached out during the year.
Just wanted to wish you all the best for the holiday and Christmas season, from here down under.
Greg
2018-12-25
