SQL Tact - Blogs - SQLServerCentral

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

SQL Tact

Use the Default System Trace to Find Autogrowth Events

The SQL Server Management Studio built-in standard report for "Disk Usage" is very handy to view the performance-sucking autogrowth events for a database... until you want to see if autogrowth events have been occurring in any database on the server.

Autogrowth events are very problematic and can cause sudden, unpredictable…

0 comments, 137 reads

Posted in SQL Tact on 3 May 2013

Hunt Down Tables Without Clustered Indexes

This one's a gimme, but an underrated utility script when I enter a new environment and look for low-hanging fruit for easy improvements.

You'd be surprised how many vendors ship applications without a single index, much less properly aligned clustered indexes.

Heaps are bad. You can't defragment a heap by…

8 comments, 1,188 reads

Posted in SQL Tact on 3 May 2013

TRY_CONVERT broken by WITH SCHEMABINDING in SQL Server 2012 Data Tools build

Here's a bug you might find if attempting to schemabind an object that uses the new TRY_CONVERT function.

To reproduce, run the following code in a SSDT database solution:

CREATE VIEW [dbo].[aview] WITH SCHEMABINDING 
AS 
SELECT id = TRY_CONVERT(int, id) 
FROM dbo.arealtable

The database project should build with this error:

0 comments, 234 reads

Posted in SQL Tact on 21 March 2013

Conversation History: The Continuing Case Against GUIDs

We had a cooperative relational database design exercise at the office last week as part of our regular department "Lunch and Learn" series, and inevitably one topic that came up is the use of GUIDs as the unique key for a table.

And before long, I had been goaded onto…

0 comments, 618 reads

Posted in SQL Tact on 8 March 2013

YYYYMM Math

I had a project recently where I had to do math on a number that represented a date, YYYYMM.

For example, February 2013 would be stored as an integer, 201302.

As an aside, YYYYMM makes WAY more sense than ever storing anything MMYYYY. Why? For one, it sorts correctly. And…

0 comments, 145 reads

Posted in SQL Tact on 1 March 2013

"An item with the same key has already been added."

In SQL Server Reporting Services, when adding a new dataset to a report, you may see an error that looks like this:

"An item with the same key has already been added."

Based on some quick googling, it looks like there are several potential causes for the error, but here's…

0 comments, 184 reads

Posted in SQL Tact on 28 February 2013

Script Out Foreign Keys With Multiple Keys

It's easy enough to use sys.foreign_keys and sys.foreign_key_columns to identify foreign keys. But what if you want to script out your foreign keys (and only your foreign keys)... that have compound primary keys?

For example,

--Script 1
create table dbo.fktable1(
  id1 int not null
, id2 int not null
, id3 int…

0 comments, 165 reads

Posted in SQL Tact on 18 February 2013

Using Foreign Keys to Determine Table Insertion Order

Here's a script to determine, based on your database's foreign key relationships, what the insertion order would be for, say, a testing dataset.

with cteFK (pktable, fktable) as ( 
       select              
            pktable = o1.name 
       ,    fktable = isnull(o2.name, '')        
       from sysobjects o1       
       left join sysforeignkeys fk on o1.id = fk.fkeyid        
       left…

0 comments, 126 reads

Posted in SQL Tact on 17 February 2013

Adding the Clustered Key To Your Nonclustered Index? Part 2

Of course, a rather obvious answer presents itself the next day. After reviewing the actual environment that my colleague was working in, it popped quickly into mind.

What if the table has a compound primary key? See comments for a play-by-play.

drop table dbo.testclusteredinclude

go

create table dbo

0 comments, 136 reads

Posted in SQL Tact on 5 February 2013

Adding the Clustered Key To Your Nonclustered Index?

A colleague of mine was understandably confused when using the almost-perfect missing index engine in SQL 2008. The engine recommended he include the clustered index key in a new nonclustered index. Read that again - you're right, it doesn't make a lot of sense.

From my colleague:

Why would you ever need…

1 comments, 154 reads

Posted in SQL Tact on 4 February 2013

Try a Database Design Exercise At Your Next SQL User Group

Tried something different for the January 2013 meeting of the Baton Rouge SQL Server User Group (brssug.org), which meets every second Wednesday at the Louisiana Technology Park (latechpark.com).

I think this was one of the most enjoyable meetings we've ever had, and certainly the most interactive.…

0 comments, 298 reads

Posted in SQL Tact on 10 January 2013

On the Advantages of DateTime2(n) over DateTime

Starting with SQL 2008, we database developers started becoming more familiar with datetime2. Sometimes folks need convincing though, so here goes.

Here's a brief review of how the precision of the datetime2 data type converts from a varchar representing a time value out to one ten-millionths of a second. Run…

0 comments, 477 reads

Posted in SQL Tact on 4 December 2012

Management Studio Database Diagram Owners

If you're working in an environment as a developer without sysadmin privileges, and you are creating database diagrams using Management Studio underrated diagram tool, but not the database owner or a sysadmin, you'll see your created diagrams look like this

where the diagram is owned by the developer using the sql…

0 comments, 1,133 reads

Posted in SQL Tact on 3 December 2012

PowerPivot for Excel "Not loaded. The Managed Add-in Loader failed to initialize."

Error when trying to enable the PowerPivot for Excel add-in for Office 2010 reads:
"Not loaded. The Managed Add-in Loader failed to initialize."

If the installation of the PowerPivot for Excel add-in succeeded, the add-in should be enabled in the Excel Add-ins options panel. If not, you might see the…

0 comments, 735 reads

Posted in SQL Tact on 30 November 2012

Database Design Contest at the 2012 AITP Region 3 Student Conference

Had a great Friday in Lafayette, LA on the campus of the University of Louisiana-Lafayette. 31 different teams submitted database designs on my problem statement that required an ERD and sample data set. The database design contest was sponsored by the Baton Rouge SQL Server User Group, a chapter…

0 comments, 226 reads

Posted in SQL Tact on 14 October 2012

Performance Tuning Presentation from October 2012 BRSSUG Meeting

Thanks to the 15+ folks who attended the Baton Rouge SQL Server User Group meeting last night! I hope everyone enjoyed "everyone gets a book" night!

I presented on SQL Server Performance tuning, which began with Michelle Ufford's article from GoDaddy on how she saw massive performance gains just by…

0 comments, 299 reads

Posted in SQL Tact on 11 October 2012

How BETWEEN is inclusive

This is a old and simple one, but an important subtlety that TSQL developers must be aware of.

Using the BETWEEN syntax is easy enough in a WHERE clause, and it's pretty obvious for numbers.

use testing

go

create table decbetweentesting

( id int identity(1,1

4 comments, 497 reads

Posted in SQL Tact on 26 September 2012

SQL Server Best Practices for DMV's - Houston Tech Fest 2012

It is a pleasure meeting everyone today at Houston Tech Fest! The new facility is miles away - literally and figuratively - from the previous Houston Tech Fest facility.

Here's a link below for downloading the presentation files and sample scripts from my presentation on "SQL Server Best Practices for…

0 comments, 327 reads

Posted in SQL Tact on 8 September 2012

SQL Agent Alerts - Severity Condition is NOT "greater than or equal to"

I ran into some bad training that had convinced a client sys admin that the SQL Agent Alerts had the "greater than or equal to property."

As in, if I want to catch and notify an operator of all high severity errors 17 through 25, all I needed to do…

0 comments, 477 reads

Posted in SQL Tact on 20 August 2012

Actual Emails: The Customer Doesn't Want Full Recovery Mode

Got this question in an email. I'll paraphrase.

William

Hoping you can advise me on best way to handle this.

My client doesn’t want to use SQL backups. Instead, they are using VM and a third party backup software by choice.

Without a DBA on staff, they would…

2 comments, 319 reads

Posted in SQL Tact on 16 August 2012