Blogs

Sort By: Most Recent | Most Viewed | Most Commented

  • How to handle a Dynamic IN() list with SQL Server

    I often see a variable of the following question in SQL Server forums: " SELECT * FROM mytable WHERE id in ('11','12','13') is there a datatype that I can declare that will hold ('11','12','13')? i.e. can I do something like: declare @myComparisionVaules [???] set @myComparisionVaules = ('11','12','13') SELECT * FROM mytable WHERE id in @myComparisionVaules" So, the basic question really is "I have a list of values...
    Posted to Tip of the Week by TimChapman on 8/31/2010

  • How to calculate SQL Server uptime

    In this article I take a look at a couple of handy scripts to calculate SQL Server up-time for reporting purposes. What is "Up-time"? First, let me explain what I mean by "up-time", as there are a few different ways to describe it. In this scenario, I am really referring to the instance being up and running. I am not taking into account if certain critical user databases are not accessible or if something is up with the network and users are not able to make connections (although...
    Posted to Tip of the Week by TimChapman on 8/8/2010

  • Quickly Generate a Number Sequence in SQL Server

    If you do not currently have a numbers table setup in your database (which you should - they're invaluable), you quickly and easily generate a large list of sequential values using some crafty SQL statements and an internal SQL Server table. I'll be using the undocumented spt_values table in the master database. This table holds a potpourri of different database values that can be used for different reasons...and it also holds a small series of sequential numbers. I can use these sequential...
    Posted to Tip of the Week by TimChapman on 6/27/2010

  • EXISTS() and COUNT(*) tricked me

    So, every once in a while I really go brain-dead and do something silly that I laugh about later. This happened earlier this week, so I thought I'd write about it. I was working on a little presentation to a group of developers on writing performant SQL Server queries. I was in the process of writing an EXISTS() statement, and noticed that the execution plan didn't show the statement going out to disk to retrieve records. It was returning SUPER FAST. In my mind-set at the time, I thought...
    Posted to TimChapman by TimChapman on 6/25/2010

  • SQL Server Triggers

    Handling trigger is a very simple, and very misunderstood facet of SQL Server programming. Today I'll take a look some very common trigger scenarios, some mistakes made, and how you can program around them. First, a little bit of background of the types of triggers available in SQL Server. Trigger Types There are two different types of SQL Server triggers to date: INSTEAD OF triggers and AFTER triggers. INSTEAD OF triggers are placed on tables and views (most often views) and are invoked INSTEAD...
    Posted to Tip of the Week by TimChapman on 2/28/2010

  • Getting started with dynamic SQL

    Sometimes you need dynamic SQL. Sometimes you don't know at run time where you're querying or what fields you need to bring back, or what your criteria will be, so there will always be a place for the use of dynamic SQL. You have to be careful how you use it, but if you do use it correctly it can be a powerful tool that you have in your tool belt. So, today I am going to show you a nice little general purpose procedure that you can write to pass in a few object strings and execute dynamic...
    Posted to Development by TimChapman on 12/7/2009

  • Development Environment Isolation

    One of the challenges facing many development organizations is how to provide adequate development needs while providing enough isolation to prevent developer conflicts. Today's virtual technology provides the ability to spin up developer specific environments quickly easily while providing the horsepower to mimic near physical environment capacity. I will leave the discussions over physical vs. virtual to discuss in another area, I will just talk about my recent experience, and how handling...
    Posted to BrandonGalderisi by BrandonGalderisi on 11/17/2009

  • Creating Indexes with IGNORE_DUP_KEY

    Unique indexes and unique constraints limit the values that are allowed in a table column to unique values. This uniqueness is almost always a result of some type of business rule. Programming these types of business rules are usually not overcomplicated to write. However, SQL Server has an option that you can set when creating unique indexes that allows you to control how duplicate values are handled so that you can potentially get away from writing these rules by hand. The unique index option,...
    Posted to Development by TimChapman on 11/15/2009

  • What version of SQL Server am I running?

    Quite often I see the question in the SQL Server forums "What version of SQL Server am I running?" To be honest, this is a VERY easy question to answer. Microsoft provides a function with a set of properties you can query for identifying such information. The function provided by Microsoft is SERVERPROPERTY. However, there are specific parameters you need to pass to it to get the information you need. So, rather than having to worry about these specific parameter values, I've created...
    Posted to Tip of the Week by TimChapman on 11/1/2009

  • Create a system stored procedure

    A system stored procedure is a proc that is stored in the master database that you can run in the context of other databases on the instance. For example, sp_msforeachtable is an example of a system stored procedure. You can run this procedure in any database on the instance and it will loop through the tables in the database and issue a command against each. As an example, here is a stored procedure I wrote when SQL Server 2005 first came out. This procedure uses a dynamic management view to recurse...
    Posted to How Tos by TimChapman on 10/29/2009

  • run_duration in SQL Server system tables

    If you are interested in the run duration of the jobs on your SQL Server you would most likely query the system tables sysjobs and sysjobhistory from the msdb database in a manner similar to this: select name ,enabled ,run_status ,run_date ,run_time ,run_duration from msdb.dbo.sysjobs sj join msdb.dbo.sysjobhistory sjh on sj.job_id = sjh. What is interesting (and yes, it could be misleading!) is that the run_duration is presented by the following formula: (hours*10000) + (minutes*100) + seconds The...
    Posted to How Tos by Feodor Georgiev on 10/26/2009

  • Why is this query slow: 1

    Since this is the first part of this series, I'll give a little background as to what I want to accomplish. At some point, you're going to run into a situation where a query on your SQL Server system is "slow". Slowness is usually ambiguous, and almost always relates to a persons reference. However, when we hear that something is slow or broken we need to check it out and see what may be the cause. So, my goal is to introduce simple to increasingly complex queries and show you the...
    Posted to Tip of the Week by TimChapman on 10/24/2009

  • sp_MSForEachDB - leashed and unleashed

    First things first - a few words on Microsoft undocumented procedures (and tools): it is this simple - USE THEM WITH CAUTION. There are several out there, and they are all great. If you compare sp_who (documented) with sp_who2 (UN-documented) you will see the advantages. In the case of sp_MSForEachDB (UN-documented), there are several warnings I would like to bring to your attention: 1. Do not abuse it, since it is a global cursor. 2. UN-documented means that if something goes wrong, you will not...
    Posted to How Tos by Feodor Georgiev on 10/9/2009

  • Viewing Performance Counters in SQL Server 2005

    I like Performance Monitor (perfmon); its a good tool for diagnosing many different types of problems. Its great for determining from how well your memory is handling requests to how well your CPU usage is doing...its also great for checking out different SQL Server metrics. In SQL Server 2005, the sys.dm_os_performance_counters Dynamic Management View (DMV) is introduced to allow you to query SQL Server related performance counters directly through the database engine. This makes is very useful...
    Posted to Tip of the Week by TimChapman on 9/30/2009

  • SQL Server 2008 Syntactic Sugar

    As a developer, I always enjoy new TSQL language features that can save me some time and potentially effort in accomplishing the same task that I otherwise could in a different fashion. These features aren't really a big deal to me to have but are sometimes fun. SQL Server 2008 has produced a couple of these features so I thought it might be worthwhile to take a look at them. Compound Operators This is a feature that exists in most common-day programming languages. The idea is simple; why use...
    Posted to Development by TimChapman on 9/28/2009
1 2 3 4 5 Next >
Copyright SQL Server Nation 2010
Powered by Community Server (Non-Commercial Edition), by Telligent Systems