Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Your profile

Search




Main Categories

Categories

November 2008
Mon Tue Wed Thu Fri Sat Sun
 << <   > >>
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

Tools

XML Feeds

What is RSS?

Tag cloud

.net .net 3.5 asp.net azure book browser bug c# chrome cloud computing database design patterns flash free functions generate google hotfix howto indexing interview kilimanjaro linq linux nhibernate nunit performance problem programming resharper review rhino mocks ria silverlight sql sql server sql server 2005 sql server 2008 sqlserver structuremap t-sql tip toread trick unit testing vb.net video windows forms windows presentation foundation wpf

Tags: tip

Data Management

The Data Management Journal

How To Convert IP Addresses Between Bigint and Varchar

by SQLDenis


Permalink 05 Oct 2008 12:06 , Categories: Data Modelling & Design Tags: how to, sql, sql server 2008, tip

Before we start with code let us take a sample IP address, does 127.0.0.1 look familiar? Yes that is your local IP address.

Here it is in decimal and binary
127 0 0 1
01111111 00000000 00000000 00000001

Now to convert, you would take the first value,
add the second value + 256
add the third value + (256 * 256) = 65536
add the fourth value + (256 * 256 * 256) =16777216

So in our case the select would be

tsqlLine number On/Off | Show/Hide | Select all
  1. SELECT
  2. 1 +
  3. 0 * 256 +
  4. 0 * 65536 +
  5. 127 * 16777216

which is
2130706433

So to convert from IP Adress to integer is very simple, you use PARSENAME to split it up and do the math. Here is the function.

tsqlLine number On/Off | Show/Hide | Select all
  1. CREATE FUNCTION dbo.IPAddressToInteger (@IP AS VARCHAR(15))
  2. RETURNS BIGINT
  3. AS
  4. BEGIN
  5.  RETURN (CONVERT(BIGINT, PARSENAME(@IP,1)) +
  6.          CONVERT(BIGINT, PARSENAME(@IP,2)) * 256 +
  7.          CONVERT(BIGINT, PARSENAME(@IP,3)) * 65536 +
  8.          CONVERT(BIGINT, PARSENAME(@IP,4)) * 16777216)
  9.  
  10. END
  11. GO

But how do you get 127.0.0.1 out of 2130706433?
It is the reversed of what we did before (surprise) so instead of multiplying we will be dividing
Here is the funcion

tsqlLine number On/Off | Show/Hide | Select all
  1. CREATE FUNCTION dbo.IntegerToIPAddress (@IP AS BIGINT)
  2. RETURNS VARCHAR(15)
  3. AS
  4. BEGIN
  5.  DECLARE @Octet1 TINYINT
  6.  DECLARE @Octet2 TINYINT
  7.  DECLARE @Octet3 TINYINT
  8.  DECLARE @Octet4 TINYINT
  9.  DECLARE @RestOfIP BIGINT
  10.  
  11.  SET @Octet1 = @IP / 16777216
  12.  SET @RestOfIP = @IP - (@Octet1 * 16777216)
  13.  SET @Octet2 = @RestOfIP / 65536
  14.  SET @RestOfIP = @RestOfIP - (@Octet2 * 65536)
  15.  SET @Octet3 = @RestOfIP / 256
  16.  SET @Octet4 = @RestOfIP - (@Octet3 * 256)
  17.  
  18.  RETURN(CONVERT(VARCHAR, @Octet1) + ‘.’ +
  19.         CONVERT(VARCHAR, @Octet2) + ‘.’ +
  20.         CONVERT(VARCHAR, @Octet3) + ‘.’ +
  21.         CONVERT(VARCHAR, @Octet4))
  22. END

Now let’s try this out, first run this

tsqlLine number On/Off | Show/Hide | Select all
  1. SELECT dbo.IPAddressToInteger(‘127.0.0.1′)

That returns 2130706433
Now run this

tsqlLine number On/Off | Show/Hide | Select all
  1. SELECT dbo.IntegerToIPAddress(2130706433)

That returns 127.0.0.1

Thanks to K. Brian Kelley for the inspiration for this post, you can also check http://www.truthsolutions.com/ to see some of his books

And also check out the related Order IP Addresses wiki article which I wrote a while ago

2 comments » • Send a trackback » 511 views

ISO Week In SQL Server

by SQLDenis


Permalink 22 Sep 2008 10:03 , Categories: Data Modelling & Design Tags: dates, functions, how to, iso, iso week, sql server 2008, tip

ISO Week in SQL Server

First let’s take a look at what ISO week is, from WikiPedia:
Week date representations are in the format as shown below.
YYYY-Www or YYYYWww
YYYY-Www-D or YYYYWwwD

[YYYY] indicates the so-called ISO year which is slightly different than the calendar year (see below). [Www] is the week number prefixed by the letter ‘W’, from W01 through W53. [D] is the weekday number, from 1 through 7, beginning with Monday and ending with Sunday. This form is popular in the manufacturing industries.
There are mutually equivalent definitions for week 01:

  • the week with the year’s first Thursday in it,
  • the week with 4 January in it,
  • the first week with the majority (four or more) of its days in the starting year, and
  • the week starting with the Monday in the period 29 December – 4 January.

If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year.
The week number can be described by counting the Thursdays: week 12 contains the 12th Thursday of the year.
The ISO year starts at the first day (Monday) of week 01 and ends at the Sunday before the new ISO year (hence without overlap or gap). It consists of 52 or 53 full weeks. The ISO year number deviates from the number of the calendar year (Gregorian year) on a Friday, Saturday, and Sunday, or a Saturday and Sunday, or just a Sunday, at the start of the calendar year (which are at the end of the previous ISO year) and a Monday, Tuesday and Wednesday, or a Monday and Tuesday, or just a Monday, at the end of the calendar year (which are in week 01 of the next ISO year). For Thursdays, the ISO year number is always equal to the calendar year number.
Examples:

  • 2008-12-29 is written “2009-W01-1″
  • 2010-01-03 is written “2009-W53-7″

You can read more about ISO week here: http://en.wikipedia.org/wiki/ISO_week_date

Sometimes you need to show the ISO week in SQL server but there was no built in way to calculate it until SQL server 2008 was released. In SQL Server 2000/2005 you could use the user defined function ISOweek which was in the SQL Server books on line.
Here is what the function looks like

tsqlLine number On/Off | Show/Hide | Select all
  1. CREATE FUNCTION ISOweek  (@DATE DATETIME)
  2. RETURNS INT
  3. AS
  4. BEGIN
  5.    DECLARE @ISOweek INT
  6.    SET @ISOweek= DATEPART(wk,@DATE)+1
  7.       -DATEPART(wk,CAST(DATEPART(yy,@DATE) AS CHAR(4))+‘0104′)
  8. –Special cases: Jan 1-3 may belong to the previous year
  9.    IF (@ISOweek=0)
  10.       SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
  11.          AS CHAR(4))+‘12′+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
  12. –Special case: Dec 29-31 may belong to the next year
  13.    IF ((DATEPART(mm,@DATE)=12) AND
  14.       ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
  15.       SET @ISOweek=1
  16.    RETURN(@ISOweek)
  17. END
  18. GO

Now run the following query on SQL server 2000 and up

tsqlLine number On/Off | Show/Hide | Select all
  1. SELECT dbo.ISOweek(‘20071231′),DATEPART(wk,‘20071231′)


If you are running SQL server 2008 then you can use DATEPART and the datepart argument isowk. Run the select statement below to see the result

tsqlLine number On/Off | Show/Hide | Select all
  1. SELECT DATEPART(isowk,‘20071231′),DATEPART(wk,‘20071231′)

As you can see here also SQL Server’s wk part returns 53 while isowk returns 1

I have also added parts of this to the wiki here: ISO Week In SQL Server

4 comments » • Send a trackback » 993 views

What is deferred name resolution and why do you need to care?

by SQLDenis


Permalink 08 Sep 2008 08:23 , Categories: Data Modelling & Design Tags: gotcha, howto, sql server, t-sql, tip, trick

So I posted a teaser in the puzzles forum. Without running this, try to guess what will happen?

tsqlLine number On/Off | Show/Hide | Select all
  1. DECLARE @x INT
  2.  
  3. SET @x = 1
  4.  
  5. IF (@x = 0)
  6. BEGIN
  7.     SELECT 1 AS VALUE INTO #temptable
  8. END
  9. ELSE
  10. BEGIN
  11.    SELECT 2 AS VALUE INTO #temptable
  12. END
  13.  
  14. SELECT * FROM #temptable –what does this return

This is the error you get
Server: Msg 2714, Level 16, State 1, Line 12
There is already an object named ‘#temptable’ in the database.

You can do something like this to get around the issue with the temp table

tsqlLine number On/Off | Show/Hide | Select all
  1. DECLARE @x INT
  2.  
  3. SET @x = 1
  4.  
  5. CREATE TABLE #temptable (VALUE INT)
  6. IF (@x = 0)
  7. BEGIN
  8.     INSERT #temptable
  9.     SELECT 1
  10. END
  11. ELSE
  12. BEGIN
  13.     INSERT #temptable
  14.     SELECT 2
  15. END
  16.  
  17. SELECT * FROM #temptable –what does this return

So what is thing called Deferred Name Resolution? Here is what is explained in Books On Line

When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, the text of the stored procedure is stored in the syscomments system table.

When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the syscomments system table of the procedure and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.

In the resolution stage, Microsoft SQL Server 2000 also performs other validation activities (for example, checking the compatibility of a column data type with variables). If the objects referenced by the stored procedure are missing when the stored procedure is executed, the stored procedure stops executing when it gets to the statement that references the missing object. In this case, or if other errors are found in the resolution stage, an error is returned.

So what is happening is that beginning with SQL server 7 deferred name resolution was enabled for real tables but not for temporary tables. If you change the code to use a real table instead of a temporary table you won’t have any problem
Run this to see what I mean

tsqlLine number On/Off | Show/Hide | Select all
  1. DECLARE @x INT
  2.  
  3. SET @x = 1
  4.  
  5. IF (@x = 0)
  6. BEGIN
  7.     SELECT 1 AS VALUE INTO temptable
  8. END
  9. ELSE
  10. BEGIN
  11.    SELECT 2 AS VALUE INTO temptable
  12. END
  13.  
  14. SELECT * FROM temptable –what does this return

What about variables? Let’s try it out, run this

tsqlLine number On/Off | Show/Hide | Select all
  1. DECLARE @x INT
  2.  
  3. SET @x = 1
  4.  
  5. IF (@x = 0)
  6. BEGIN
  7.     DECLARE @i INT
  8.     SELECT @i = 5
  9. END
  10. ELSE
  11. BEGIN
  12.    DECLARE @i INT
  13.    SELECT @i = 6
  14. END
  15.  
  16. SELECT @i

And you get the follwing error
Server: Msg 134, Level 15, State 1, Line 13
The variable name ‘@i’ has already been declared. Variable names must be unique within a query batch or stored procedure.

Now why do you need to care about deferred name resolution? Let’s take another example from a blogpost I made a while back: Do you depend on sp_depends (no pun intended)

First create this proc

tsqlLine number On/Off | Show/Hide | Select all
  1. CREATE PROC SomeTestProc
  2. AS
  3. SELECT dbo.somefuction(1)
  4. GO

now create this function

tsqlLine number On/Off | Show/Hide | Select all
  1. CREATE FUNCTION somefuction(@id INT)
  2. RETURNS INT
  3. AS
  4. BEGIN
  5. SELECT @id = 1
  6. RETURN @id
  7. END
  8. Go

now run this

tsqlLine number On/Off | Show/Hide | Select all
  1. SP_DEPENDS ’somefuction’

result: Object does not reference any object, and no objects reference it.

Most people will not create a proc before they have created the function. So when does this behavior rear its ugly head? When you script out all the objects in a database, if the function or any objects referenced by an object are created after the object that references them then sp_depends won’t be 100% correct

SQL Server 2005 makes it pretty easy to do it yourself

tsqlLine number On/Off | Show/Hide | Select all
  1. SELECT specific_name,*
  2. FROM information_schema.routines
  3. WHERE object_definition(OBJECT_ID(specific_name)) LIKE ‘%somefuction%’
  4. AND routine_type = ‘procedure’
4 comments » • Send a trackback » 753 views

How Do You Check If A Temporary Table Exists In SQL Server

by SQLDenis


Permalink 22 Aug 2008 08:57 , Categories: Data Modelling & Design Tags: howto, programming, sql server, t-sql, tip, trick

I see more and more people asking how to check if a temporary table exists. How do you check if a temp table exists?

You can use IF OBJECT_ID(’tempdb..#temp’) IS NOT NULL Let’s see how it works

tsqlLine number On/Off | Show/Hide | Select all
  1. –Create table
  2. USE Norhtwind
  3. GO
  4.  
  5. CREATE TABLE #temp(id INT)
  6.  
  7. –Check if it exists
  8. IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
  9. BEGIN
  10. PRINT ‘#temp exists!’
  11. END
  12. ELSE
  13. BEGIN
  14. PRINT ‘#temp does not exist!’
  15. END
  16.  
  17. –Another way to check with an undocumented optional second parameter
  18. IF OBJECT_ID(‘tempdb..#temp’,‘u’) IS NOT NULL
  19. BEGIN
  20. PRINT ‘#temp exists!’
  21. END
  22. ELSE
  23. BEGIN
  24. PRINT ‘#temp does not exist!’
  25. END
  26.  
  27.  
  28.  
  29. –Don’t do this because this checks the local DB and will return does not exist
  30. IF OBJECT_ID(‘tempdb..#temp’,‘local’) IS NOT NULL
  31. BEGIN
  32. PRINT ‘#temp exists!’
  33. END
  34. ELSE
  35. BEGIN
  36. PRINT ‘#temp does not exist!’
  37. END
  38.  
  39.  
  40. –unless you do something like this
  41. USE tempdb
  42. GO
  43.  
  44. –Now it exists again
  45. IF OBJECT_ID(‘tempdb..#temp’,‘local’) IS NOT NULL
  46. BEGIN
  47. PRINT ‘#temp exists!’
  48. END
  49. ELSE
  50. BEGIN
  51. PRINT ‘#temp does not exist!’
  52. END
  53.  
  54. –let’s go back to Norhtwind again
  55. USE Norhtwind
  56. GO
  57.  
  58.  
  59. –Check if it exists
  60. IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
  61. BEGIN
  62. PRINT ‘#temp exists!’
  63. END
  64. ELSE
  65. BEGIN
  66. PRINT ‘#temp does not exist!’
  67. END

now open a new window from Query Analyzer (CTRL + N) and run this code again

tsqlLine number On/Off | Show/Hide | Select all
  1. –Check if it exists
  2. IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
  3. BEGIN
  4. PRINT ‘#temp exists!’
  5. END
  6. ELSE
  7. BEGIN
  8. PRINT ‘#temp does not exist!’
  9. END

It doesn’t exist and that is correct since it’s a local temp table not a global temp table

Well let’s test that statement

tsqlLine number On/Off | Show/Hide | Select all
  1. –create a global temp table
  2. CREATE TABLE ##temp(id INT) –Notice the 2 pound signs, that’s how you create a global variable
  3.  
  4. –Check if it exists
  5. IF OBJECT_ID(‘tempdb..##temp’) IS NOT NULL
  6. BEGIN
  7. PRINT ‘##temp exists!’
  8. END
  9. ELSE
  10. BEGIN
  11. PRINT ‘##temp does not exist!’
  12. END

It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)

tsqlLine number On/Off | Show/Hide | Select all
  1. –Check if it exists
  2. IF OBJECT_ID(‘tempdb..##temp’) IS NOT NULL
  3. BEGIN
  4. PRINT ‘##temp exists!’
  5. END
  6. ELSE
  7. BEGIN
  8. PRINT ‘##temp does not exist!’
  9. END

And yes this time it does exist since it’s a global table

I have also added this to our wiki, read it here: Check If Temporary Table Exists

Leave a comment » • Send a trackback » 1249 views

Tools


RssFeeds

Blogs


Become a blogger

Statistics


Top 10 rated users for the last 7 days

User Ratings
Emtucifor16
Remou13
AlexCuse10
ZmrAbdulla10
j460610
damber6
SQLDenis4
ptheriault3
Thirster421
onpnt1

Links



With Thanks To...


Powered by B2Evolution
Valid XHTML | Valid CSS