How Tos » All Tags » Beginner SQL (RSS)

Browse by Tags

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

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

  • 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...
Copyright SQL Server Nation 2010
Powered by Community Server (Non-Commercial Edition), by Telligent Systems