Why Your WHERE Clause Ignores a Perfectly Good Index
You add an index on a column in the WHERE clause, run the query again, and still get a scan. Before adding another index, I look at how the ...
SQL Server
Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts
Deleting Millions of Rows in SQL Server, One Batch at a Time
I run scheduled jobs on several systems that remove data past its retention window. Depending on the system, that might mean anything older ...
Two Ways to Mask PII in SQL Server (and When Each One Fits)
Production data has a way of following an application into development and test. A database restore brings over the records needed to reprod...
SQL Code Generation Tool
The SQL Code Generation Tool is an open source project that I created to help automate the creation of stored procedures, model objects, a...
Using SQL Server SQLCMD and WinZip wzzip to backup and zip/encrypt a database
Here's an example of how to create a BAT file to backup a SQL Server database using SQLCMD and to zip the backup file using WinZip wz...
Shredding XML Data in SQL Server: Elements and Attributes
SQL Server can pull values out of an XML document and hand them back as ordinary rows and columns. The syntax depends on whether your data l...
sys.dm_exec_describe_first_result_set: Getting a Stored Procedure's Result Metadata
If you ever need to work out, at runtime, what columns a stored procedure is going to return, SQL Server has a dynamic management function f...
SQL Server Reorganizing and Rebuilding Indexes
"The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlyin...
SQL Server Data Type Mappings (ADO.NET)
"SQL Server and the .NET Framework are based on different type systems. For example, the .NET Framework Decimal structure has a max...
Configure SQL Server Agent Mail to Use Database Mail
Email notifications stopped working for a SQL Server Agent job, and this was in the error log: [264] An attempt was made to send an email ...
SSMS Error 916: The server principal is not able to access the database
This is the error I hit in SQL Server Management Studio when trying to expand the Databases node: Failed to retrieve data for this request...
SSMS: Saving Changes Is Not Permitted
If you change a table in the SQL Server Management Studio designer and try to save, you may get this: Saving changes is not permitted. The...
DATE, TIME, and DATETIME2 in SQL Server
Before SQL Server 2008, if you needed to store a date you used DATETIME , whether or not you cared about the time. If you needed only a time...
Displaying the Sizes of Your SQL Server's Database's Tables
SQL Server has a handy system stored procedure called sp_spaceused that reports the space used by a database or by an individual table. To ...
Temporary Tables and Dynamic SQL
When programming in SQL you sometimes need to create a temporary table inside a stored procedure. That part is straightforward: SELECT Tra...
Creating a Data Access Layer with LINQ to SQL
Building a genuine data access layer on top of LINQ to SQL raises a question that is harder than it looks: where do you draw the line betwee...
SCOPE_IDENTITY vs @@IDENTITY in SQL Server
This one is one of the basics that all SQL programmers learn at some point, sometimes the hard way. @@IDENTITY returns the most recently c...
Setting a variable from dynamic SQL
I do not have to do this often, but it comes in handy in certain situations. Setting a variable from dynamic SQL: DECLARE @MyValue INT ...
SQL Server GetAge Function
A simple function to calculate age from a date of birth. It handles the two cases that trip people up: a birthday later in the current year,...
SQL Server GetStartOfWeek Scalar-Valued Function
This function is useful if you need to find the first day of the week for a given date. Because different clients and applications define th...