Jane's Technical Stuff
Archives
May 2002March 2003
May 2003
July 2003
August 2003
June 2005
July 2005
August 2005
December 2005
January 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
May 2008
June 2008
July 2008
August 2008
September 2008
October 2008
Wednesday, September 03, 2008
Database Comparison tools: Redgate SQL Compare Review
I first started using Redgate SQL Compare when I was at Glass's in 2003/04 and I'm still using it 4 years later. Over those years the product has evolved and new products have been released from Redgate. I have only ever used it for SQL Server (7, 2000 and 2005) and so can't comment on it's appropriateness or reliableness targeting other database products.
At the time that I first found SQL Compare, there weren't that many tools available that did a reliable job of comparing objects and producing scripts of the differences. I know that one of my colleagues at Madgex is a fan of SQL Delta. One day we sat down to compare the tools and discovered that there really wasn't much to choose between them.
I have always used SQL Compare more as a tool to check my scripted updates, rather than to produce those scripts. After a bad experience at a previous company when a member of my team managed to empty an articles table rather than update it when manipulating the database via Enterprise Manager I've habitually manually created defensive SQL change scripts with appropriate transactions and error handling.
SQL Compare is simple to use, works quickly and produces a detailed list of differences between database objects. The differences that are detected are configurable, so you can choose to ignore or report upon white space or comment differences.
When you first open the screen you are presented with a screen offering options of what you want to compare.
Once you have registered a couple of databases to compare, it is a short wait until the differences are displayed.
The display of differences are divided into:
For each object that is different you can choose to view the details of that object which will display a SQL based description of the object with the differences identified by a highlight line - sometimes it might be the whole object, a part of an object (i.e.a column definition) and sometimes it will be a constraint, or a grant statement that is missing.
From the summary screen you can select the changes that you're interested in and get SQL Compare to automatically synchonise your target database to match the source, or vice versa. You can also get it to generate some SQL to do the generation to allow you to put your SQL upgrade scripts into a build process, or under source control. In the majority of cases I will use this tool to check my upgrade scripts, rather than to produce them and if I spot any difference I tend to write my own, defensive, SQL to make the upgrade scripts.
In the 4 or so years I've been using SQL Compare I've found it to be a stable, evolving product which I have come to rely upon completely to check upgrade processes. I made the most use of this tool at Glass's where I was working on a product which was client/server application where it couldn't be relied upon that the customer was always running the latest version. Consequently, our preparation for release process involved installing the earliest supported version of the system, and upgrading it to the release candidate on one machine and installing a clean, new build of the release candidate from scratch and comparing the two and producing upgrade SQL scripts for any identified differences before starting the process again. We also included a call to the command line version into the automated MSBuild process producing an HTML report of the differences which could be used to fail the build if anything other than 0 differences was reported.
At the time that I first found SQL Compare, there weren't that many tools available that did a reliable job of comparing objects and producing scripts of the differences. I know that one of my colleagues at Madgex is a fan of SQL Delta. One day we sat down to compare the tools and discovered that there really wasn't much to choose between them.
I have always used SQL Compare more as a tool to check my scripted updates, rather than to produce those scripts. After a bad experience at a previous company when a member of my team managed to empty an articles table rather than update it when manipulating the database via Enterprise Manager I've habitually manually created defensive SQL change scripts with appropriate transactions and error handling.
SQL Compare is simple to use, works quickly and produces a detailed list of differences between database objects. The differences that are detected are configurable, so you can choose to ignore or report upon white space or comment differences.
When you first open the screen you are presented with a screen offering options of what you want to compare.
Once you have registered a couple of databases to compare, it is a short wait until the differences are displayed.
The display of differences are divided into:
- objects that appear on the source database but not the target
- objects that appear on the target database but not the source
- objects that appear on both databases but are different
- objects that are the same on both databases
For each object that is different you can choose to view the details of that object which will display a SQL based description of the object with the differences identified by a highlight line - sometimes it might be the whole object, a part of an object (i.e.a column definition) and sometimes it will be a constraint, or a grant statement that is missing.
From the summary screen you can select the changes that you're interested in and get SQL Compare to automatically synchonise your target database to match the source, or vice versa. You can also get it to generate some SQL to do the generation to allow you to put your SQL upgrade scripts into a build process, or under source control. In the majority of cases I will use this tool to check my upgrade scripts, rather than to produce them and if I spot any difference I tend to write my own, defensive, SQL to make the upgrade scripts.
In the 4 or so years I've been using SQL Compare I've found it to be a stable, evolving product which I have come to rely upon completely to check upgrade processes. I made the most use of this tool at Glass's where I was working on a product which was client/server application where it couldn't be relied upon that the customer was always running the latest version. Consequently, our preparation for release process involved installing the earliest supported version of the system, and upgrading it to the release candidate on one machine and installing a clean, new build of the release candidate from scratch and comparing the two and producing upgrade SQL scripts for any identified differences before starting the process again. We also included a call to the command line version into the automated MSBuild process producing an HTML report of the differences which could be used to fail the build if anything other than 0 differences was reported.
Labels: database change management, review, SQL, SQLServer2005, tool
// posted by Jane @ 9:36 AM 
Comments:
Wednesday, August 20, 2008
SQL Snap
At last September's BarCamp Brighton I saw the CSS Specificity Snap cards and this gave me an idea. So, armed with a concept of showing the different ways of producing the same output using the SQL Server 2005 flavour of SQL I started generating SQL statements. Alex was kind enough to make them pretty, and last week I ran a Madgex ILP session to play.
There are 26 cards in my pack, each card has a SQL statement, and a letter. The letter is used for the crib sheet to allow me to easily spot the matches.
I prepared by producing two piles of 13 cards, with a match in both piles. I then put one pile in the middle of the table face down, and dealt out the rest of the cards to the 5 people who were playing. I placed the 3 spare cards on the table face up so that we could all see them and proceeded to turn them over one by one. When a card was turned over everyone looked at the SQL on the overturned card, and checked their cards and the spare cards to see if there was a match. If a match wasn't spotted then I explained what the SQL was doing, and at only one point did I have to say which letter the matching card would have. During this I had quite a few comments about functions people didn't recognise - especially COALESCE and NULLIF. I also explained some of the performance, and functional differences between the SQL statements when they were seen - so for instance I explained the differences between DELETE and TRUNCATE but I still I have these as a match because they can provide the same result and provided an interesting talking point.
The second game we played was a memory game where I placed all of the cards on the table face down and everyone turned over 2 cards at a time. If the SQL on the cards resulted in the same output, then it was a match and they took the cards away. If they didn't then the cards got turned back over and the next person had a go.
All in all this session took about 30 minutes, and resulted in quite a lot of noise and laughter (so much so that someone came and closed the door of the room we were in).
The following table contains the Letter Code, SQL statement, and the matching Letter code. They are ordered so that the matches are grouped together.
There are 26 cards in my pack, each card has a SQL statement, and a letter. The letter is used for the crib sheet to allow me to easily spot the matches.
I prepared by producing two piles of 13 cards, with a match in both piles. I then put one pile in the middle of the table face down, and dealt out the rest of the cards to the 5 people who were playing. I placed the 3 spare cards on the table face up so that we could all see them and proceeded to turn them over one by one. When a card was turned over everyone looked at the SQL on the overturned card, and checked their cards and the spare cards to see if there was a match. If a match wasn't spotted then I explained what the SQL was doing, and at only one point did I have to say which letter the matching card would have. During this I had quite a few comments about functions people didn't recognise - especially COALESCE and NULLIF. I also explained some of the performance, and functional differences between the SQL statements when they were seen - so for instance I explained the differences between DELETE and TRUNCATE but I still I have these as a match because they can provide the same result and provided an interesting talking point.
The second game we played was a memory game where I placed all of the cards on the table face down and everyone turned over 2 cards at a time. If the SQL on the cards resulted in the same output, then it was a match and they took the cards away. If they didn't then the cards got turned back over and the next person had a go.
All in all this session took about 30 minutes, and resulted in quite a lot of noise and laughter (so much so that someone came and closed the door of the room we were in).
The following table contains the Letter Code, SQL statement, and the matching Letter code. They are ordered so that the matches are grouped together.
| Code | SQL | Match |
| V | SELECT CAST(GETDATE() AS VARCHAR(11)) | G |
| G | SELECT CONVERT (VARCHAR(11), GETDATE()) | V |
| Z | SELECT name FROM sysobjects WHERE xtype = 'P' | Q |
| Q | SELECT name FROM sys.procedures | Z |
| D | SELECT sysobjects.name, syscolumns.name FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.xtype = 'u' | A |
| A | SELECT sysobjects.name, syscolumns.name FROM sysobjects, syscolumns WHERE sysobjects.id = syscolumns.id AND sysobjects.xtype = 'u' | D |
| U | SELECT getdate() | O |
| O | EXEC('SELECT getdate()') | U |
| J | DELETE FROM Test | T |
| T | TRUNCATE TABLE Test | J |
| Y | SELECT ISNULL(NULL,1) | W |
| W | SELECT COALESCE(NULL,1) | Y |
| P | SELECT [name], xtype FROM sysobjects ORDER BY xtype | M |
| M | SELECT [name], xtype FROM sysobjects ORDER BY 2 | P |
| H | SELECT NULLIF(1,1) | R |
| R | SELECT CASE 1 WHEN 1 THEN NULL ELSE 1 END | H |
| I | SELECT [name], xtype FROM sysobjects ORDER BY 1 | N |
| N | SELECT [name], xtype FROM sysobjects ORDER BY [name] | I |
| X | SELECT CAST(GETDATE() AS VARCHAR(20)) | F |
| F | SELECT CONVERT (VARCHAR(20), GETDATE()) | X |
| S | SELECT name FROM syscolumns WHERE id = ( SELECT id FROM sysobjects WHERE xtype = 'u' AND name = 'Jobs' ) | K |
| K | WITH objects (id) AS ( SELECT id FROM sysobjects WHERE xtype = 'u' AND name = 'Jobs' ) SELECT name FROM syscolumns INNER JOIN objects ON syscolumns.id = objects.id | S |
| L | INSERT INTO Job (JobID, PrimaryJobTypeID) VALUES (1,1) | B |
| B | INSERT INTO Job (JobID, PrimaryJobTypeID) SELECT 1,1 | L |
| C | DECLARE @sMessage AS VARCHAR(20) SET @sMessage = 'Hello' | E |
| E | DECLARE @sMessage AS VARCHAR(20) SELECT @sMessage = 'Hello' | C |
Labels: SQL, SQLServer2005
// posted by Jane @ 8:17 PM
Comments:
Generating insert statements from table data - Updated
Yesterday Christian left me a comment containing an enhancement for the spu_generateinsert SQL. His suggestion was adding a new parameter @GenerateOneLinePerColumn to allow more "pretty" SQL to be produced - producing all the columns on separate lines which makes it easier for file comparisons to spot differences when comparing data values. I thought it made sense, so this morning I used an hour of my Madgex ILP time to make this change.
By calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 0 it produces
-- ** Start of Inserts
INSERT INTO [Detail] ([ID], [Age]) VALUES (1,36)
INSERT INTO [Detail] ([ID], [Age]) VALUES (2,40)
-- ** End of Inserts
but by calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 1 it produces
-- ** Start of Inserts
INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
1,
36
)
INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
2,
40
)
-- ** End of Inserts
Thanks to Christian for this suggestion. The updated script can be found here and related blog posts can be found here.
Note: this is now only working on SQL 2000 as during my testing I found that VARCHAR(8000) just wasn't long enough so I've made it VARCHAR(max). This is the only SQL 2005 specific piece of SQL in this procedure and I've put comments in the code to indicate -- change this to be (8000) for SQL Server 2000.
By calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 0 it produces
-- ** Start of Inserts
INSERT INTO [Detail] ([ID], [Age]) VALUES (1,36)
INSERT INTO [Detail] ([ID], [Age]) VALUES (2,40)
-- ** End of Inserts
but by calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 1 it produces
-- ** Start of Inserts
INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
1,
36
)
INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
2,
40
)
-- ** End of Inserts
Thanks to Christian for this suggestion. The updated script can be found here and related blog posts can be found here.
Note: this is now only working on SQL 2000 as during my testing I found that VARCHAR(8000) just wasn't long enough so I've made it VARCHAR(max). This is the only SQL 2005 specific piece of SQL in this procedure and I've put comments in the code to indicate -- change this to be (8000) for SQL Server 2000.
Labels: spu_generateinsert, SQLServer, SQLServer2005
// posted by Jane @ 6:36 PM
Comments:
Wednesday, August 06, 2008
Intersect vs Inner Join - Timings
I read the article SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN - INTERSECT vs. INNER JOIN today and was interested in the relative speed of Inner join vs Intersect for simple matcjhes and so thought I'd make another use of my timing code to see the difference.
I produced a couple of very simple tables:
Summary ([ID] INT, [Name] NVARCHAR(10))
Detail ([ID] INT, [Age] INT)
both with primary keys on ID and populated them both as follows:
INSERT INTO Summary
SELECT 1, 'Jane' UNION ALL
SELECT 2, 'Richard'
INSERT INTO Detail
SELECT 1, 36 UNION ALL
SELECT 2,40
I chose to do a very simple task - retrieve the ID for an entry which is in both Summary and Detail - note: this assumes that the ID is a foreign key constraint and the IDs are relating to the same item.
Using my rudimentary timing code, I think that intersect is more performant than the inner join - based on running the same query 100000 times
SELECT Summary.ID
FROM Summary
INNER JOIN Detail
ON Summary.ID = Detail.ID
and
SELECT ID FROM Summary
INTERSECT
SELECT ID FROM Detail
which has the following results:
I ran them both together and took a look at the actual execution plans and it does indicate that the intersect takes a little less effort - which must be down to the Seek instead of Scan in the Join'd table detail
So, another option for me to remember to consider when determining the best way to get at some data
I produced a couple of very simple tables:
Summary ([ID] INT, [Name] NVARCHAR(10))
Detail ([ID] INT, [Age] INT)
both with primary keys on ID and populated them both as follows:
INSERT INTO Summary
SELECT 1, 'Jane' UNION ALL
SELECT 2, 'Richard'
INSERT INTO Detail
SELECT 1, 36 UNION ALL
SELECT 2,40
I chose to do a very simple task - retrieve the ID for an entry which is in both Summary and Detail - note: this assumes that the ID is a foreign key constraint and the IDs are relating to the same item.
Using my rudimentary timing code, I think that intersect is more performant than the inner join - based on running the same query 100000 times
SELECT Summary.ID
FROM Summary
INNER JOIN Detail
ON Summary.ID = Detail.ID
and
SELECT ID FROM Summary
INTERSECT
SELECT ID FROM Detail
which has the following results:
| Description | TimeInMS |
| Join | 11746|
| Intersect | 10203 |
I ran them both together and took a look at the actual execution plans and it does indicate that the intersect takes a little less effort - which must be down to the Seek instead of Scan in the Join'd table detail
So, another option for me to remember to consider when determining the best way to get at some data
Labels: SQL, SQLServer2005
// posted by Jane @ 6:28 PM
Comments:
Monday, July 28, 2008
Full Text Indexing - the impact of index time and query time language choice
Following on from my More on SQL Server 2005 Full Text Index Service post the other day, I thought I'd give an example of how it works
I created a table LanguageData which consisted of 2 fields liID and sValue
CREATE TABLE [dbo].[LanguageData]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Value] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_LanguageData] PRIMARY KEY CLUSTERED
(
[ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I entered some sample data as follows
INSERT INTO [LanguageData](Value)
SELECT 'the' UNION
SELECT 'przed' UNION
SELECT 'jakby'
where 'the' is featured in the English and Neutral language noise word files, 'przed' and 'jakby' are in the Polish language noise files. Note: You'll need to have installed the Polish full text index to make this work.
Next enable the full text indexing on the database
sp_fulltext_database 'enable'
and then create a full text catalog and an index for the table LanguageData
CREATE FULLTEXT CATALOG LanguageData AS DEFAULT
CREATE FULLTEXT INDEX ON LanguageData ([Value] LANGUAGE 1045 )
KEY INDEX [PK_LanguageData]
where 1045 indicates the language Polish - retrieved from
SELECT alias, lcid FROM Sys.syslanguages
WHERE alias = 'Polish'
Now, time to run some tests,
1) Check that all is initially correct, get everything
SELECT * FROM LanguageData
which returns 3 rows, as expected
2) Get everything which matches the noise word 'jakby'
SELECT * FROM LanguageData
WHERE CONTAINS(*,'jakby')
returns no rows as the word 'jakby' was stripped out at index time, and is also stripped out at query time, and a warning message "Informational: The full-text search condition contained noise word(s)."
3) Get everything which matches the noise word 'jakby' specifying Polish (1045) in the CONTAINS clause
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'jakby', language 1045 )
returns no rows as the word 'jakby' was stripped out at index time, and is also stripped out at query time, and a warning message "Informational: The full-text search condition contained noise word(s)."
4) Get everything which matches the word 'jakby' specifying US English (1033) in the CONTAINS clause
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'jakby', language 1033 )
returns no rows as the word 'jakby' was stripped out at index time. No warning message is displayed though as 'jakby' is not a noise word for US English
5) Get everything which matches the word 'the'
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'the')
returns one row, as 'the' isn't a noise word in Polish and so wasn't stripped out at index time or at query time
6) Get everything which matches the word 'the' specifying Polish in the CONTAINS clause
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'the', language 1045 )
returns one row, as 'the' isn't a noise word in Polish and so wasn't stripped out at index time or at query time
7) Get everything which matches the word 'the' specifying US English in the CONTAINS clause
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'the', language 1033 )
returns no rows as 'the' is a noise word in US English and therefore is excluded at query time. A warning message "Informational: The full-text search condition contained noise word(s)." is displayed
Now to make it more interesting, lets add some data which combines noise words with normal words
INSERT INTO [LanguageData] (Value)
VALUES
('jakby przed the test')
which includes 2 polish noise words, one english noise word and one remaining word
8) Get everything which matches the word 'jakby'
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'jakby')
returns no rows as the word 'jakby' was stripped out at index time, and is also stripped out at query time, and a warning message "Informational: The full-text search condition contained noise word(s)." is displayed
9) Get everything which matches the word 'the'
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'the')
returns 2 rows, both the individual 'the' entry and the new 'jakby przed the test' rows. No message is displayed.
10) Get everything which matches the word 'the' using an explicit query language of Polish
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'the', language 1045)
returns 2 rows, both the individual 'the' entry and the new 'jakby przed the test' rows. No message is displayed.
11) Get everything which matches the word 'the' using an explicit query language of English
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'the', language 1033 )
returns no rows as the word 'the' was stripped at query time according to the noise words for 1033. A warning message "Informational: The full-text search condition contained noise word(s)." is displayed
And then to make it even more interesting, lets add a new word 'jane' to the LanguageData dataset, and to the noisewords file for the Neutral language (LCID 0) which (on my machine at least) is at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseNEU.txt
To get the full text indexing service to pick up the changes to the noise files, you need to restart the service via the Control Panel -> Administrative Tools -> Service dialog
INSERT INTO LanguageData (Value)
VALUES ('jane')
12) Get everything which matches the word 'jane' using the implicit query language (Polish)
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'jane')
which returns 1 row, as 'jane' isn't a polish noise word and wasn't stripped out at either index or query time
13) Get everything which matches the word 'jane' using the explicit query language English
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'jane', language 1033 )
which returns 1 row, as 'jane' isn't a polish noise word and so wasn't stripped out an index time, neither is it an english noise word so isn't stripped out at query time either
14) Get everything which matches the word 'jane' using the explicit query language Neutral
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'jane', language 0 )
which returns 0 rows as 'jane' is a neutral noise word and so is stripped out at index time. A warning message "Informational: The full-text search condition contained noise word(s)." is displayed
What this shows, is that when you choose a language to set your full text index up as, this impacts the words which will be stripped out of the index as anything defined as noise will be removed. This has an impact on the choice of language when different language content is being indexed as we need to be clear that what is one languages noise word, isn't another ones non-noise word.
Setup
I created a table LanguageData which consisted of 2 fields liID and sValue
CREATE TABLE [dbo].[LanguageData]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Value] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_LanguageData] PRIMARY KEY CLUSTERED
(
[ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I entered some sample data as follows
INSERT INTO [LanguageData](Value)
SELECT 'the' UNION
SELECT 'przed' UNION
SELECT 'jakby'
where 'the' is featured in the English and Neutral language noise word files, 'przed' and 'jakby' are in the Polish language noise files. Note: You'll need to have installed the Polish full text index to make this work.
Next enable the full text indexing on the database
sp_fulltext_database 'enable'
and then create a full text catalog and an index for the table LanguageData
CREATE FULLTEXT CATALOG LanguageData AS DEFAULT
CREATE FULLTEXT INDEX ON LanguageData ([Value] LANGUAGE 1045 )
KEY INDEX [PK_LanguageData]
where 1045 indicates the language Polish - retrieved from
SELECT alias, lcid FROM Sys.syslanguages
WHERE alias = 'Polish'
Scenarios
Now, time to run some tests,
1) Check that all is initially correct, get everything
SELECT * FROM LanguageData
which returns 3 rows, as expected
2) Get everything which matches the noise word 'jakby'
SELECT * FROM LanguageData
WHERE CONTAINS(*,'jakby')
returns no rows as the word 'jakby' was stripped out at index time, and is also stripped out at query time, and a warning message "Informational: The full-text search condition contained noise word(s)."
3) Get everything which matches the noise word 'jakby' specifying Polish (1045) in the CONTAINS clause
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'jakby', language 1045 )
returns no rows as the word 'jakby' was stripped out at index time, and is also stripped out at query time, and a warning message "Informational: The full-text search condition contained noise word(s)."
4) Get everything which matches the word 'jakby' specifying US English (1033) in the CONTAINS clause
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'jakby', language 1033 )
returns no rows as the word 'jakby' was stripped out at index time. No warning message is displayed though as 'jakby' is not a noise word for US English
5) Get everything which matches the word 'the'
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'the')
returns one row, as 'the' isn't a noise word in Polish and so wasn't stripped out at index time or at query time
6) Get everything which matches the word 'the' specifying Polish in the CONTAINS clause
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'the', language 1045 )
returns one row, as 'the' isn't a noise word in Polish and so wasn't stripped out at index time or at query time
7) Get everything which matches the word 'the' specifying US English in the CONTAINS clause
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'the', language 1033 )
returns no rows as 'the' is a noise word in US English and therefore is excluded at query time. A warning message "Informational: The full-text search condition contained noise word(s)." is displayed
Now to make it more interesting, lets add some data which combines noise words with normal words
INSERT INTO [LanguageData] (Value)
VALUES
('jakby przed the test')
which includes 2 polish noise words, one english noise word and one remaining word
8) Get everything which matches the word 'jakby'
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'jakby')
returns no rows as the word 'jakby' was stripped out at index time, and is also stripped out at query time, and a warning message "Informational: The full-text search condition contained noise word(s)." is displayed
9) Get everything which matches the word 'the'
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'the')
returns 2 rows, both the individual 'the' entry and the new 'jakby przed the test' rows. No message is displayed.
10) Get everything which matches the word 'the' using an explicit query language of Polish
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'the', language 1045)
returns 2 rows, both the individual 'the' entry and the new 'jakby przed the test' rows. No message is displayed.
11) Get everything which matches the word 'the' using an explicit query language of English
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'the', language 1033 )
returns no rows as the word 'the' was stripped at query time according to the noise words for 1033. A warning message "Informational: The full-text search condition contained noise word(s)." is displayed
And then to make it even more interesting, lets add a new word 'jane' to the LanguageData dataset, and to the noisewords file for the Neutral language (LCID 0) which (on my machine at least) is at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseNEU.txt
To get the full text indexing service to pick up the changes to the noise files, you need to restart the service via the Control Panel -> Administrative Tools -> Service dialog
INSERT INTO LanguageData (Value)
VALUES ('jane')
12) Get everything which matches the word 'jane' using the implicit query language (Polish)
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'jane')
which returns 1 row, as 'jane' isn't a polish noise word and wasn't stripped out at either index or query time
13) Get everything which matches the word 'jane' using the explicit query language English
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'jane', language 1033 )
which returns 1 row, as 'jane' isn't a polish noise word and so wasn't stripped out an index time, neither is it an english noise word so isn't stripped out at query time either
14) Get everything which matches the word 'jane' using the explicit query language Neutral
SELECT * FROM LanguageData
WHERE CONTAINS(*, 'jane', language 0 )
which returns 0 rows as 'jane' is a neutral noise word and so is stripped out at index time. A warning message "Informational: The full-text search condition contained noise word(s)." is displayed
Summary
What this shows, is that when you choose a language to set your full text index up as, this impacts the words which will be stripped out of the index as anything defined as noise will be removed. This has an impact on the choice of language when different language content is being indexed as we need to be clear that what is one languages noise word, isn't another ones non-noise word.
- When querying a full text index, it is possible to specify that the query you are running is for a particular language, but if you do and if the language is different to that you set the index up as, then you'll remove 2 sets of noise words from your search - both those that were set up when the index was defined, but also those based on the language specified in the query
- The noise files are defined on an instance by instance basis and so any alterations to the noise file will affect all full text indexes on an instance.
- To pick up changes to the noise files, the service needs to be restarted.
- SQL Server 2008 seems to change this and so more research will be required - it relies on STOPLISTs instead.
Labels: full text index, localisation, localization, SQLServer2005
// posted by Jane @ 4:30 PM
Comments:
Thursday, July 24, 2008
More on SQL Server 2005 Full Text Index Service
In my previous post about How to work out which are valid full text languages on a SQL Server 2005 instance I referred to sys.syslanguages and sys.fulltext_languages in my queries, but didn't really say much more about them, so here goes
In the definition on MSDN it states
And one thing on the choice of U.S. English vs UK English. The SQL Server Full Text Search: Language Features says
As an English person, living in England and speaking English I find this a somewhat grating use of the phrase UK English. Bah!
In the definition on MSDN it states
The list in this table, doesn't match those in sys.syslanguages. These are purely the full-text-indexable languages. As I mentioned in my previous post 6 languages can be added by following these instructions. The line
SELECT *
FROM LanguageData
WHERE CONTAINS(*, 'the', language 1045 )
which indicates that the locale used for querying should be 1045, which equates to Polish. I have some sample SQL to post in the next few days which demonstrates the difference between indexing and querying language choices.
I've been doing quite a bit of work with trying to understand how the SQL Server 2005 full text index works, and how the language choice impacts it. My knowledge of full text indexing as a whole to this stage hasn't been great, so I've done quite a lot of background reading. Amongst the best resources I've found are: both by Hillary Cotter which provide a really simple, but yet pretty comprehensive introduction to the various features of indexing and querying using the Full Text Index service.
sys.syslanguages
In the definition on MSDN it states
"Contains one row for each language present in the instance of SQL Server 2005. Although U.S. English is not in syslanguages, it is always available to SQL Server."
And one thing on the choice of U.S. English vs UK English. The SQL Server Full Text Search: Language Features says
"In actual fact UK English does not refer to the Queen's English or the English used in the United Kingdom, but International English; the English that is used in all other English speaking countries other than US English."
As an English person, living in England and speaking English I find this a somewhat grating use of the phrase UK English. Bah!
sys.fulltext_languages
In the definition on MSDN it states
"This catalog view contains one row per language available for full-text indexing/querying operations. Each row provides an unambiguous representation of the available full-text linguistic resources that are registered with Microsoft SQL Server. The name or lcid can be specified in the full-text queries and full-text index DDL."
The list in this table, doesn't match those in sys.syslanguages. These are purely the full-text-indexable languages. As I mentioned in my previous post 6 languages can be added by following these instructions. The line
"The name or lcid can be specified in the full-text queries and full-text index DDL."refers to the ability to issue the following SQL:
SELECT *
FROM LanguageData
WHERE CONTAINS(*, 'the', language 1045 )
which indicates that the locale used for querying should be 1045, which equates to Polish. I have some sample SQL to post in the next few days which demonstrates the difference between indexing and querying language choices.
In General
I've been doing quite a bit of work with trying to understand how the SQL Server 2005 full text index works, and how the language choice impacts it. My knowledge of full text indexing as a whole to this stage hasn't been great, so I've done quite a lot of background reading. Amongst the best resources I've found are: both by Hillary Cotter which provide a really simple, but yet pretty comprehensive introduction to the various features of indexing and querying using the Full Text Index service.
Labels: full text index, localisation, localization, SQLServer2005
// posted by Jane @ 8:02 PM
Comments:
How to work out which are valid full text languages on a SQL Server 2005 instance
Despite SQL Server 2005 supporting 33 languages (found by issuing SELECT * FROM sys.syslanguages), not all of these are available for the full text index service. To find out which ones are run the query:
SELECT *
FROM sys.fulltext_languages
On my machine, this returns the following languages:
An additional 6 languages are supported and available for a separate install. These are :
To install these, follow the instructions here.
The following languages are not supported for full text searching at all within SQL Server 2005:
SQL Server 2008 offers more full text language support bringing the total of available languages to 50. It would appear that Danish, Polish and Turkish remain installable additions.
SELECT *
FROM sys.fulltext_languages
On my machine, this returns the following languages:
- British English
- Chinese (Hong Kong SAR, PRC)
- Chinese (Macau SAR)
- Chinese (Singapore)
- Simplified Chinese
- Traditional Chinese
- Dutch
- English
- French
- German
- Italian
- Japanese
- Korean
- Neutral
- Spanish
- Swedish
- Thai
An additional 6 languages are supported and available for a separate install. These are :
- Danish
- Polish
- Português (Brasil)
- Portuguese
- Russian
- Turkish
To install these, follow the instructions here.
The following languages are not supported for full text searching at all within SQL Server 2005:
- Arabic
- Bulgarian
- Croatian
- Czech
- Estonian
- Finnish
- Greek
- Hungarian
- Latvian
- Lithuanian
- Norwegian
- Romanian
- Slovak
- Slovenian
SQL Server 2008 offers more full text language support bringing the total of available languages to 50. It would appear that Danish, Polish and Turkish remain installable additions.
Labels: full text index, localisation, localization, SQLServer2005
// posted by Jane @ 7:36 PM
Comments:
Friday, July 18, 2008
TSQL: Enumations and constants
Bruce sent me a link the other day to an article T4 template for generating SQL view from C# enumeration which I found interesting from a modelling constants/enumerations in SQL viewpoint.
The example used was modelling an enumeration of ContactType which has valid items of Individual and Organisation.
The article used a view to model this, as per
CREATE VIEW enumContactType
AS
SELECT
0 AS Individual,
1 AS Organization
and then using it within a SELECT as
SELECT *
FROM Contact
WHERE Type = (SELECT Organization FROM enumContactType)
(Note: in the original article Oleg used a schema called enum, but I'm just ignoring this at the moment and have thus changed the name from enum.ContactType to enumContactType)
In my previous company, we used Scalar-Valued Functions to mimic constants, and I guess this could be extended to enumerations. I thought I'd re-create the above example and give it a try to see how it looks and compares.
So, to model the enumeration ContactType, I've created two functions as follows:
CREATE FUNCTION enumContactTypeIndividual()
RETURNS INT
AS
BEGIN
RETURN 0
END
GO
CREATE FUNCTION enumContactTypeOrganisation()
RETURNS INT
AS
BEGIN
RETURN 1
END
GO
And then to reproduce the SELECT query I wrote:
SELECT *
FROM Contact
WHERE Type = dbo.enumContactTypeOrganisation()
The resulting data matches that used in the VIEW model and provides an alternative. I'm sure that a template could be written to produce those functions as an output as per the end part of Oleg's article.
I was interested in the relative performance of these two methods, so armed with my timing code from last week I checked them out. I amended the SELECT to bring back the COUNT(*) FROM Contact into a local integer variable, and ran it 1000000 times.
The results are as follows:
showing that the view method is more performant.
I then changed the function to make use of SCHEMABINDING. The new functions look like:
CREATE FUNCTION enumContactTypeIndividual()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
RETURN 0
END
GO
CREATE FUNCTION enumContactTypeOrganisation()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
RETURN 1
END
GO
And the timings change to be:
which do reduce the time taken for the UDF but still means that the view is faster.
For interests sake I then ran a comparison timing against the code using the literal as:
SELECT *
FROM Contact
WHERE Type = 1
which resulted in
showing it is faster, but not by much, than the view.
So, what has this shown?
The example used was modelling an enumeration of ContactType which has valid items of Individual and Organisation.
The article used a view to model this, as per
CREATE VIEW enumContactType
AS
SELECT
0 AS Individual,
1 AS Organization
and then using it within a SELECT as
SELECT *
FROM Contact
WHERE Type = (SELECT Organization FROM enumContactType)
(Note: in the original article Oleg used a schema called enum, but I'm just ignoring this at the moment and have thus changed the name from enum.ContactType to enumContactType)
An alternative
In my previous company, we used Scalar-Valued Functions to mimic constants, and I guess this could be extended to enumerations. I thought I'd re-create the above example and give it a try to see how it looks and compares.
So, to model the enumeration ContactType, I've created two functions as follows:
CREATE FUNCTION enumContactTypeIndividual()
RETURNS INT
AS
BEGIN
RETURN 0
END
GO
CREATE FUNCTION enumContactTypeOrganisation()
RETURNS INT
AS
BEGIN
RETURN 1
END
GO
And then to reproduce the SELECT query I wrote:
SELECT *
FROM Contact
WHERE Type = dbo.enumContactTypeOrganisation()
The resulting data matches that used in the VIEW model and provides an alternative. I'm sure that a template could be written to produce those functions as an output as per the end part of Oleg's article.
Performance and timings
I was interested in the relative performance of these two methods, so armed with my timing code from last week I checked them out. I amended the SELECT to bring back the COUNT(*) FROM Contact into a local integer variable, and ran it 1000000 times.
The results are as follows:
| Code | Description | TimeInMS |
| EnumView | Using the view | 13010 |
| EnumUDF | Using the UDF | 21450 |
showing that the view method is more performant.
I then changed the function to make use of SCHEMABINDING. The new functions look like:
CREATE FUNCTION enumContactTypeIndividual()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
RETURN 0
END
GO
CREATE FUNCTION enumContactTypeOrganisation()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
RETURN 1
END
GO
And the timings change to be:
| Code | Description | TimeInMS |
| EnumView | Using the view | 13010 |
| EnumUDF | Using the UDF | 20280 |
which do reduce the time taken for the UDF but still means that the view is faster.
For interests sake I then ran a comparison timing against the code using the literal as:
SELECT *
FROM Contact
WHERE Type = 1
which resulted in
| Code | Description | TimeInMS |
| EnumLiteral | Using the literal | 12043 |
showing it is faster, but not by much, than the view.
Summary
So, what has this shown?
- Using a view is quite efficient and effective for modelling enumerations
- Using a UDF is an alternative, but is slower
- Schema binding makes UDF usage quicker
- The difference between using a VIEW and using the hard-coded literal isn't a lot in perfomance terms
Labels: SQL, SQLServer2005
// posted by Jane @ 1:24 PM
Comments:
Tuesday, July 08, 2008
INFORMATION_SCHEMA views
As I alluded to the other day, I'm gradually weaning myself off my dependency on (the fairly ugly) sys.objects, sys.columns etc as a way to query the meta data about my database. Instead I'm using the SQL-92 compliant INFORMATION_SCHEMA views.
So now, when I'm writing database upgrade scripts and attempting to write defensive SQL (which is my usual position these days, regardless of whether I think the script will be run more than once - lets just say I've learnt from making such assumptions) I usually wrap
ALTER TABLE
statements within
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'MyTable' AND Column_Name = 'MyNewColumn'),
CREATE TABLE
statements within
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name = 'MyTable') etc
The main area that I have to revert to the sys views for is indexes, and finding out what columns are included in which index, which is the uglier, but no-less-effective
SELECT
OBJECT_NAME (i.object_id) AS Tablename,
i.name AS IndexName,
c.name AS ColumnName,
CASE ic.is_descending_key
WHEN 1 THEN 'DESC'
ELSE 'ASC'
END as ColumnSort
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
INNER JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
INNER JOIN sys.objects o
on c.object_id = o.object_id
WHERE o.type = 'U'
ORDER BY TableName, indexName, ic.key_ordinal
MSDN has an interesting article Querying the SQL Server System Catalog FAQ which has examples for finding out (using the various object catalog views) many different areas of meta data across a SQL Server 2005 database and is worth using as a starting point.
Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server 2005 comply with the SQL-92 standard definition for the INFORMATION_SCHEMA.
So now, when I'm writing database upgrade scripts and attempting to write defensive SQL (which is my usual position these days, regardless of whether I think the script will be run more than once - lets just say I've learnt from making such assumptions) I usually wrap
ALTER TABLE
statements within
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'MyTable' AND Column_Name = 'MyNewColumn'),
CREATE TABLE
statements within
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name = 'MyTable') etc
The main area that I have to revert to the sys views for is indexes, and finding out what columns are included in which index, which is the uglier, but no-less-effective
SELECT
OBJECT_NAME (i.object_id) AS Tablename,
i.name AS IndexName,
c.name AS ColumnName,
CASE ic.is_descending_key
WHEN 1 THEN 'DESC'
ELSE 'ASC'
END as ColumnSort
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
INNER JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
INNER JOIN sys.objects o
on c.object_id = o.object_id
WHERE o.type = 'U'
ORDER BY TableName, indexName, ic.key_ordinal
MSDN has an interesting article Querying the SQL Server System Catalog FAQ which has examples for finding out (using the various object catalog views) many different areas of meta data across a SQL Server 2005 database and is worth using as a starting point.
Labels: SQL, SQLServer2005
// posted by Jane @ 2:20 PM
Comments:
Monday, July 07, 2008
TSQL - Timings
A while ago I blogged about how to get the date element of a datetime column in TSQL. In that post I said
but I didn't prove it at the time. I gave it some more thought and wanted to know what the differences were, so I wrote some timings code.
This script creates one table Timings with columns of Code, Description, ActionTime and IsComplete. It has a combined primary key of Code and IsComplete. Code must be unique - and can be a string of up to 10 characters long to uniquely identify the action being timed. IsComplete is used to differentiate between the start time and end time of the process being monitored.
The script also creates 3 stored procedures:
I wrote some script to then use these objects to test the assertion I made that FLOOR and combinations of converting DATETIME to FLOAT etc would be more efficient than using either CAST or CONVERT to VARCHAR(12) and back again to a DATETIME.
------------------------------
-- Clean up before we start --
------------------------------
DELETE FROM Timings
WHERE Code IN ('FLR','CONVERT','CAST')
GO
---------------------
-- Try using Floor --
---------------------
EXEC up_RecordStart @Code='FLR', @Description='SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))'
GO
DECLARE @i AS INTEGER
DECLARE @floorDate AS DATETIME
SET @i = 0
WHILE @i < 1000000 -- try the next statement for 1000000 times - this should be enough to see some differences
BEGIN
SET @floorDate = CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))
SET @i = @i + 1
END
PRINT @floorDate
GO
EXEC up_RecordEnd @Code='FLR'
GO
-----------------------
-- Try using convert --
-----------------------
EXEC up_RecordStart @Code='CONVERT', @Description='SELECT CONVERT(DATETIME,CONVERT(VARCHAR(12),GETDATE()))'
GO
DECLARE @i AS INTEGER
DECLARE @floorDate AS DATETIME
SET @i = 0
WHILE @i < 1000000
BEGIN
SET @floorDate = CONVERT(DATETIME,CONVERT(VARCHAR(12),GETDATE()))
SET @i = @i + 1
END
PRINT @floorDate
GO
EXEC up_RecordEnd @Code='CONVERT'
GO
--------------------
-- Try using Cast --
--------------------
EXEC up_RecordStart @Code='CAST', @Description='SELECT CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)'
GO
DECLARE @i AS INTEGER
DECLARE @floorDate AS DATETIME
SET @i = 0
WHILE @i < 1000000
BEGIN
SET @floorDate = CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)
SET @i = @i + 1
END
PRINT @floorDate
GO
EXEC up_RecordEnd @Code='CAST'
GO
-------------------------
-- Now get the timings --
-------------------------
EXEC up_GetTimings 'FLR'
GO
EXEC up_GetTimings 'CONVERT'
GO
EXEC up_GetTimings 'CAST'
GO
This results in the following data being returned:
which shows that the method using FLOOR is more efficient, and that there isn't a lot to chose between CONVERT and CAST
I would probably have done it via a CONVERT/CAST operation, converting to a VARCHAR and then back to a DATETIME, but this is a much more efficient method.
but I didn't prove it at the time. I gave it some more thought and wanted to know what the differences were, so I wrote some timings code.
This script creates one table Timings with columns of Code, Description, ActionTime and IsComplete. It has a combined primary key of Code and IsComplete. Code must be unique - and can be a string of up to 10 characters long to uniquely identify the action being timed. IsComplete is used to differentiate between the start time and end time of the process being monitored.
The script also creates 3 stored procedures:
- up_RecordStart which takes 2 parameters - the unique code and optional description. This is used to record the start of the activity being monitored.
- up_RecordEnd which takes just 1 parameter - the code - should match the code used in up_RecordStart. This is used to record the end of the activity being monitored.
- up_GetTimings which again takes just 1 parameter - the code to return the timings from. It then returns the Code, Description and the length of time the action took in ms.
I wrote some script to then use these objects to test the assertion I made that FLOOR and combinations of converting DATETIME to FLOAT etc would be more efficient than using either CAST or CONVERT to VARCHAR(12) and back again to a DATETIME.
------------------------------
-- Clean up before we start --
------------------------------
DELETE FROM Timings
WHERE Code IN ('FLR','CONVERT','CAST')
GO
---------------------
-- Try using Floor --
---------------------
EXEC up_RecordStart @Code='FLR', @Description='SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))'
GO
DECLARE @i AS INTEGER
DECLARE @floorDate AS DATETIME
SET @i = 0
WHILE @i < 1000000 -- try the next statement for 1000000 times - this should be enough to see some differences
BEGIN
SET @floorDate = CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))
SET @i = @i + 1
END
PRINT @floorDate
GO
EXEC up_RecordEnd @Code='FLR'
GO
-----------------------
-- Try using convert --
-----------------------
EXEC up_RecordStart @Code='CONVERT', @Description='SELECT CONVERT(DATETIME,CONVERT(VARCHAR(12),GETDATE()))'
GO
DECLARE @i AS INTEGER
DECLARE @floorDate AS DATETIME
SET @i = 0
WHILE @i < 1000000
BEGIN
SET @floorDate = CONVERT(DATETIME,CONVERT(VARCHAR(12),GETDATE()))
SET @i = @i + 1
END
PRINT @floorDate
GO
EXEC up_RecordEnd @Code='CONVERT'
GO
--------------------
-- Try using Cast --
--------------------
EXEC up_RecordStart @Code='CAST', @Description='SELECT CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)'
GO
DECLARE @i AS INTEGER
DECLARE @floorDate AS DATETIME
SET @i = 0
WHILE @i < 1000000
BEGIN
SET @floorDate = CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)
SET @i = @i + 1
END
PRINT @floorDate
GO
EXEC up_RecordEnd @Code='CAST'
GO
-------------------------
-- Now get the timings --
-------------------------
EXEC up_GetTimings 'FLR'
GO
EXEC up_GetTimings 'CONVERT'
GO
EXEC up_GetTimings 'CAST'
GO
This results in the following data being returned:
| Code | Description | TimeInMS |
| FLR | SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE()))) | 1313 |
| CONVERT | SELECT CONVERT(DATETIME,CONVERT(VARCHAR(12),GETDATE())) | 3236 |
| CAST | SELECT CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME) | 3203 |
which shows that the method using FLOOR is more efficient, and that there isn't a lot to chose between CONVERT and CAST
Labels: SQL, SQLServer2005
// posted by Jane @ 7:11 PM
Comments:
Monday, June 30, 2008
Find a string in a stored procedure - Revisited
A little over 2 years ago I posted a couple of methods of finding the instances of a string within stored procedures - at the time I'd left some debug 'Print @' code in at least one stored procedure and needed to find it before releasing the code.
Today, I wanted to do the same thing, and as over the past 2 years I've been weaning myself off accessing the sysobjects tables, instead making use of the INFORMATION_SCHEMA views, I decided it was time to add an update to that earlier post.
So, using INFORMATION_SCHEMA.ROUTINES it is coded as :
SELECT SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%PRINT @%'
Today, I wanted to do the same thing, and as over the past 2 years I've been weaning myself off accessing the sysobjects tables, instead making use of the INFORMATION_SCHEMA views, I decided it was time to add an update to that earlier post.
So, using INFORMATION_SCHEMA.ROUTINES it is coded as :
SELECT SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%PRINT @%'
Labels: SQL, SQLServer, SQLServer2005
// posted by Jane @ 2:53 PM
Comments:
Monday, June 16, 2008
Scripting out object level SQL
In SQL Server 2000 Enterprise Manager provided the ability to output SQL file on an object level for tables, functions, stored procedures etc. In SQL Server 2005 this option disapeared and the only outputting was in a single file. This wasn't great for controlling objects within a source control system.
A while ago I stumbled across Scriptio, a ClickOnce application to do just this. It has a very simple interface, and you can tell that it was designed to solve a problem, rather than as an end solution.
To get a list of database objects, enter a Server name, select either Use Windows Authentication or enter a Username and password and press Connect. This should populate the Database drop down. Select a database and the bottom section should be populated with the objects that are scriptable within your database. From here, it is a simple case of selecting the appropriate options from the top right hand section (Include CREATE, Include DROP etc) and selecting the appropriate objects from the bottom section (there is a handy Script All option which selects everything. I have never been able to get the Schema or Type drop downs to populate, which may be something to do with the design of my objects.
The options tab allows choices of how many files to create (or even if you want to create a file), where to store them and some additional scripting objects (Include collation, Qualify CREATEs with Schema etc).
This tab displays the SQL generated - in an single box, allowing the content to be copied to clipboard and used elsewhere. Just as an observation, I've never seen the "Save As..." button enabled but I've never needed to use it either so that hasn't been a problem.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Book]') AND type in (N'U'))
DROP TABLE [dbo].[Book]
GO
/****** Object: Table [dbo].[Book] Script Date: 06/16/2008 17:30:18 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Book](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[Author] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[PublisherId] [int] NULL,
[ISBN] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[PublishedYear] [int] NULL,
[BookAgeId] AS (case datepart(year,getdate())-[PublishedYear] when (0) then (1) when (1) then (2) else (3) end)
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetBooks]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[up_GetBooks]
GO
/****** Object: StoredProcedure [dbo].[up_GetBooks] Script Date: 06/16/2008 17:30:19 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jane Dallaway
-- Create date: 12 June 2008
-- Description: Get all books, in full detail
-- =============================================
CREATE PROCEDURE up_GetBooks
AS
BEGIN
SET NOCOUNT ON;
SELECT Book.Id, Book.Title, Book.Author, Publisher.Name, Book.ISBN, Book.PublishedYear, BookAge.Name BookAge
FROM Book
INNER JOIN BookAge
ON Book.BookAgeId = BookAge.Id
INNER JOIN Publisher
ON Book.PublisherId = Publisher.Id
END
GO
This is a great utility to allow scripting on an object by object basis. The source code is available too.
A while ago I stumbled across Scriptio, a ClickOnce application to do just this. It has a very simple interface, and you can tell that it was designed to solve a problem, rather than as an end solution.
Tab One - Database Objects
To get a list of database objects, enter a Server name, select either Use Windows Authentication or enter a Username and password and press Connect. This should populate the Database drop down. Select a database and the bottom section should be populated with the objects that are scriptable within your database. From here, it is a simple case of selecting the appropriate options from the top right hand section (Include CREATE, Include DROP etc) and selecting the appropriate objects from the bottom section (there is a handy Script All option which selects everything. I have never been able to get the Schema or Type drop downs to populate, which may be something to do with the design of my objects.
Tab Two - More Options
The options tab allows choices of how many files to create (or even if you want to create a file), where to store them and some additional scripting objects (Include collation, Qualify CREATEs with Schema etc).
Tab Three - Generated Script
This tab displays the SQL generated - in an single box, allowing the content to be copied to clipboard and used elsewhere. Just as an observation, I've never seen the "Save As..." button enabled but I've never needed to use it either so that hasn't been a problem.
Sample script - a table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Book]') AND type in (N'U'))
DROP TABLE [dbo].[Book]
GO
/****** Object: Table [dbo].[Book] Script Date: 06/16/2008 17:30:18 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Book](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[Author] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[PublisherId] [int] NULL,
[ISBN] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[PublishedYear] [int] NULL,
[BookAgeId] AS (case datepart(year,getdate())-[PublishedYear] when (0) then (1) when (1) then (2) else (3) end)
) ON [PRIMARY]
GO
Sample script - a stored procedure
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetBooks]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[up_GetBooks]
GO
/****** Object: StoredProcedure [dbo].[up_GetBooks] Script Date: 06/16/2008 17:30:19 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jane Dallaway
-- Create date: 12 June 2008
-- Description: Get all books, in full detail
-- =============================================
CREATE PROCEDURE up_GetBooks
AS
BEGIN
SET NOCOUNT ON;
SELECT Book.Id, Book.Title, Book.Author, Publisher.Name, Book.ISBN, Book.PublishedYear, BookAge.Name BookAge
FROM Book
INNER JOIN BookAge
ON Book.BookAgeId = BookAge.Id
INNER JOIN Publisher
ON Book.PublisherId = Publisher.Id
END
GO
Summary
This is a great utility to allow scripting on an object by object basis. The source code is available too.
Labels: SQL, SQLServer2005
// posted by Jane @ 5:43 PM
Comments:
Monday, June 09, 2008
Database Change Management: Tarantino review
Last week, Chris Alcock included a mention of an article .NET Database Migration Tool Roundup in The Morning Brew #109. Both Bruce and I read this and came to the same conclusion, that Tarantino might be worth a look to stop us from continuing writing our own tool to manage the change process reliably. Today we invested some more of our ILP time into examining how it works, and what it does.
Firstly, there isn't much documentation, so we had to fumble around a bit to work out how to use it. I read the wiki page with interest, and found myself agreeing with what was said there. I especially liked the quote:
We found that this documentation, and the process description validated our aims and thoughts on how the process should work, suggesting:
We aren't, initially, looking at a tool that needs to integrate with an automated build environment, so we didn't need a lot of the features that Tarantino offers. Instead we concentrated on the Tarantino.DatabaseManager.exe application and worked with that. The result of this is that some of our requirements aren't met by the DatabaseManager but may be met by some of the other aspects of Tarantino.
The user interface is fairly self explanatory, and there is a .config file associated with it to pre-fill the default values. There are 4 possible actions: Create, Update, Drop and Rebuild.
DatabaseManager uses file naming as a convention for determining the order in which the update files will run - the recommendation for naming is 0001_Script.sql, 0002_Script.sql etc however during our investigation we discovered that we could leave Copy of 0001_Script.sql hanging around and this would just get appended to the list in alphabetical order. We were somewhat concerned that during the development process amongst a team, more than one person could be working on database updates at a time (and when using a local database, there is no guarantee that anyone else would know about it) which could mean, at best, two files with the same prefix, i.e. 0003_AddTableArticles.sql and 0003_FixBug12122.sql and at worse a source control conflict of 0003_Script.sql. This is where naming conventions would obviously come into play. During our discussions we liked the idea of some form of dependency mapping - i.e. 0003_AddTableArticles.sql is dependent on the change 0001_Script.sql. This would obviously help us to ensure that errors with code vs database versioning could be avoided. This isn't provided within Tarantino.
DatabaseManager records the scripts run in a table named usd_AppliedDatabaseScript in the database that is being worked on. This means that all actions will not be recorded in perpetuity as choosing the action Drop or Rebuild from the user interface would remove this table as well as all others, and the database itself. One of our preferences is to have a central database per server which records the scripts run - this would mean that the dba team would be able to see, at a minimum, what scripts have been run and when. We also feel that having some form of contact details associated with the script is a useful addition - this seems to be covered in the deployment tool that is included in Tarantino, but not on a script by script basis.
One of the process pre-requisites mentioned for Tarantino is:
Note: All tests were carried out on a Windows XP laptop running a local installation of SQL Server 2005.
We have previously, also looked at DBVerse to meet our requirements. As neither tool satisfies what we're attempting to do, we'll continue writing our own but this was a worthwhile diversion as it really validates our thoughts on process vs program, and how that process should look.
Firstly, there isn't much documentation, so we had to fumble around a bit to work out how to use it. I read the wiki page with interest, and found myself agreeing with what was said there. I especially liked the quote:
"Successful database change management requires that a consistent process be applied by all team members. Without a consistent process than the tools provided in this solution will not provide its full value"
We found that this documentation, and the process description validated our aims and thoughts on how the process should work, suggesting:
- a local database for each developer - this works for me on 2 levels, firstly in the same way that a good developer wouldn't check their code into source control until it was complete, the database changes shouldn't affect anyone else until this stage either, and secondly database changes get tested by other team members
- changes are implemented as SQL scripts
- there is a predefined order in which the SQL scripts should run
We aren't, initially, looking at a tool that needs to integrate with an automated build environment, so we didn't need a lot of the features that Tarantino offers. Instead we concentrated on the Tarantino.DatabaseManager.exe application and worked with that. The result of this is that some of our requirements aren't met by the DatabaseManager but may be met by some of the other aspects of Tarantino.
The user interface is fairly self explanatory, and there is a .config file associated with it to pre-fill the default values. There are 4 possible actions: Create, Update, Drop and Rebuild.
- Create - the default. This will create the database identified in the Database field (I didn't find how you specified the settings for database creation during my 1.5 hour investigation, but I'm sure it's there somewhere). It will then update the database identified in the Database field according to scripts found in the Update sub-folder of the folder specified in the Script Folder field, running them in alphabetical order.
- Update - This will update the database identified in the Database field according to scripts found in the Update sub-folder of the folder specified in the Script Folder field, running them in alphabetical order if they haven't already been reported as run in the table of scripts
- Drop - This will drop the database identified in the Database field
- Rebuild - This will combine the Drop and Create tasks above.
DatabaseManager uses file naming as a convention for determining the order in which the update files will run - the recommendation for naming is 0001_Script.sql, 0002_Script.sql etc however during our investigation we discovered that we could leave Copy of 0001_Script.sql hanging around and this would just get appended to the list in alphabetical order. We were somewhat concerned that during the development process amongst a team, more than one person could be working on database updates at a time (and when using a local database, there is no guarantee that anyone else would know about it) which could mean, at best, two files with the same prefix, i.e. 0003_AddTableArticles.sql and 0003_FixBug12122.sql and at worse a source control conflict of 0003_Script.sql. This is where naming conventions would obviously come into play. During our discussions we liked the idea of some form of dependency mapping - i.e. 0003_AddTableArticles.sql is dependent on the change 0001_Script.sql. This would obviously help us to ensure that errors with code vs database versioning could be avoided. This isn't provided within Tarantino.
DatabaseManager records the scripts run in a table named usd_AppliedDatabaseScript in the database that is being worked on. This means that all actions will not be recorded in perpetuity as choosing the action Drop or Rebuild from the user interface would remove this table as well as all others, and the database itself. One of our preferences is to have a central database per server which records the scripts run - this would mean that the dba team would be able to see, at a minimum, what scripts have been run and when. We also feel that having some form of contact details associated with the script is a useful addition - this seems to be covered in the deployment tool that is included in Tarantino, but not on a script by script basis.
One of the process pre-requisites mentioned for Tarantino is:
"Create a change script that wraps all of the database changes into a single transactional change script. A Tool like Red Gate SQL Compare makes this a 30 second operation."This unfortunately means that error handling is poor and transaction handling is non existant. One of our tests was to create a file containing invalid SQL amongst the (valid and not applied) change scripts. The error was reported in the output window amongst nAnt exception reporting - making it quite hard to track down. When the error occurred, then data relating to the preceding SQL files in that Update batch will remain in the database but the subsequent files will not even be called - this leaves the database in a state of uncertainty. The table usd_AppliedDatabaseScript gets a row added for any previous files successfully completed, but the Version gets left as NULL. When I corrected the SQL and re-ran the process, the Version column gets updated to match the version number for the rest of the files successfully applied in the batch. This results in different date/time stamps on the rows, but the Version number being the same which makes me wonder if tracking the version history of the database is then subject to a certain amount of interpretation. In addition, selecting the action Create from the user interface when the database already exists results in an exception being raised - in my opinion this should be handled via some defensive SQL coding.
Note: All tests were carried out on a Windows XP laptop running a local installation of SQL Server 2005.
We have previously, also looked at DBVerse to meet our requirements. As neither tool satisfies what we're attempting to do, we'll continue writing our own but this was a worthwhile diversion as it really validates our thoughts on process vs program, and how that process should look.
Labels: database change management, ilp, SQLServer2005
// posted by Jane @ 6:56 PM
Comments:
Friday, May 30, 2008
Searching for 'special' characters in a database using TSQL
One of my colleagues asked me for some quick SQL help earlier. Some data in the database he was looking at had ended up with a 'special character' on the end of it in a specific table. This didn't affect all rows, just some, but he needed helping identify those rows.
For the sake of arguments, lets imagine that the table was called Item, and the affected column was named Name, and there was also an Id column named ItemId. He knew one of the affected Items, and so we examined the data to determine what character it was at the end. To do this we did
SELECT ASCII(SUBSTRING(Name,10,1))
FROM Item
WHERE ItemId = 100
This informed us that the character was actually ASCII value 160. Knowing this, I then used the PATINDEX function and produced a query like:
SELECT *
FROM Item
WHERE PATINDEX('%' + CHAR(160) + '%',Name) > 0
which seemed to return the row that he'd already identified along with new ones. This is probably not the most performant method, but this was needed to check something in a debugging/support environment and not code that would be run on a regular basis.
For the sake of arguments, lets imagine that the table was called Item, and the affected column was named Name, and there was also an Id column named ItemId. He knew one of the affected Items, and so we examined the data to determine what character it was at the end. To do this we did
SELECT ASCII(SUBSTRING(Name,10,1))
FROM Item
WHERE ItemId = 100
This informed us that the character was actually ASCII value 160. Knowing this, I then used the PATINDEX function and produced a query like:
SELECT *
FROM Item
WHERE PATINDEX('%' + CHAR(160) + '%',Name) > 0
which seemed to return the row that he'd already identified along with new ones. This is probably not the most performant method, but this was needed to check something in a debugging/support environment and not code that would be run on a regular basis.
Labels: SQL, SQLServer2005
// posted by Jane @ 12:56 PM
Comments:
Wednesday, April 09, 2008
TSQLUnit Updates - Helper Functions
Last week I attended another ILP workshop, this time about nUnit. This made me remember about the TSQLUnit testing framework, and the changes I had made to it whilst using it at my previous company and the fact that these changes were just sitting in a file somewhere and had never been shared.
The project which benefited from the most from TSQLUnit was one working with a large set of complicated calculations. Due to the nature of the data, this work was carried out in the database layer. These calculations were based on a well specified system, and so was a great candidate for thorough unit testing. Whilst building the calculation engine, I enhanced the base TSQLUnit installation to have some additional helper functions to save me coding the same thing over and over again.
I added a set of value checking helper procedures as follows
tsu_CheckValues_Decimal, tsu_CheckValues_Int, tsu_CheckValues_String, tsu_CheckValues_Date, tsu_CheckValues_Money all of which take 3 parameters - the first the expected data value, the second the actual data value, and the third an optional parameter expressing the message to be displayed to the developer if this assertion fails.
For example, EXEC tsu_CheckValues_Int 0,1,'Oops'
results in the following error being recorded 'Integer value mismatch Oops - Unexpected Result. Expected: 0. Actual: 1'
In additon I added some helper functions to assist in the generation of error messages as follows
tsu_GenerateErrorMessage_Date, tsu_GenerateErrorMessage_Int, tsu_GenerateErrorMessage_Real, tsu_GenerateErrorMessage_String all of which take 3 parameters, the first being the initial part of the message to display, the second the expected data value and the final the actual data value.
For example, SELECT dbo.tsu_GenerateErrorMessage_Int('Integer value mismatch',0,1) which results in the following text being generated 'Integer value mismatch - Unexpected Result. Expected: 0. Actual: 1'
The helper functions do not alter or change the basis under which TSQLUnit operates, and so they should install and work happily within existing TSQLUnit installations. Download the TSQLUnit Helper Functions.
I have also provided a script containing sample TSQLUnit tests - one coded to pass, one coded to fail, both making use of the tsu_CheckValues_Int.
In another post I will go into the other changes I made, and the recommendations for testing we came up with as a result of this exercise.
The project which benefited from the most from TSQLUnit was one working with a large set of complicated calculations. Due to the nature of the data, this work was carried out in the database layer. These calculations were based on a well specified system, and so was a great candidate for thorough unit testing. Whilst building the calculation engine, I enhanced the base TSQLUnit installation to have some additional helper functions to save me coding the same thing over and over again.
I added a set of value checking helper procedures as follows
tsu_CheckValues_Decimal, tsu_CheckValues_Int, tsu_CheckValues_String, tsu_CheckValues_Date, tsu_CheckValues_Money all of which take 3 parameters - the first the expected data value, the second the actual data value, and the third an optional parameter expressing the message to be displayed to the developer if this assertion fails.
For example, EXEC tsu_CheckValues_Int 0,1,'Oops'
results in the following error being recorded 'Integer value mismatch Oops - Unexpected Result. Expected: 0. Actual: 1'
In additon I added some helper functions to assist in the generation of error messages as follows
tsu_GenerateErrorMessage_Date, tsu_GenerateErrorMessage_Int, tsu_GenerateErrorMessage_Real, tsu_GenerateErrorMessage_String all of which take 3 parameters, the first being the initial part of the message to display, the second the expected data value and the final the actual data value.
For example, SELECT dbo.tsu_GenerateErrorMessage_Int('Integer value mismatch',0,1) which results in the following text being generated 'Integer value mismatch - Unexpected Result. Expected: 0. Actual: 1'
The helper functions do not alter or change the basis under which TSQLUnit operates, and so they should install and work happily within existing TSQLUnit installations. Download the TSQLUnit Helper Functions.
I have also provided a script containing sample TSQLUnit tests - one coded to pass, one coded to fail, both making use of the tsu_CheckValues_Int.
In another post I will go into the other changes I made, and the recommendations for testing we came up with as a result of this exercise.
Labels: ilp, SQLServer2000, SQLServer2005, TSQLUnit
// posted by Jane @ 1:57 PM
Comments:
Wednesday, March 26, 2008
Defensive SQL Updates
One of my recent tasks, well before my month off anyway, was to upgrade a number of similar SQL Server 2005 databases to a new structure. The databases had all started off the same, and were going to end up the same, but had been subjected to a differing number of bug fixes and enhancements along the way.
If I was updating just one database I would have used the excellent Redgate SQL Compare (of which more in a future post) but as I had to update many then this would not have been the most reliable way.
Instead, I wrote defensive SQL, that is SQL which checks for impact before doing it. So, before adding a column for instance, it checks to see if that column exists, and only attempts to add it if it doesn't - thus preventing errors. The whole update script was wrapped in a Transaction, and by making use use of a variable @bError of type BIT to track any errors, the last statement could COMMIT or ROLLBACK the transaction as appropriate.
So, for example, to add a new column to a table, here is the script I used:
IF @bError = 0
BEGIN
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'MyTable' AND Column_Name = 'MyNewColumn')
BEGIN
ALTER TABLE MyTable ADD MyNewColumn INT NOT NULL
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'MyTable' AND Column_Name = 'MyNewColumn')
BEGIN
PRINT 'Error Adding column ColumnName to table MyTable'
SET @bError = 1
END
ELSE
BEGIN
PRINT 'Added column ColumnName to table MyTable'
END
END
END
By checking for the column's existance after applying the change, I could determine if an error had occurred, and set the @bError flag accordingly. I used the Print statements to produce a log of exactly what stages had been carried out on each database, using the function DB_NAME() to output the name of the current database at the beginning of the log.
If I was updating just one database I would have used the excellent Redgate SQL Compare (of which more in a future post) but as I had to update many then this would not have been the most reliable way.
Instead, I wrote defensive SQL, that is SQL which checks for impact before doing it. So, before adding a column for instance, it checks to see if that column exists, and only attempts to add it if it doesn't - thus preventing errors. The whole update script was wrapped in a Transaction, and by making use use of a variable @bError of type BIT to track any errors, the last statement could COMMIT or ROLLBACK the transaction as appropriate.
So, for example, to add a new column to a table, here is the script I used:
IF @bError = 0
BEGIN
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'MyTable' AND Column_Name = 'MyNewColumn')
BEGIN
ALTER TABLE MyTable ADD MyNewColumn INT NOT NULL
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'MyTable' AND Column_Name = 'MyNewColumn')
BEGIN
PRINT 'Error Adding column ColumnName to table MyTable'
SET @bError = 1
END
ELSE
BEGIN
PRINT 'Added column ColumnName to table MyTable'
END
END
END
By checking for the column's existance after applying the change, I could determine if an error had occurred, and set the @bError flag accordingly. I used the Print statements to produce a log of exactly what stages had been carried out on each database, using the function DB_NAME() to output the name of the current database at the beginning of the log.
Labels: SQL, SQLServer2005
// posted by Jane @ 9:37 AM
Comments:
Thursday, January 24, 2008
Finding some text within a SQL Server 2005 Database
I was asked what sounded like a fairly simple question today - is there a way to do a full text search of a database, without having to set up full text searching. As this was to answer an immediate query - where was some text that was being displayed coming from - and not within the context of a query to form part of an application I thought about it a while, and decided that some dynamic SQL was what was needed. I started off with the fact that I wanted to pull together a statement like:
SELECT COUNT(*) FROM TableName WHERE ColumnName LIKE '%SearchTerm%'
and then run that across all columns which are text, ntext, varchar, nvarchar, char or nchar.
Getting the list of table and column names is reasonably straightforward
SELECT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar', 'nvarchar', 'text', 'ntext', 'char', 'nchar')
I wanted to loop through all of these and get the counts, so I used a local table variable to hold the results
DECLARE @tabSearchableColumns TABLE (TableName VARCHAR(100), ColumnName VARCHAR(100), Matches int) and simply inserted the results of the above query into it (along with a NULL for the Matches column). I then set up a loop, looping based on the COUNT(*) FROM @tabSearchableColumns WHERE Matches IS NULL. Within that loop I take the top item from @tabSearchableColumns, and use the TableName and ColumnName to populate the SQL SELECT above.
Now came the interesting part, getting the variable @intDataCount from the following statement:
EXEC (SELECT @intDataCount = COUNT(*) FROM TableName WHERE ColumnName LIKE '%SearchTerm%)
as the variable @intDataCount has the scope of the execute statement, and doesn't get propogated to any outside variable with the same name.
To get around this involves the use of sp_executesql specifying the parameter that we expect to get out, i.e. EXEC sp_executesql @strSQL, N'@intDataCount INT OUTPUT', @intDataCount OUTPUT
An important point here is that @strSQL, or the string being sent in as the SQL string MUST be cast be an NVARCHAR, either by DECLARE @strSQL NVARCHAR(100) or N'SELECT ColumnName FROM TableName'. Otherwise, don't be surprised to encounter the following error:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Finally, having got the count out into my @intDataCount variable, I can update the appropriate row in tabSearchableColumns, redo the COUNT(*) FROM @tabSearchableColumns WHERE Matches IS NULL and continue looping. The final stage is just to select the table and column data which has Matches > 0.
The script for FindTableColumnDataMatches is available for download, and as with sp_generateinsert is tested against SQL Server 2005, leave me comments if you've found this useful, or have suggestions for improvements.
SELECT COUNT(*) FROM TableName WHERE ColumnName LIKE '%SearchTerm%'
and then run that across all columns which are text, ntext, varchar, nvarchar, char or nchar.
Getting the list of table and column names is reasonably straightforward
SELECT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar', 'nvarchar', 'text', 'ntext', 'char', 'nchar')
I wanted to loop through all of these and get the counts, so I used a local table variable to hold the results
DECLARE @tabSearchableColumns TABLE (TableName VARCHAR(100), ColumnName VARCHAR(100), Matches int) and simply inserted the results of the above query into it (along with a NULL for the Matches column). I then set up a loop, looping based on the COUNT(*) FROM @tabSearchableColumns WHERE Matches IS NULL. Within that loop I take the top item from @tabSearchableColumns, and use the TableName and ColumnName to populate the SQL SELECT above.
Now came the interesting part, getting the variable @intDataCount from the following statement:
EXEC (SELECT @intDataCount = COUNT(*) FROM TableName WHERE ColumnName LIKE '%SearchTerm%)
as the variable @intDataCount has the scope of the execute statement, and doesn't get propogated to any outside variable with the same name.
To get around this involves the use of sp_executesql specifying the parameter that we expect to get out, i.e. EXEC sp_executesql @strSQL, N'@intDataCount INT OUTPUT', @intDataCount OUTPUT
An important point here is that @strSQL, or the string being sent in as the SQL string MUST be cast be an NVARCHAR, either by DECLARE @strSQL NVARCHAR(100) or N'SELECT ColumnName FROM TableName'. Otherwise, don't be surprised to encounter the following error:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Finally, having got the count out into my @intDataCount variable, I can update the appropriate row in tabSearchableColumns, redo the COUNT(*) FROM @tabSearchableColumns WHERE Matches IS NULL and continue looping. The final stage is just to select the table and column data which has Matches > 0.
The script for FindTableColumnDataMatches is available for download, and as with sp_generateinsert is tested against SQL Server 2005, leave me comments if you've found this useful, or have suggestions for improvements.
Labels: FindTableColumnDataMatches, SQL, SQLServer2005
// posted by Jane @ 7:59 PM
Comments:
Wednesday, January 23, 2008
Update to spu_generateinsert - use of UNION SELECT
Jon Green from 4R Systems Inc left a comment earlier proposing a suggested improvement to spu_generateinsert.
He suggested that it would be easier to scan the produced SQL if it made use of SELECT and UNION SELECT rather than producing a whole host of INSERT statements. This changes the output from:
INSERT INTO [TableName] ([ColumnA], [ColumnB]) VALUES ('A','B')
INSERT INTO [TableName] ([ColumnA], [ColumnB]) VALUES ('C','D')
and replaces it with
INSERT INTO [TableName] ([ColumnA], [ColumnB])
SELECT 'A','B'
UNION SELECT 'C','D'
This is a great suggestion and I have implemented it by the use of an additional parameter @producesingleinsert. By default this is set to 0 which produces the separate INSERT statements as it has done all along. If it is specified as 1, however, it now produces a single statement making use of SELECT and UNION SELECT. I have implemented this as an optional parameter so as not to break any reliance on this functionality.
I also made another change today which is to exclude calculated columns. This has involved joining INFORMATION_SCHEMA.COLUMNS to syscolumns via sysobjects to make use of syscolumns.IsComputed.
The final change was the addition of another optional parameter @ debug. Again this defaults to 0, but if set to 1 prints out the SELECT clause which will be used to obtain the data. This can help to debug problems with the procedure, both in terms of the internal logic, but also with regards to checking that the criteria specified is being handled correctly.
As ever this is work in progress, so please continue sending in your suggestions and I'll attempt to implement them.
Download the script from here.
He suggested that it would be easier to scan the produced SQL if it made use of SELECT and UNION SELECT rather than producing a whole host of INSERT statements. This changes the output from:
INSERT INTO [TableName] ([ColumnA], [ColumnB]) VALUES ('A','B')
INSERT INTO [TableName] ([ColumnA], [ColumnB]) VALUES ('C','D')
and replaces it with
INSERT INTO [TableName] ([ColumnA], [ColumnB])
SELECT 'A','B'
UNION SELECT 'C','D'
This is a great suggestion and I have implemented it by the use of an additional parameter @producesingleinsert. By default this is set to 0 which produces the separate INSERT statements as it has done all along. If it is specified as 1, however, it now produces a single statement making use of SELECT and UNION SELECT. I have implemented this as an optional parameter so as not to break any reliance on this functionality.
I also made another change today which is to exclude calculated columns. This has involved joining INFORMATION_SCHEMA.COLUMNS to syscolumns via sysobjects to make use of syscolumns.IsComputed.
The final change was the addition of another optional parameter @ debug. Again this defaults to 0, but if set to 1 prints out the SELECT clause which will be used to obtain the data. This can help to debug problems with the procedure, both in terms of the internal logic, but also with regards to checking that the criteria specified is being handled correctly.
As ever this is work in progress, so please continue sending in your suggestions and I'll attempt to implement them.
Download the script from here.
Labels: spu_generateinsert, SQL, SQLServer2005
// posted by Jane @ 6:52 PM
Comments:
Tuesday, January 15, 2008
More on spu_generateinsert
Dave posted a comment on the original post the other day seeking for help with resolving an error he was having running the script. The problem transpired to be with dealing with columns of data type image. On investigation, I discovered that there were quite a few column types which wouldn't work as they hadn't been catered for.
I've updated the script to work with all the data types I can get it to work with. For images, binary and varbinary columns, it attempts to insert NULL and puts a warning at the bottom of the generated script saying:
-- ** WARNING: There is an image column in your table which has not been migrated - this has been replaced with NULL. You will need to do this by hand. Images are not supported by this script at this time. or
-- ** WARNING: There is a binary or varbinary column in your table which has not been migrated - this has been replaced with NULL. You will need to do this by hand. Binary and VarBinary are not supported by this script at this time. as appropriate
This isn't great, but is at least informative and explains what has happened.
I will try and think about a way of scripting out data from columns of type image, binary and varbinary, but at least, in the meantime the script no longer errors (at least to my knowledge - if you know otherwise, then please let me know and I'll make more fixes).
Download updated spu_generateinsert.
I've updated the script to work with all the data types I can get it to work with. For images, binary and varbinary columns, it attempts to insert NULL and puts a warning at the bottom of the generated script saying:
-- ** WARNING: There is an image column in your table which has not been migrated - this has been replaced with NULL. You will need to do this by hand. Images are not supported by this script at this time. or
-- ** WARNING: There is a binary or varbinary column in your table which has not been migrated - this has been replaced with NULL. You will need to do this by hand. Binary and VarBinary are not supported by this script at this time. as appropriate
This isn't great, but is at least informative and explains what has happened.
I will try and think about a way of scripting out data from columns of type image, binary and varbinary, but at least, in the meantime the script no longer errors (at least to my knowledge - if you know otherwise, then please let me know and I'll make more fixes).
Download updated spu_generateinsert.
Labels: spu_generateinsert, SQL, SQLServer, SQLServer2005
// posted by Jane @ 6:32 PM
Comments:
Monday, January 07, 2008
Another update - Generate Insert statement from table for SQL Server
Bruce used my spu_generateinsert script today and identified a further issue, it didn't handle GUIDs properly. I fixed that and did a spot of further testing on one of my test databases and discovered a collation issue, so I fixed that as well whilst I was at it. The result is that it runs slower now, but specifies COLLATE database_default on each column to ensure that there are no "Cannot resolve collation conflict for column 1 in SELECT statement" errors raised.
As before, download it here and feel free to feed back any issues/improvements etc.
As before, download it here and feel free to feed back any issues/improvements etc.
Labels: spu_generateinsert, SQLServer, SQLServer2005
// posted by Jane @ 6:35 PM