Posts Tagged ‘TSQL’

Functions & sargable queries

Using functions improperly in your where clauses always prevents index usage right? I’ve been reviewing some queries generated by linq and I’ve found out this isn’t always the case. A quick demo… Create a temp tables and insert some data ?View Code TSQLCREATE TABLE #test ( id INTEGER NOT NULL PRIMARY KEY CLUSTERED, [name] VARCHAR(100) NOT NULL ); […]

Are you checking for possible integer overflow?

I realized I wasn’t! We run a couple of systems that I know stick a mass of records through on a daily basis. Better start doing this then or I might end up doing a whoopsie! Here’s a script I’ve quickly knocked up to make checking this simple. This script will check the specified tables TINYINT, SMALLINT, INT & BIGINT values […]

Table-Valued Parameters need an alias!

I’m always the one to say RTFM but this one stumped me for a while. I had problems using a Table-Valued Parameter in a Stored Procedure today. The explanation is indeed in the documentation but it’s perhaps not as clear as it should be. Hopefully this post will be picked up better in Google for […]

Purging data & Partitioning for Paupers

Several months ago at work we started having some terrible problems with some jobs that purge old data from our system. These jobs were put into place before my time, and while fine at the time, were now causing us some big problems. Purging data would take hours and cause horrendous blocking while they were […]

TRIGGER_NESTLEVEL TSQL Function

This function is used to determine the current nest level or number of triggers that fired the current one. This could be used to prevent triggers from firing when fired by others. Here’s an example that does that; we have two tables with triggers, that fire AFTER INSERT, and insert into the other table. The […]