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
A pet dislike of mine is application developers creating connection strings, but without Application Names. That’s painful because when you view the processes in SQL Server Activity Monitor, or look at commands in traces from SQL Server Profiler or Extended Events Profiler, you see this:
If your connection just says .Net SqlClient Data Provider, no-one has any idea what application that is. If you are using another connection library, it might say a different name but the concept is the same. You are making management of the server much harder than it needs to be.
2018-12-24
System configurations are becoming more complex all the time. Each and every server, container, and key application and service today has log files that tell you a wealth about what’s going on under the covers. But how accessible are those log files in your organization?
If you aren’t using a log management tool, you probably should be.
Here are a few easy ones to get started with:
One thing that I do find frustrating with Microsoft tooling at present is the constant churn of product names. A while back, we had Application Insights that could collect details of what was happening within an app. The data for that was stored in a tool called Log Analytics, and it could also collect operating system logs and more. Agents were provided for on-premises systems.
2018-12-20
Currently, SQL Server query plans are stored as XML. You can see what they look like by right-clicking in any query plan in SQL Server Management Studio (SSMS), and clicking Show Execution Plan XML:
That will return a whole bunch of XML like this:
It’s important to understand that when SSMS is showing a graphical execution plan, it’s just graphically rendering some XML like the plan above.
The Properties window in SSMS is also showing details extracted from that same XML.
2018-12-20
