Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

March 2009 Blog Posts

NULL NOT IN conundrum

The scenario is very simple. You want to get all ID’s from one table that does not exists in another table. This is how the two simple tables are set up. DECLARE     @Table1 TABLE             (                          ID INT             ) INSERT      @Table1 SELECT      1 UNION ALL SELECT      2 UNION ALL SELECT      3 UNION ALL SELECT      4 UNION ALL SELECT      5 UNION ALL SELECT      6 UNION ALL SELECT      7 DECLARE     @Table2 TABLE             (                          ID INT             ) INSERT      @Table2 SELECT      1 UNION ALL SELECT      3 UNION ALL SELECT      4 UNION ALL SELECT      5 UNION ALL SELECT      7 UNION...

posted @ Thursday, March 19, 2009 10:02 AM | Feedback (7) | Filed Under [ SQL Server 2008 SQL Server 2005 SQL Server 2000 ]

Human readable string truncation

In same cases, truncation of a long string is necessary. In most cases you just need to truncate it at the spot and have every section be exactly the same length, like this: DECLARE     @String VARCHAR(MAX),             @Size TINYINT SELECT      @String = 'Hello my name is Jeff. I need some help on a project because ',             @String = @String + 'right now this is how the application i am working ',             @String = @String + 'with displays data.',             @Size = 32 SELECT      1 + Number AS Part,              SUBSTRING(@String, Number * @Size, @Size) FROM        master..spt_values WHERE       Type = 'P'...

posted @ Wednesday, March 18, 2009 11:11 PM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

A general approach to sort different dateformats correctly

DECLARE @Stats TABLE         (             SomeDate DATETIME         ) INSERT  @Stats SELECT  20000 + ABS(CHECKSUM(NEWID())) % 30000 FROM    master..spt_values DECLARE @Style INT ...

posted @ Wednesday, March 18, 2009 2:31 PM | Feedback (4) | Filed Under [ SQL Server 2008 SQL Server 2005 SQL Server 2000 ]

Excel DAYS360 clone

CREATE FUNCTION dbo.DATEDIFF360 (     @source DATETIME,     @target DATETIME,     @style BIT = 0 ) RETURNS INT AS BEGIN         RETURN  CASE @style                     -- European style                     WHEN 1 THEN CASE                                     WHEN DATEPART(DAY, @target) = 31...

posted @ Sunday, March 15, 2009 4:12 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Excel ERF clone for two variables

I previously posted a solution for one variable here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone.aspx and here is a solution for using two variables. CREATE FUNCTION dbo.fnErf (             @z1 FLOAT,             @z2 FLOAT,             @MaxIterations TINYINT = 10 ) RETURNS FLOAT AS BEGIN             IF @z1 IS NULL OR @z2 IS NULL                          RETURN      NULL               DECLARE     @n TINYINT,                          @s1 FLOAT,                          @s2 FLOAT,                          @p1 FLOAT,                          @p2 FLOAT,                          @a1 FLOAT,                          @a2 FLOAT               SELECT      @n = 1,                          @p1 = 1,                          @p2 = 1,                          @a1 = @z1,                          @a2 = @z2,                          @MaxIterations = COALESCE(ABS(@MaxIterations), 10)               WHILE @p1 <> 0.0E AND @p2 <> 0.0E AND @n <= @MaxIterations                          SELECT      @s1 = @z1 /(2.0E * @n + 1.0E),                                      @s2 = @z2 /(2.0E *...

posted @ Wednesday, March 11, 2009 2:55 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Excel ERF clone

This Excel ERF clone works for with SQL Server 2000 and later. I have found that 10 iterations will give enough acccuracy (maximum float accuracy) in most cases, so you can call the function with SELECT  dbo.fnErf(0.35, DEFAULT) However, if you feel the need to more accuracy, replace second parameter with a value of your choice. Here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone-for-two-variables.aspx is also a solution for two variables. CREATE FUNCTION dbo.fnErf (         @z FLOAT,         @MaxIterations TINYINT = 10 ) RETURNS FLOAT AS BEGIN         IF @z IS NULL                 RETURN      NULL         DECLARE @n TINYINT,                 @s FLOAT,                 @p FLOAT,                 @a FLOAT         SELECT  @n = 1,                 @p = 1,                 @a = @z,                 @MaxIterations = COALESCE(ABS(@MaxIterations), 10)         WHILE @p <> 0.0E AND @n <= @MaxIterations                 SELECT  @s = @z /(2.0E * @n...

posted @ Wednesday, March 11, 2009 1:59 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

How to efficiently reuse gaps in identity column

Today I am going to talk about how to efficiently reuse identity values in a column, even if this is something that normally not should be bothered. The reason for this solution was a request for help from a member here on SQLTeam, who was near run out of identity values. I did some reasearch first to see which was the most common method to deal with this situation and not surprisingly the method of iterating all records from start to end was used. That method is not efficient. What if you have 1 million records and there is only 1 gap at...

posted @ Tuesday, March 10, 2009 9:06 AM | Feedback (7) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Extract XML structure automatically

Today I am going to write about how to extract the XML structure from a file. The basic idea is to bulk read the file from disk, place the content in an XML variable and traverse elements in the variable and ultimately output a resultset showing the structure of xml file. I often use this to determine what kind of XML the file is by comparing the returned resultset with a lookup-table. See comment in code to understand what happens CREATE PROCEDURE dbo.uspGetFileStructureXML (     @FileName NVARCHAR(256) ) AS -- Prevent unwanted resultsets back to client SET NOCOUNT ON -- Initialize...

posted @ Thursday, March 05, 2009 3:06 PM | Feedback (9) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Powered by:
Powered By Subtext Powered By ASP.NET