How Tos

Sort by: Most Recent | Most Viewed | Most Commented

  • 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...

  • 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...

  • 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...

  • Determining Age through T-SQL

    Sometimes it is necessary to calculate a person's age. I've seen people use a simple datediff on YEAR but that is not sufficient. The reason why that is not sufficient is because it ONLY evaluates a difference on the YEAR value of the date. So that means from December 31, 2008 to January 1, 2009 it would return 1. Obviously that's not correct. You have to also compensate for the day of the year. There are a couple of ways this can be done. You can do inline SQL to calculate the value...

  • How to determine the Sunday of a given week!

    Sometimes it is necessary for reporting purposes to query data for a caldendar week. When doing so, us the the datepart function to retrieve the weeknum will not work because if a week spans more than one year it will only return the data for the same year. For example, January 1, 2009 is a Thursday. So the only dates that are in Week one of 2009 are January 1-3 (Thursday-Saturday). So how would you include from Sunday forward? The below function will help you. This function will return, for a given...

  • Splitting a string into a table of characters

    Sometimes you may not be able to see the characters in your data in SQL Server Mangement Studio (or another application). This simple function will allow you to pass an input string in, and it will return the individual characters as rows from a Table Valued Function. First the code: if object_id('[dbo].[fn_CharsToTable]') is not null drop function [dbo].[fn_CharsToTable] go create function [dbo].[fn_CharsToTable](@InputString varchar(max)) returns @Values TABLE (position int not null primary...

  • Updating values in one table with values from another table

    Sometimes, it is necessary to update a field in one table, with information from another table. In this article, I will go over with you a few different methods for doing this. At the bottom there is a link to download the entire SQL script used in this post. The first thing that we will do is create a few temporary tables to test with. We will be loading these temporary tables, intentionally leaving the count columns in the #Employers table NULL because after each update we will set them back to...

  • Hashing large data strings with a User Defined Function

    If you have ever tryed to hash data in SQL Server using SQL Server's build in HASHBYTES , you know that that it doesn't accept greater than 8000 bytes of data. While that may work for a lot of scenarios, it doesn't help when you have large strings. Below we will go over the basics of a function that I use for HASHING large data strings. The basic way that this function works is that it takes the input @String which is nvarchar(max), splits it into 8000 byte (4000 character) chunks and...

  • User Defined Functions

    User defined functions have many uses in SQL Server. Functions are useful to help clean up complex string manipulation logic in other areas of code and many other things. But convenience is not free. There is overhead in using functions and we will go over that later in the article. There are two types of User Defined Functions in SQL Server, Scalar Valued and Table Valued Functions. Scalar Valued functions are used to return a single value whereas Table Valued functions return a table. The table...

  • Table Valued Functions

    A table valued function is a function that returns a table instead of a single value. In this post, we will go over the Table Valued Functions in more detail than we did in the general topic covering User Defined Functions . There are many uses for table valued functions so we will go over a couple of examples to ensure that you will be successful when you begin to create your own. The first thing you need to understand is how you define a function as a Table Valued Function. This is done by defining...

  • A View of Numbers

    The below view will generate a list of numbers ranging from 1 - 4,294,967,296. This view can be used in place of a physical table of numbers for some small operations but since it utilizes several large cross joins it is not very efficient once it starts counting into large numbers. create view vw_Nums as with cte0 as (select 1 as c union all select 1), cte1 as (select 1 as c from cte0 a, cte0 b), cte2 as (select 1 as c from cte1 a, cte1 b), cte3 as (select 1 as c from cte2 a, cte2 b), cte4 as (select...

  • Converting a delimited string into a table

    In this article, I will show you how you can convert a delimited string of data into a table of data. In order to do this we will use a table valued function. There are many ways to achieve this but this how I like to do it. In order to use the function, you will need a numbers table. Since that's a topic that we haven't covered here yet, we will utilize a view that builds one on the fly. The view can be found in the A View of Numbers post. If you have a numbers table, replace "vw_nums"...

  • Stored Procedures

    Stored procedures are part of the core functionality of a DBMS. SQL Server is no exception to that. Stored procedures have many uses. They can be used to handle basic CRUD operations as well as complex logic. There are several things that you have to consider when it comes to building stored procedures for you database. Is this something that has to be done in the database? Is this something that should be done in the database? Does my database server have the capacity to handle this as it's...

  • Changing the display format of DATETIME values

    Often times, people want to change the format of a datetime value returned from SQL Server. There are some things that you need to consider before doing this. The first thing that you need to be aware of is that SQL Server stores DATETIME values in an 8 byte value in the database, not as a string. So changing the display format will change your DATETIME into a VARCHAR The SMALLDATETIME datatype is stored in a 4 byte value. SQL Server 2008 introduces several new date datatypes including DATETIME2...
Copyright SQL Server Nation 2010
Powered by Community Server (Non-Commercial Edition), by Telligent Systems