I ran into this little gem today while doing some deletes and forgeting Delete syntax :))) There is now Output clause for DELETE, INSERT and UPDATE statements. It outputs the affected rows into a table variable, a simple resultset or into a table.
Read more →
Here's an interesting way of contencating values in one column. It makes use of the new XML capabilities.
use master select column_name as col1 FROM INFORMATION_SCHEMA.Columns WHERE table_name = 'spt_values' select (select column_name as col1 FROM INFORMATION_SCHEMA.
Read more →
According to Donald Farmer who visited Slovenia a few months ago importing flat files with SQL Server Integration Services is the fastest way today to do that. SSIS now has a special very fast and very optimised library that transforms flat file text into appropriate SQL Servers datatypes.
Read more →
We're all familiar with four constant columns that should be in every table. Those are: CreatedOn, CreatedBy, LastUpdatedOn, LastUpdatedBy or any other names you have for them. :)) But you get my point.
Read more →
This is an interesting thing to know. It seems that automatic update of statistics doesn't work for small tables (up to 500 rows) on SQL server 2000.
More here.
Legacy Comments
Tara
2006-05-12
re: Manual Update statistics on small tables may provide a big impact Posting the same comment from the forums:
You aren't supposed to rely on the auto update statistics anyway.
Read more →
in xp_sprintf up to 50 arguments can be specified. sometimes there's a need for more. This is it and it could as easily be rewritten as a function.
create proc spSPrintF @body varchar(8000) = '', @params varchar(1000) = '', @paramSeparator varchar(10) = ',' as begin if @params = '' begin select @body return end -- so we don't have to specially handle the last item set @params = @params + @paramSeparator declare @param varchar(200) while @params <> '' begin select @param = replace(left(@params, charindex('"' + @paramSeparator, @params)), '"', ''), @body = stuff(@body, charindex('%s', @body), 2, @param), @params = replace(@params, '"' + @param + '"' + @paramSeparator, '') end select @body end go declare @body varchar(8000), @params varchar(1000) select @body = 'This is a %s text i have to %s with some %s i want', – Parameters must be in format '"parameterValue","parameterValue2","parameterValue3"' – no spaces between parameters!
Read more →
Maybe i'm reinventing hot water but no matter. It's fun for me :))) These 2 functions round the number up or down on the decimal position we specify.
Read more →
I stumbled on an interesting thing yesterday while playing… :)) Seems that getting all data from a table is faster if we add a "phantom" where part that forces a clustered index seek instead of a scan.
Read more →
A select statement which returns all rows in nortwhwind..orders uses a clustered index scan. That's great, since it has to go through all of the data… But look at this example:
Read more →
A simple function that checks if a passed string is a GUID.
create function IsGuid ( @testString varchar(38)) returns int as begin declare @ret int select @ret = 0, @testString = replace(replace(@testString, '{', ''), '}', '') if len(isnull(@testString, '')) = 36 and @testString NOT LIKE '%[^0-9A-Fa-f-]%' and -- check for proper positions of hyphens (-) charindex('-', @testString) = 9 and charindex('-', @testString, 10) = 14 and charindex('-', @testString, 15) = 19 and charindex('-', @testString, 20) = 24 and charindex('-', @testString, 25) = 0 set @ret = 1 return @ret end go select dbo.
Read more →
this is a select trigger on a table. found this in a blog of our first Slovenian Sql Server MVP (i think he's first :))).
http://solidqualitylearning.com/blogs/dejan/archive/2004/11/25/214.aspx
quite interesting...
Legacy Comments
Tara
2005-08-08
re: a select trigger.
Read more →
This is a list of some stuff (mostly beginner) i've learned here on SQLTeam forums and i've written them down over time... i've posted it in forums here and Madhivanan gave the idea to blog it.
Read more →
There were some quetions in the forums about transposing data without the use of aggregate functions. While building a CSV list is nice sometime that's not what we want. So this is a sproc that does just that.
Read more →
in a forum i said i once did a test on isnull and coalesce speed test but i haven't documented it. so i did it again. results in articles under test .
Read more →
well there was once a debate on SqlTeam in which i claimed that IsNull is slower than Coalesce when used in same fashion:
IsNull (col1, 'somevalue') vs Coalesce(col1, 'somevalue')
so i did a little testing.
Read more →