x002548's Blog

SET BASED Number Table Create Hack

posted @ Friday, October 29, 2010 12:29 PM | Feedback (3) | Filed Under [ SQL Gimmicks ]

XML, SQL & DB2 V10

posted @ Friday, October 08, 2010 3:41 PM | Feedback (0) | Filed Under [ DB2 Disconnected Ramblings ]

Next Available Date

Saw a post the other day on SQLTeam.

The OP Asked: "If I supply a Date, How can I get the next available date, that is not a holiday, and is not on the weekend."

I came up with this hack...please let me know if anyone has come with a different version

Thanks

Brett

CREATE TABLE Holidays (Holiday_Dt datetime, HoldayName varchar(255))
GO

INSERT INTO Holidays(Holiday_Dt, HoldayName)
SELECT '1/1/2010', 'New Years Day' UNION ALL
SELECT '1/18/2010', 'Martin Luther King' UNION ALL
SELECT '2/15/2010', 'President''s Day' UNION ALL
SELECT '5/31/2010', 'Memorial Day' UNION ALL
SELECT '7/5/2010', 'July 4th Holiday' UNION ALL
SELECT '9/6/2010', 'Labor Day' UNION ALL
SELECT '11/25/2010', 'Thanksgiving' UNION ALL
SELECT '11/26/2010', 'Black Friday' UNION ALL
SELECT '12/24/2010', 'Christmas Holiday' UNION ALL
SELECT '12/31/2010', 'New Years Eve'
GO

DECLARE @inp_Dt datetime; SET @inp_Dt = '11/25/2010'

    SELECT TOP 1 CASE WHEN Holiday_Dt IS NULL THEN inp_Dt ELSE DATEADD(d,n,inp_Dt) END AS Next_Available_Dt
      FROM (SELECT @inp_Dt AS inp_Dt) AS XXX
 LEFT JOIN Holidays h
  ON  h.Holiday_Dt = xxx.inp_Dt
 LEFT JOIN (SELECT 1 n UNION SELECT 2 n UNION SELECT 3 n UNION SELECT 4 n UNION
   SELECT 5 n UNION SELECT 6 n UNION SELECT 7 n) AS n
  ON  xxx.inp_Dt < DATEADD(d,n,inp_Dt)
     WHERE DATENAME(dw,DATEADD(d,n,inp_Dt)) NOT IN ('Saturday','Sunday')
    AND NOT EXISTS (SELECT * FROM Holidays WHERE Holiday_Dt = DATEADD(d,n,inp_Dt))
GO

DROP TABLE Holidays
GO

posted @ Wednesday, June 30, 2010 2:43 PM | Feedback (3) | Filed Under [ SQL Server ]

DB2 OS/390 Database COPY and RESTORE

posted @ Thursday, December 03, 2009 1:32 PM | Feedback (0) | Filed Under [ DB2 ]

Parsing String in SQL Server Hack

posted @ Friday, November 20, 2009 12:45 PM | Feedback (2) | Filed Under [ SQL Server ]

Replace a Character Anywhere in a DB

posted @ Monday, November 09, 2009 3:03 PM | Feedback (0) | Filed Under [ SQL Server ]

How can I update Multiple Tables at once

posted @ Thursday, April 03, 2008 9:49 AM | Feedback (4) | Filed Under [ SQL Server ]

Because you're mine, I walk the line

....ummm, well, that "line" would be walking through a line in a family tree.  I saw a post the other the day that asked, if I know a relative somewhere in a families "lineage", how can I find the entire family tree from top to bottom.

Well here's a hack to do that...don't ask for effecienciey.  This for SQL Server 2000.  I have to determine if CTE's in 2k5 can come up with a better solution, but here it is for now.

CREATE TABLE Parent (
   ID_PK int IDENTITY(1,1)
 , [Name] varchar(20)
 , PhoneNum varchar(20)
 , Address varchar(30))

CREATE TABLE Child (
   ID_PK int
 , ParentID_FK int)
GO

INSERT INTO Parent([Name],PhoneNum, Address)
SELECT 'Annie',     '111-111-1111', '1st Street' UNION ALL
SELECT 'Bob',       '222-222-2222', '2nd Street' UNION ALL
SELECT 'Cathy',     '333-333-3333', '3rd Street' UNION ALL
SELECT 'Don',       '444-444-4444', '4th Street' UNION ALL
SELECT 'Emily',     '555-555-5555', '5th Street' UNION ALL
SELECT 'Frank',     '666-666-6666', '6th Street' UNION ALL
SELECT 'Georgette', '777-777-7777', '7th Street' UNION ALL
SELECT 'Harry',     '888-888-8888', '8th Street'

INSERT INTO Child(ID_PK, ParentID_FK)
SELECT 1, null UNION ALL
SELECT 2, 1    UNION ALL
SELECT 3, 2    UNION ALL
SELECT 4, 3    UNION ALL
SELECT 5, null UNION ALL
SELECT 6, 5    UNION ALL
SELECT 7, 6    UNION ALL
SELECT 8, 7
GO

SELECT * FROM Parent p LEFT JOIN Child c ON p.ID_PK = c.ID_PK
GO

CREATE FUNCTION udf_FindTree (@Child varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @p int, @p_save int, @rs varchar(8000)
SELECT @p = 0, @p_save = 0
SELECT @p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
 WHERE [Name] = @Child
--Loop Until @@rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @p)
  BEGIN
 SELECT @p_save = @p
 SELECT @p = ParentID_FK FROM Child c WHERE ID_PK = @p_save
-- The Last assignement is the top Parent
  END
--Now Walk from the top Down until @@rowcount = 0
SELECT @p = @p_save
SELECT @rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @p)
  BEGIN
 SELECT @rs = @rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @p
 SELECT @p = ID_PK FROM Child WHERE ParentID_FK = @p
  END
RETURN @rs
END
GO

SELECT dbo.udf_FindTree('Cathy')
GO

SELECT * FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
 WHERE [Name] = 'Cathy'
GO

CREATE PROC usp_FindTree @Child varchar(20)
AS
SET NOCOUNT ON
DECLARE @p int, @p_save int, @rs varchar(8000)
SELECT @p = 0, @p_save = 0
SELECT @p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
 WHERE [Name] = @Child
--Loop Until @@rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @p)
  BEGIN
 SELECT @p_save = @p
 SELECT @p = ParentID_FK FROM Child c WHERE ID_PK = @p_save
-- The Last assignement is the top Parent
  END
--Now Walk from the top Down until @@rowcount = 0
SELECT @p = @p_save
SELECT @rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @p)
  BEGIN
 SELECT @rs = @rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @p
 SELECT @p = ID_PK FROM Child WHERE ParentID_FK = @p
  END
SELECT @rs AS rs
SET NOCOUNT OFF
GO

EXEC usp_FindTree 'Cathy'
GO

DROP PROC usp_FindTree
DROP Function udf_FindTree
DROP TABLE Parent, Child
GO

posted @ Thursday, July 26, 2007 3:45 PM | Feedback (4) | Filed Under [ SQL Server ]

Alias to be or not to be

EDIT:  This is bizzare..I ran multiple table joins, and reran it ovr and over, and the times are always different and one time one is faster than the othe and other times it's the other wway around.

We were having a discussion over at SQLTeam on whether to use full table name aliases or short aliases to label columns.  It always seemed to be a mattter of preference and debate on how self docuenting the code.  For m I will always use short aliases and make sure I lable every column, even if it's unique, just so when I come back to the code I don't have to guess or go to the data model.  Some suggest that that's not good enough and fully qualify the columns with the name.  I think that is overkill, but hey, to each his or her own. 

Then we got into a discussion about performance and if there was any.  My first thought was, no way...then someone asked if anyone had ever tested it...so I got to thinking...ys, yes, I know, a dangerous proposition.  And while this is not diffinitive by any means, and I've seen varying results, but the table without the aliases at all took longer.  I have to run some more complicated tests, but this is what we get from the following code.

ShortLabelTime
--------------
93

FullLabelTime
-------------
93

NoLableTime
-----------
126

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime DEFAULT(GetDate()))
GO

DECLARE @x int
SELECT  @x = 1
WHILE @x < 10000
  BEGIN
 INSERT INTO myTable99(Col2) SELECT 'x'
 SELECT @x = @x + 1
  END
GO

DECLARE @s datetime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT a.Col1, a.Col2, a.Col3 FROM myTable99 a WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS ShortLabelTime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT myTable99.Col1, myTable99.Col2, myTable99.Col3 FROM myTable99 WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS FullLabelTime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT Col1, Col2, Col3 FROM myTable99 WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS NoLableTime
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

posted @ Tuesday, March 27, 2007 2:59 PM | Feedback (2) | Filed Under [ SQL Server ]

Add Foreign Keys Back to the Database

OK, Well....sorry to keep you hangng out there.  But, if you followed the code in the above link you will have all of the RI saved to the work table, so now all you need to do is replay it.  The following is the code that will do this for you.  Again, sorry for the delay.

posted @ Thursday, March 01, 2007 9:49 AM | Feedback (0) | Filed Under [ SQL Gimmicks ]

Collecting Requirements For Key Information

I've been asked to assist (this time BEFORE Project initiation for a change) in the developmennt of a new application.  The Business Liason/Tech group that is doing this has been collecting requirements (basically reviewing an EXCEL Spreadsheet on steriods) and is coming up with a data model.  We will be doing a model review, but I was able to guide them in how to record the information so I can leverage the data to generate the tables.  I've done this several times already, and they are still fine tuning the table defintion.  I then take that document (Excel, again) and generate the DDL and drop it into ERWin (I'll post that code later).

Now they are curious on how to define relationships, Primary Keys and Alternate keys.  I decided to figure out the best way to guide them in documenting this so I could leverage that as well.  So I came up with the following.  Just ask your Business Liason group to record the information in the following form, then just use the code below and generate all of your code.

Basically, you need a spreadsheet with the follwoing information

Parent Table, Key Column, Child Table, Key Type, Key Order and  Key Sequence.

Parent Table as it implies is the Parent in a relationship.  For Alterante Keys, Primary Keys it is the table that key info is being generated for.  Key Column is the Column that will be used in the key.  Child Table as is implied the child of a relationship.  It is only used for a Foreign Key type.  Key Type is P for Primary, F for Foreign Key amd A for an Alternate Key.  Key Order defines the Order of Columns in a key, and finally Key Sequence defines the Order in which Foreign or Alternate keys are created.  It is also used as part of the index or constraint.

I have been on the wrong end of documentation gone bad too many times, and there is immense push back when you tell them you can't use it...OK, you can use, you have to read it, then retype everything that they already typed. 

I hope you find this useful.

CREATE TABLE myTable99 (
   Parent  sysname
 , keyColumn  sysname
 , Child  sysname
 , keyType  char(1)
 , keyOrder  int
 , keySequence  int)
GO

INSERT INTO myTable99(Parent, keyColumn, Child, keyType, keyOrder, keySequence)
SELECT 'myEmployee',  'EMPL_ID', 'myDirectory', 'F', 1, 1 UNION ALL
SELECT 'myEmployee',  'EMPL_ID', ''           , 'P', 1, 1 UNION ALL
SELECT 'myEmployee',  'SSN'    , ''           , 'A', 1, 1 UNION ALL
SELECT 'myDirectory', 'PHONE'  , ''           , 'P', 1, 1 UNION ALL
SELECT 'myDirectory', 'EMPL_ID', ''           , 'P', 2, 1
GO

SELECT * FROM myTable99
GO

CREATE TABLE myEmployee(EMPL_ID char(12) NOT NULL, SSN char(9), EMP_NAME varchar(50))
CREATE TABLE myDirectory(PHONE char(10) NOT NULL, EMPL_ID char(12) NOT NULL)
GO

SELECT SQL FROM (
SELECT DISTINCT 'ALTER TABLE ' + Parent
 + ' WITH NOCHECK ADD CONSTRAINT '
 + Parent + '_PK PRIMARY KEY (' AS SQL
 , Parent, 1 AS SQL_GROUP, 1 AS keyOrder
  FROM myTable99
 WHERE keyType = 'P'
UNION ALL
SELECT '   '+keyColumn AS SQL
 , Parent, 2 AS SQL_GROUP, keyOrder
  FROM myTable99
 WHERE keyOrder = 1
   AND keyType = 'P'
UNION ALL
SELECT ' , '+keyColumn AS SQL
 , Parent, 3 AS SQL_GROUP, keyOrder
  FROM myTable99
 WHERE keyOrder <> 1
   AND keyType = 'P'
UNION ALL
SELECT DISTINCT ' )' AS SQL
 , Parent, 4 AS SQL_GROUP, 1 AS keyOrder
  FROM myTable99
 WHERE keyType = 'P'
UNION ALL
SELECT DISTINCT 'GO' AS SQL
 , Parent, 5 AS SQL_GROUP, 1 AS keyOrder
  FROM myTable99
 WHERE keyType = 'P') AS XXX
ORDER BY Parent, SQL_GROUP, keyOrder

posted @ Thursday, February 22, 2007 1:05 PM | Feedback (0) | Filed Under [ SQL Gimmicks ]

What'dya mean I can't TRUNCATE Tables that have RI?

So, we've gotten into the business of sanitizing or scrambling sensitive production data for development environments.  This is, for the most part, was the direction to do this for mainframe flat files.  Now comes along distributed environments, mostly 3rd party vendor applications on sql server.  You should see some of the twisted things these apps do.  Using reserved words as column names, creating tables with the same name but different owners..the list is long.

In any case, when we told the people who support this mess that we would need fixed width flat files,  they were.."but...but...we have over 1,000 tables".  Long story short, I wrote a bunch of sprocs to automate alot of the steps needed to get the data out and put it back so they could have a sanitized environment.

One of the issues we ran across was how to handle all of this with RI.  Now I guess I could have had them set up a data dictionary that show the relationships, but I choose a lazy way out.  I used the catalogs to copy all the RI to a Work table, DROP All of the RI (This is done with another sproc I have yet o post) so that it could be "replayed" after I was done with the TRUNCATES and the bcp in's.  Here's a sproc that will log all the RI for any database.  Be forewarned...it takes dynamic sql to a whole other level.  If anyone has anything simpler, I'd like to see it, but this works fine.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_Gen_FK_code]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_Gen_FK_code]
GO

CREATE procedure isp_Gen_FK_code
  @dbname varchar(255)

AS
/*

EXEC isp_Gen_FK_code
  @dbname = 'OHM_Prod'

SELECT * FROM FK_create_code

*/

SET NOCOUNT ON
DECLARE @tablename nvarchar(128), @column nvarchar(128), @schema nvarchar(128), @constraint nvarchar(128)
DECLARE @fktable nvarchar(128), @fkconstraint nvarchar(128), @onupdate varchar(9), @ondelete varchar(9)
DECLARE @comma char(1), @createsql nvarchar(4000), @dropsql nvarchar(4000), @truncatesql nvarchar(4000)
DECLARE @DYSQL nvarchar(4000),@ColList sysname, @ColList2 sysname

DECLARE @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int, @rc int

SELECT @rc = 0
CREATE TABLE ##Key_Column_usage(Constraint_catalog sysname,CONSTRAINT_SCHEMA sysname, CONSTRAINT_NAME varchar(300)
    ,TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, COLUMN_NAME sysname
    ,ORDINAL_POSITION int)

SET @DYSQL = 'use '+@dbname+'
insert into ##Key_Column_usage
select '''+@dbname+'''    as CONSTRAINT_CATALOG
 ,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
 ,c_obj.name    as CONSTRAINT_NAME
 ,'''+@dbname+'''    as TABLE_CATALOG
 ,user_name(t_obj.uid) as TABLE_SCHEMA
 ,t_obj.name    as TABLE_NAME
 ,col.name    as COLUMN_NAME
 ,case col.colid 
  when ref.fkey1 then 1   
  when ref.fkey2 then 2   
  when ref.fkey3 then 3   
  when ref.fkey4 then 4   
  when ref.fkey5 then 5   
  when ref.fkey6 then 6   
  when ref.fkey7 then 7   
  when ref.fkey8 then 8   
  when ref.fkey9 then 9   
  when ref.fkey10 then 10   
  when ref.fkey11 then 11   
  when ref.fkey12 then 12   
  when ref.fkey13 then 13   
  when ref.fkey14 then 14   
  when ref.fkey15 then 15   
  when ref.fkey16 then 16
 end      as ORDINAL_POSITION
from
 '+@dbname+'.dbo.sysobjects c_obj
 ,'+@dbname+'.dbo.sysobjects t_obj
 ,'+@dbname+'.dbo.syscolumns col
 ,'+@dbname+'.dbo.sysreferences  ref
where
 permissions(t_obj.id) != 0
 and c_obj.xtype in ('+'''F'''+ ')
 and t_obj.id = c_obj.parent_obj
 and t_obj.id = col.id
 and col.colid   in
 (ref.fkey1,ref.fkey2,ref.fkey3,ref.fkey4,ref.fkey5,ref.fkey6,
 ref.fkey7,ref.fkey8,ref.fkey9,ref.fkey10,ref.fkey11,ref.fkey12,
 ref.fkey13,ref.fkey14,ref.fkey15,ref.fkey16)
 and c_obj.id = ref.constid
union
 select
 '''+@dbname+'''    as CONSTRAINT_CATALOG
 ,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
 ,i.name     as CONSTRAINT_NAME
 ,'''+@dbname+'''    as TABLE_CATALOG
 ,user_name(t_obj.uid) as TABLE_SCHEMA
 ,t_obj.name    as TABLE_NAME
 ,col.name    as COLUMN_NAME
 ,v.number    as ORDINAL_POSITION
from
 '+@dbname+'.dbo.sysobjects  c_obj
 ,'+@dbname+'.dbo.sysobjects  t_obj
 ,'+@dbname+'.dbo.syscolumns  col
 ,master.dbo.spt_values  v
 ,'+@dbname+'.dbo.sysindexes  i
where
 permissions(t_obj.id) != 0
 and c_obj.xtype in ('+'''UQ'''+' ,'+'''PK'''+')
 and t_obj.id = c_obj.parent_obj
 and t_obj.xtype  = '+'''U'''+'
 and t_obj.id = col.id
 and col.name = index_col(user_name(t_obj.uid)+'+'''.'''+'+t_obj.name,i.indid,v.number)
 and t_obj.id = i.id
 and c_obj.name  = i.name
 and v.number  > 0
  and v.number  <= i.keycnt
  and v.type  = '+'''P'''+''
execute sp_executesql @DYSQL

TRUNCATE TABLE FK_create_code
SET @DYSQL = 'declare cstrts cursor fast_forward read_only for
 SELECT DISTINCT
   c.[TABLE_SCHEMA]
 , c.[TABLE_NAME]
 , u.CONSTRAINT_NAME
  FROM   ' +@dbname+'.[INFORMATION_SCHEMA].[COLUMNS] c
  JOIN   ##Key_Column_usage u
    ON    c.[TABLE_NAME]      = u.[TABLE_NAME]
   AND   c.[TABlE_SCHEMA]    = u.[TABLE_SCHEMA]
   AND    c.[COLUMN_NAME]     = u.[COLUMN_NAME]
  JOIN   ' +@dbname+'.[INFORMATION_SCHEMA].[table_constraints] t
    ON    u.[CONSTRAINT_NAME] = t.[CONSTRAINT_NAME]
 WHERE    t.[CONSTRAINT_TYPE] = ' + '''FOREIGN KEY'''

execute sp_executesql @DYSQL
Select @error_out = @@error
If @error_out <> 0
  BEGIN
   SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1
   GOTO isp_Gen_FK_code_Error
  END

OPEN cstrts

fetch next from cstrts
into @schema, @tablename, @constraint

while @@fetch_status = 0
begin

SET @DYSQL = 'DECLARE @cr nchar(2), @go nvarchar(8)
SET @cr = nchar(13)+nchar(10)
SET @go = @cr + '+'''GO'''+' + @cr

SELECT DISTINCT
   @fktable = u2.[TABLE_NAME]
 , @fkconstraint = r.[UNIQUE_CONSTRAINT_NAME]
 , @onupdate = r.[UPDATE_RULE]
 , @ondelete = r.[DELETE_RULE]
 --, @column = u.[COLUMN_NAME]
  FROM ' +@dbname+'.[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
  JOIN ##Key_Column_usage u2
    ON r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
  JOIN ##Key_Column_usage u
    ON u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
 WHERE r.[CONSTRAINT_NAME] = @constraint

SELECT @ColList = Null

select @ColList = COALESCE(@ColList + '+ ''','''+ ', '+'''''' +') + '
     +'''['''+' +CAST(COLUMN_NAME AS sysname)+'+''']'''+'
FROM (SELECT DISTINCT TOP 100 u.COLUMN_NAME, u.[ORDINAL_POSITION]
        FROM ' +@dbname+'.[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
        JOIN ##Key_Column_usage u2
          ON r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
        JOIN ##Key_Column_usage u
          ON u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
       WHERE r.[CONSTRAINT_NAME] = @constraint
    ORDER BY u.[ORDINAL_POSITION]
) AS XXX
set @createsql =
'+ '''ALTER TABLE ['+@dbname+'].[''' +'
+ @schema
+ '+'''].['''+'
+ @tablename
+ '+'''] ADD CONSTRAINT ['''+'
+ @constraint
+ '+'''] '''+'
+ @cr
+ '+'''FOREIGN KEY ('''+'
+ @colList
+ '+''') REFERENCES ['+@dbname+'].[''' +'
+ @schema
+ '+'''].['''+'
+ @fktable
+ '+'''] ('''+'

CREATE TABLE #Ver(Dbversion varchar(2000))
INSERT INTO #Ver
SELECT @@Version

 SELECT @ColList2 = Null

  select @ColList2 = COALESCE(@ColList2 + '+ ''','''+ ', '+'''''' +') + '
     +'''['''+' +CAST(c.COLUMN_NAME AS sysname)+'+''']'''+'
    FROM ' +@dbname+'.[INFORMATION_SCHEMA].[COLUMNS] c
    JOIN ##Key_Column_usage u
      ON c.[TABLE_NAME] = u.[TABLE_NAME]
     AND c.[COLUMN_NAME] = u.[COLUMN_NAME]
   WHERE u.[CONSTRAINT_NAME] = @fkconstraint
     AND u.[CONSTRAINT_SCHEMA] = c.[TABLE_SCHEMA]
ORDER BY u.[ORDINAL_POSITION]

set @createsql = @createsql + @colList2

IF EXISTS(SELECT * FROM #Ver where dbversion like '+ '''%8.00%'''+')
BEGIN

set @createsql = @createsql + '+''') ON DELETE '''+'
+ @ondelete
+ '+''' ON UPDATE '''+'
+ @onupdate
END
ELSE
BEGIN
set @createsql = @createsql + '+''')'''+'
END

INSERT INTO FK_Create_code (FK_Code)
VALUES (@createsql)

--print @createsql'

execute sp_executesql @DYSQL,N' @tablename nvarchar(128), @column nvarchar(128), @schema nvarchar(128), @constraint nvarchar(128)
 ,@fktable nvarchar(128), @fkconstraint nvarchar(128), @onupdate varchar(9), @ondelete varchar(9)
, @comma char(1), @createsql nvarchar(4000), @dropsql nvarchar(4000), @truncatesql nvarchar(4000),@ColList nvarchar(300)
, @colList2 nvarchar(300)'
,@tablename,@column,@schema, @constraint, @fktable, @fkconstraint , @onupdate, @ondelete, @comma, @createsql
, @dropsql, @truncatesql, @ColList, @colList2

Select @error_out = @@error
If @error_out <> 0
  BEGIN
   SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1
   GOTO isp_Gen_FK_code_Error
  END

fetch next from cstrts
into @schema, @tablename, @constraint

end

isp_Gen_FK_code_Exit:
close cstrts
deallocate cstrts
DROP TABLE ##KEY_column_usage
RETURN @rc
SET NOCOUNT OFF

isp_Gen_FK_code_Error:

If @Error_Type = 50001
 BEGIN
  Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc)) 
          + ',"' + '  @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
          + ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
          + ',"' + '  Message: ' + ',"' + RTrim(description)
          From master..sysmessages
        Where error = @error_out)
 END

RAISERROR @Error_Type @Error_Message

GOTO isp_Gen_FK_code_Exit

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

posted @ Tuesday, February 20, 2007 11:58 AM | Feedback (2) | Filed Under [ SQL Gimmicks ]

SET Versus SELECT (Or, Who Really Cares Anyway)

EDIT:  As Tara points out:

Vyas did this test quite some time ago: http://vyaskn.tripod.com/differences_between_set_and_select.htm Either I never read it, or I forgot I read it.  Well hopefully I pulled some different points together differently here than Vyas did, and at the very least, I hope I made my feeling clear about a program that has to loop over 2 million times.  Thanks for the heads up Tara...                                     

OK, this always comes up from time to time, and it always seem that people are both sides of the fence. "SET is faster because....", "No, SELECT is faster because".  Well there should be no debate about it, yet I've failed to find a definitive explanation of what the true story behind this is.  In any case I've wanted to look into this.

In this thread the question comes up again.  In that thread, Peter posts a link to a SQL Server Magazinee Article that discusses this topic.  While it was a good read, I had a hard time buying it.  The author (who is listed as "Reader") posted that in using SET, it is optimized and when a single value is set that this is more effecient.  They then go on to say that after 1 million iterations with multiple value stes, SELECT was 59% more effecient for each operation.  Now the fact that they are doing checks between each assignment seems to cause a potential unexpected interference by SQL server to the outcome of the results.  Now, in above article, the first line states "Loops are fairly common in SQL Server stored procedures. ", which in itself is sort of a red herring, since if you are coding that way, I would sugest that you step back and rethink your process.  If you can't find a set based solution for 99% for what you have to do, then drop me a line,  or head on over to SQL Team.

So I set forth for my own test.  I did my tests with an undisturbed loop where dattime values were grabbed before and after the loops of pure sets.  I did for 1,000, 10,000, 1,000,000 and 10,000,000.  Looking Kalen's book (Inside SQL Server 2000), she has a chapter th differences between the two, but nothing about performance.  I'll need to google around some more, however,  there must be an explanation about the internals.  I did the test for Multiple variable assignments, and another set for single variable assignments.  For the 1 million iteration (and it's really 2 million assignments)  I got the following:

SELECT_MS_Multiple  SET_MS_Multiple
------------------                  ---------------
43470                              202963

SELECT_MS_Single   SET_MS_Single
----------------                    -------------
42453                             45746

Now in both Cases, SELECT wins, in the case of Multiple assignments, SELECT seems to blow SET's doors off.  Now, I need to reiterate this again.  If you are finding that you have a process that needs to loop 1 million times, you either are backed into a corner due to previous developement that can't be changed, you've run into the 1% of the time that you have to, or you have a flawed application design. 

If Anyone sees anyuthing wrong with this test, or  if anyone has any comments I would look forward to it.  With all that said, I use SELECT almost exclusively.  Here's the code:

DECLARE   @SET1 int,      @SELECT1 int,      @SET2 int,           @SELECT2 int
DECLARE   @SET3 int,      @SELECT3 int,      @SET4 char,          @SELECT4 char
DECLARE   @SET5 char,     @SELECT5 char,     @SET6 char,          @SELECT6 char
DECLARE   @SET7 datetime, @SELECT7 datetime, @SET8 datetime,      @SELECT8 datetime
DECLARE   @SET9 datetime, @SELECT9 datetime, @SETA varchar(8000), @SELECTA varchar(8000)

DECLARE @x int, @s1 datetime, @s2 datetime, @e1 datetime, @e2 datetime, @c int
DECLARE @s3 datetime, @s4 datetime, @e3 datetime, @e4 datetime

SELECT @x = 1, @s1 = getDate(), @c = 1000000

WHILE @x < @c
  BEGIN
 SELECT    @SELECT1 = 1
  , @SELECT2 = 2
  , @SELECT3 = 3
  , @SELECT4 = 'a'
  , @SELECT5 = 'b'
  , @SELECT6 = 'c'
  , @SELECT7 = '2001-09-11'
  , @SELECT8 = GetDate()
  , @SELECT9 = '1999-12-31'
  , @SELECTA = 'This is a test of the emergency Broadcationg System.  This is only a test'

 SELECT    @SELECT1 = 0
  , @SELECT2 = 0
  , @SELECT3 = 0
  , @SELECT4 = ''
  , @SELECT5 = ''
  , @SELECT6 = ''
  , @SELECT7 = ''
  , @SELECT8 = 0
  , @SELECT9 = 0
  , @SELECTA = ''
 
 SET @x = @x + 1
  END

SELECT @x = 1, @s2 = getDate(), @e1 = getDate()

WHILE @x < @c
  BEGIN
 SET       @SELECT1 = 1
 SET   @SELECT2 = 2
 SET    @SELECT3 = 3
 SET   @SELECT4 = 'a'
 SET   @SELECT5 = 'b'
 SET   @SELECT6 = 'c'
 SET   @SELECT7 = '2001-09-11'
 SET   @SELECT8 = GetDate()
 SET   @SELECT9 = '1999-12-31'
 SET    @SELECTA = 'This is a test of the emergency Broadcationg System.  This is only a test'

 SET       @SELECT1 = 0
 SET   @SELECT2 = 0
 SET    @SELECT3 = 0
 SET   @SELECT4 = ''
 SET   @SELECT5 = ''
 SET   @SELECT6 = ''
 SET   @SELECT7 = 0
 SET   @SELECT8 = 0
 SET   @SELECT9 = 0
 SET    @SELECTA = ''
 
 SET @x = @x + 1
  END

SELECT @e2 = getDate()

posted @ Monday, February 12, 2007 2:36 PM | Feedback (3) | Filed Under [ SQL Server ]

3rd and 45? Drop back and Punt? Nah, Generate INSERTS

EDIT 2007/09/06:  I've modified the sproc to change the dates to be formatted to 121 and stripped out all trailing spaces for char's

So, we don't have DBArtisan, but I am very happy for my copy of ERWin.  Don't know what I'd do with out it.

So we have some requirements where they want to create insert statements to load a production table.  I said, why not just bcp the data out in native format and create an osql script for the production DBA's to insert the data, or a sproc perhaps.

There are many better ways in my opinion, but I do like a challenge, so I wrote the following.  It's not a very mature sproc...no error handling, doesn't handle images or text (how would you anyway for inserts?), ect

Just supply the table_name to the sproc

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_Generate_Inserts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_Generate_Inserts]
GO

CREATE PROC isp_Generate_Inserts
 @TABLE_NAME sysname
AS

SET NOCOUNT ON

/*
EXEC isp_Generate_Inserts 'BusinessGroup'
EXEC isp_Generate_Inserts 'MEPType'
EXEC isp_Generate_Inserts 'Person'
EXEC isp_Generate_Inserts 'Profile'
EXEC isp_Generate_Inserts 'Status'
EXEC isp_Generate_Inserts 'SubBusinessGroup'
EXEC isp_Generate_Inserts 'XREF'
EXEC isp_Generate_Inserts 'Operator'
EXEC isp_Generate_Inserts 'FORMREF'
EXEC isp_Generate_Inserts 'MEPTERRITORY'
EXEC isp_Generate_Inserts 'MEPTICKLERSTATUS'
*/

DECLARE @INSERT varchar(8000), @COLLIST varchar(8000)
--, @TABLE_NAME sysname
, @SELECT varchar(8000), @cmd varchar(8000), @x int

  SELECT @COLLIST = COALESCE(@COLLIST + ', ','') + COLUMN_NAME
    FROM INFORMATION_SCHEMA.Columns
   WHERE TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @INSERT = 'INSERT INTO ' + @TABLE_NAME + '('+ @COLLIST + ')'

-- SELECT @INSERT

SELECT @SELECT = COALESCE(@SELECT + '+'',''+ ','') +
           CASE WHEN DATA_TYPE
  IN ('datetime','smalldatetime')
  THEN + ''''+ +''''+''''+''''+'+' + 'COALESCE(CONVERT(varchar(25),' + COLUMN_NAME + ',121),'''')' + '+' + ''''+''''+''''+'''' 
  WHEN DATA_TYPE
  NOT IN ('int','bigint','smallint','tinyint','deciaml','numeric','money')
  THEN + ''''+ ''''+''''+''''+'+COALESCE(REPLACE(RTRIM(' + COLUMN_NAME + ')' + ','''''''','''''''''''')' + ','''')+' + ''''+''''+''''+''''
  ELSE + 'COALESCE(RTRIM(CONVERT(varchar(25),' + COLUMN_NAME + ')),'''''''''''')'
    END
    FROM INFORMATION_SCHEMA.Columns
   WHERE TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @SELECT = 'SELECT ' + @SELECT + ' AS DATA FROM ' + @TABLE_NAME

-- SELECT @SELECT

SET @cmd = 'CREATE VIEW XXX AS ' + @SELECT

EXEC(@cmd)

CREATE TABLE myTable99(RowId int IDENTITY(1,1), Data varchar(8000))

INSERT INTO myTable99(Data) SELECT DATA FROM XXX

SELECT 0 AS RowId, @INSERT AS DATA INTO myTemp99

SET @cmd = 'CREATE VIEW YYY AS '
+'SELECT RowId, DATA FROM myTemp99 '
+'UNION ALL '
+'SELECT RowId, '+ '''' + 'SELECT ' + '''' + ' + DATA+ ' + '''' + ' UNION ALL ' + '''' + ' AS DATA FROM myTable99 WHERE RowId < (SELECT COUNT(*) FROM myTable99)'
+'UNION ALL '
+'SELECT RowId, '+ '''' + 'SELECT ' + '''' + ' + DATA AS DATA FROM myTable99 WHERE RowId = (SELECT COUNT(*) FROM myTable99) '

-- SELECT @cmd

EXEC(@cmd)

SET @cmd = 'bcp "SELECT DATA FROM MEP.dbo.YYY ORDER BY RowId" QUERYOUT D:\MEP\Scripts\INS_'+@TABLE_NAME+'.Dat -T -c -S<servername>'

-- SELECT @cmd

EXEC master..xp_cmdshell @cmd

 DROP VIEW XXX, YYY
 DROP TABLE myTable99, myTemp99
 SET NOCOUNT OFF

EXEC master..xp_cmdshell 'Dir D:\MEP\Scripts\*.*'

GO

posted @ Thursday, February 08, 2007 2:39 PM | Feedback (0) | Filed Under [ Code Generataion ]

Deconstruct Text, word by word, into a single column

Why, I still didn't get an answer from the poster in this post.  

But with Rudy laying the ground work I came up with the following.  

Why anyone would need to do thids, I have no idea.  I added a twist to this version 

where it counts the occurances.  The end goal here though was to find the text

row with the most occurances of any 1 word, and order the rows in that order.

Why?  I have no idea.

 
create table somewords
( id integer not null primary key identity
, blah text not null
);
insert into somewords (blah) 
values ('a word that appears maximum number of times in a column')
insert into somewords (blah) 
values ('Is it possible to get words from text columns in a sql server database')
insert into somewords (blah) 
values ('This could solve my problem if reffered column contain only single word')
insert into somewords (blah) 
values ('that''s going to require that you split out every word in the column individually')
insert into somewords (blah) 
values ('the query will definitely not be easy to write')
insert into somewords (blah) 
values ('Please read the sticky at the top of the board')
insert into somewords (blah) 
values ('The physical order of data in a database has no meaning')
GO
CREATE TABLE UniqueWords (
   Word varchar(256)
 , WordId int IDENTITY(1,1)
 , WordCount int DEFAULT(1)
 , Add_Dt datetime DEFAULT (GetDate()))
 GO
CREATE UNIQUE INDEX UnqueWords_PK ON UniqueWords(Word)
GO
CREATE PROC isp_INS_UNIQUE_WORDS
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @Words INT, @Pos INT, @x Int, @str varchar(256)
    , @word varchar(256), @start int, @end int, @exitstart int
 SELECT @Words = 0, @Pos = 1, @x = -1, @Word = '', @start = 1
 DECLARE myCursor CURSOR FOR SELECT Blah FROM SomeWords
 OPEN myCursor
 FETCH NEXT FROM myCursor INTO @str
 WHILE @@FETCH_STATUS = 0
   BEGIN
  WHILE (@x <> 0)
   BEGIN
    SET @x     = CHARINDEX(' ', @str, @Pos)
    IF @x <> 0
      BEGIN 
     SET @end   = @x - @start
     SET @word  = SUBSTRING(@str,@start,@end)
     IF NOT EXISTS (SELECT * FROM UniqueWords WHERE Word = @Word)
      INSERT INTO UniqueWords(Word) SELECT @word
       ELSE
      UPDATE UniqueWords SET WordCount = WordCount + 1 WHERE Word = @Word
     -- SELECT @Word, @@ROWCOUNT,@@ERROR
     -- SELECT @x, @Word, @start, @end, @str
     SET @exitstart = @start + @end + 1
     SET @Pos   = @x + 1
     SET @start = @x + 1
     SET @Words = @Words + 1
      END
    IF @x = 0
      BEGIN
     SET @word  = SUBSTRING(@str,@exitstart,LEN(@str)-@exitstart+1)
     IF NOT EXISTS (SELECT * FROM UniqueWords WHERE Word = @Word)
      INSERT INTO UniqueWords(Word) SELECT @word
       ELSE
      UPDATE UniqueWords SET WordCount = WordCount + 1 WHERE Word = @Word
     -- SELECT @Word, @@ROWCOUNT,@@ERROR
     -- SELECT @x, @Word, @exitstart, LEN(@str)-@exitstart, @str
      END
   END
  FETCH NEXT FROM myCursor INTO @str
  SELECT @Words = 0, @Pos = 1, @x = -1, @Word = '', @start = 1
   END 
   CLOSE myCursor
   DEALLOCATE myCursor
   SET NOCOUNT OFF
 RETURN @Words
END
GO
EXEC isp_INS_UNIQUE_WORDS
GO
SELECT * FROM UniqueWords ORDER BY Word
GO
DROP PROC isp_INS_UNIQUE_WORDS
DROP TABLE UniqueWords, somewords
GO

posted @ Wednesday, December 20, 2006 3:27 PM | Feedback (2) | Filed Under [ SQL Gimmicks ]