Tag: SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Having system named objects is one of those things that people may debate, but as you move to more automated and scripted processes can cause you issues. In the case of a Primary Key (PK), if you do something like this:

CREATE TABLE OrderDetail
    (
      OrderID INT IDENTITY(1, 1)
                  PRIMARY KEY ,
      OrderDate DATETIME
    );

What you get is something like this:

When you compare that with the same table in another database, what’s the likelihood that you’ll have the PK named PK__OrderDet__D3B9D30C7D677BB4? Probably pretty low.

This means that if you are looking to deploy changes, and perhaps compare the deployment from one database to the next, you’ll think you have different indexes. Most comparison tools will then want to change the index on your target server, which might be using this technique. Or the choice might be something that performs much worse.

What we want to do is get this named the same on all databases. In this case, the easiest thing to do with rename the constraint on all systems. This is easy to do with sp_rename, which is better than dropping and rebuilding the index.

I can issue an easy query to do this:

exec sp_rename ‘PK__OrderDet__D3B9D30C7D677BB4’, ‘OrderDetail_PK’

When do this, I see the object is renamed.

This table has over a million rows, and while that’s not large, it does take time time to rebuild the index. With a rename, the change is to the metadata and takes a split second.

SQLNewBlogger

These quick, easy, administrator items are great to blog about. As an exercise, if this table has millions of rows, how much longer does the index rebuild take?

This is a great topic for you to write about (or learn about) and show how you can better administer your SQL Server databases.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I haven’t used sp_executesql much. Instead, my habitually way of executing dynamic SQL has been with EXEC(). There are a few differences between these commands, but I had to look at sp_executesql recently and realized I didn’t know much about it.

One of the neat things with sp_executesql is that you can pass in parameters.  That’s pretty cool. I hadn’t ever bothered, but if you read the docs, you’ll see that if you execute the same code over and over, with different parameters, you might get the same execution plan. This can be a performance boost.

NOTE: THIS IS NOT ALWAYS BETTER. It can be.

I’m not going to delve into deep details, but Kimberly Tripp does so read her post (and then write your own thoughts).

The Code

Here’s some code to demonstrate. I have a simple table with 3 columns to insert. In this case, here’s my insert:

INSERT EventLogger VALUES (@m, @d, @u)

Now, I went to use this over and over, but with different values for the parameters. Obviously I can just do this:

…

SET @m = ‘Error Message’

INSERT EventLogger VALUES (@m, @d, @u)

SET @m = ‘New Error Message’

INSERT EventLogger VALUES (@m, @d, @u)

…

However, imagine that I’m building this INSERT string dynamically because it’s more complex. How do I execute this over and over with new values? With EXEC(), I rebuild the string. With sp_executesql, I do this:

DECLARE @cmd NVARCHAR(MAX)
DECLARE @dt DATETIME = GETDATE();
DECLARE @msg VARCHAR(200) = ‘An error occured’;
DECLARE @usr VARCHAR(10) = ‘Steve’;
DECLARE @p NVARCHAR(500);

SELECT @cmd = N’INSERT EventLogger VALUES (@m, @d, @u)’

SELECT @p = N’@m varchar(200), @d datetime, @u varchar(10)’

EXEC sp_executesql @cmd, @p, @m = @msg, @d = @dt, @u = @usr;

SELECT @dt = GETDATE()
     , @msg = ‘A new error occured’
     , @usr = ‘Bob’;

EXEC sp_executesql @cmd, @p, @m = @msg, @d = @dt, @u = @usr;
GO
SELECT top 10
  *
FROM dbo.EventLogger AS el

Now, I check the table:

I thought that was cool.

SQLNewBlogger

This isn’t a deep post. It’s a light look, with a little explanation. I’ll do more later. However, I’m hoping this serves as a way to show you how to start investigating a topic. I’ve spent a bit of time experimenting and learning. I’m fairly confident I could play and use sp_executesql more.

You could do this as well, start digging into a topic and then show how you’re learning.