March 2009 - How Tos

Sort by: Most Recent | Most Viewed | Most Commented

  • 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