mo.notono.us

Monday, December 01, 2008

Installing Security Update for SQL Server Service Pack 2 (KB954606) takes a LOOOONG time

While I’m ranting, I might as well point out that installing the Security Update for SQL Server 2005 Service Pack 2 (KB954606) takes forever and a day, with no info in the “Overall Progress” bar.  That said – don’t try to cancel the update, the Cancel operation takes just as long.  So the longer you waited before you gave up on the update, the longer you’ll have to wait for the cancellation to finish.

Yay.

Labels: , , , , ,

Monday, September 08, 2008

CodeSmith: Yay, my code is still there...

CodeSmith's templates are now hosted on Google Code, and though the search doesn't find me (huh?  Google not being able to search a text file?), I found my old code myself...

http://code.google.com/p/codesmith/source/browse/branches/
4.x/Templates/DatabaseSchema/StoredProcedureDescriptions.cst

Since almost all my work since day 1 has been hidden behind corporate firewalls, it's nice to see some segments in the wild.

Labels: , ,

Tuesday, July 29, 2008

SQL: Once again, for the record...

Performance is not a compelling reason to choose stored procedures over dynamic SQL.

At work today I was surprised to overhear a suggestion to dynamically create stored procedures in SQL, just "to take advantage of the added performance of stored procedures" over dynamic/ad-hoc/inline SQL.

So here we go again, for the record:

Performance of Stored Proc vs. Dynamic/Ad-hoc SQL

Actual, simplistic test by BlackWasp:

image
http://www.blackwasp.co.uk/SpeedTestSqlSproc.aspx

SQL Books On Line:

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans. The database engine uses an efficient algorithm for comparing new Transact-SQL statements with the Transact-SQL statements of existing execution plans. If the database engine determines that a new Transact-SQL statement matches the Transact-SQL statement of an existing execution plan, it reuses the plan. This reduces the relative performance benefit of precompiling stored procedures by extending execution plan reuse to all SQL statements.
http://msdn2.microsoft.com/en-us/library/aa174792(SQL.80).aspx

DeKlarit dev take:

People still thinks Stored Procedures are faster, even if there is much evidence that shows otherwise. Fortunately, when they go and ask the LinQ for SQL/Entities team they get the same answer than they get from me. They are not.
http://weblogs.asp.net/aaguiar/archive/2006/06/22/Stored-Procs-vs-Dynamic-SQL.aspx

There are lots of good reasons to use Stored Procedures, and lots of good reasons to use Dynamic SQL.  Performance is rarely a good reason to chooswe one way or the other, and if performance is  the decision factor often dynamic sql comes out on top, e.g. if you can use dynamic sql to eliminate an OR for example (as when using optional query parameters in an advanced search form).

Labels: , , ,

Thursday, July 24, 2008

SQL (reminder): Executing a Stored Procedure Dynamically with SP_EXECUTESQL

I've been doing SharePoint too long if I need a reminder on how to do this...

If you, for some reason that I will not go into here, have a table cell with the name of a stored procedure, along with the parameters for said procedure, and you wish to execute that procedure, below is the general format:

Stored Procedure Name and parameter values:

MySP 1, 'a', 'foo', 'New Jersey'

Valid SQL to execute:

EXEC SP_EXECUTESQL 'EXEC MySP 1, ''a'', ''foo'', ''New Jersey'''

SP_EXECUTESQL does not allow inline string manipulation (such as prepending the EXEC statement or escaping the single quotes), so that manipulation has to be done before executing the string.

Thanks to Andre for the reminder.

Labels:

Monday, February 11, 2008

SQL: Simple Search of Stored Procedure Code

Once upon a time I used to send out a "Tip of the Day" to my esteemed colleagues.  Here's a piece of code I used a lot prior to creation of my Data Dictionary system function (prior to me starting SharePoint development, which has pretty much precluded any SQL work).

 

Date: Tue, 22 Nov 2005 18:47:41 -0400
Subject: Tip of the Day: How to locate a word in sql

Say you need to find where a table field named 'InitSalesID' is referenced in your stored procedures and functions.

Easy, use my FindText query:

DECLARE @query varchar(100)
--Change this
SET @query = 'InitSalesID'
--End changes
 
--Leave this alone
SELECT DISTINCT name, type
FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id
WHERE text LIKE '%' + @query + '%'
ORDER BY name

But what if the database also contains a field called ‘DefInitSalesID’? The above query would also return those references.  How do you limit the results to those only referencing the ‘InitSalesID’ field?

You can’t simply preface the text in the query with a space, since you may have code like ‘bu.InitSalesID’, or the field name may be at the beginning of a line, etc.

The solution is this:

DECLARE @query varchar(100)
--Change this
SET @query = 'InitSalesID'
--End changes
 
--Leave this alone
SELECT DISTINCT name, type
FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id
WHERE text LIKE '%[^a-z]' + @query + '[^a-z]%'
ORDER BY name

By adding the [^a-z] wildcards to the like statement, you limit the search to entries that do NOT have an alpha character immediately before or after the query text.

Labels: ,

Monday, October 08, 2007

Announcing squrl.us

Squrl.us (Short, Quick URLs) is a side project I've been putting together over some weekends and weeknights lately.  Essentially it is a tinyurl clone, but with added functionality, and a cuter look.

It's all really quite simple:  You enter a long and unruly url, press the Scurry button, and in return get a number of short, quick urls that are easy to remember, jot down, or communicate verbally.

For example, the url http://www.spiegel.de/panorama/leute/0,1518,510187,00.html is converted to

The squrl is just a redirection to the original link, the mobile link uses Skweezer.net to render a mobile-friendly version, the cache link goes to the Google cache for the url, and the translated link is to Google's automatic translation service (note this only works for non-English pages).

The whole thing is running on ASP.NET 3.5 with C# 3.0 and Linq (for the heck of it), and SQL Server Express 2005 (this may be migrated to MySQL 5.0 due to server constraints).

Go try it out!

Labels: , , , , ,

Wednesday, July 11, 2007

SQL: Efficiently Get Table Row Count

A quick and efficient way to get the row count of a table is to query the system table sysindexes

In order to get the rows of a single table called MyTable you can use the following SQL:

SELECT rows FROM sysindexes WHERE indid < 2 AND ID = OBJECT_ID('MyTable')

In order to get the rows of all tables, you can use this:

SELECT so.name, si.rows
FROM sysindexes si 
       INNER JOIN sysobjects so on si.id = so.id
WHERE si.indid < 2 
       AND so.xtype = 'U'
ORDER BY so.name

Labels: ,

SQL: Compare values or rows

In SQL, if you need to check if two values (or rows) are different, the BINARY_CHECKSUM function is very useful and fast:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_1mi1.asp

Labels: ,

SQL: Does the object exist?

If you need to check if an object exists in the database, rather than querying an obscure system table, you can use the OBJECT_ID function.

If you want to verify that the table FooBar exists in the YadiYada database, simply write the following IF statement:

IF OBJECT_ID('YadiYada..FooBar') IS NOT NULL
BEGIN
--FooBar exists so you can use it here
--...
END

Labels: ,

Tuesday, May 08, 2007

MOSS: SQL LIKE = CAML Contains

Note to self and anyone else searching for this: 

The "LIKE" operator in CAML is Contains.

Sadly the documentation on CAML is pathetic.

Labels: , ,

Tuesday, July 25, 2006

A Fix() Function in T-SQL

Rob Farley did a nice writeup on how to create A Fix() Function in T-SQL, using TDD concepts to boot.

Final code:

create function dbo.fix(@num float, @digits int) returns float as
begin
  declare @res float
  select @res = case 
    when @num > 0 
      then round(@num,@digits-1-floor(log10(@num))) 
      else round(@num,@digits-1-floor(log10(-@num))) 
    end
  return (@res)
end

Technorati Tags: , ,

Labels:

Tuesday, May 30, 2006

(Paul Wilson's) Truth about Stored Procs and O/R Mapping

Paul Wilson has some good points about Stored Procs and O/R mappers in The Truth about Stored Procs and O/R Mapping Technorati Tags: , , ,

Labels:

Friday, May 26, 2006

Another Pro-Con SQL Stored Procedures Debate

In this corner: Eric Wise, pro stored procs.
In the other corner: Jeremy D. Miller, con stored procs, pro O/R.

Eric: The Pragmatic Adhoc SQL vs Stored Procedures Discussion (I got in the first comment...)
Jeremy: Why I do not use Stored Procedures
Eric: In Response To No Stored Procedures
Jeremy: Grab bag of follow up's for data access, persistence, query engines, and o/r mapping

UPDATE: Frans Bouma's VERY SIMILAR post. Of course his predates mine by 7 hrs, and is far more inclusive.

Technorati Tags: , , , ,

Labels:

Monday, April 03, 2006

SQL: Corrected IsSmallDate function

Mounir BEN HAMED was kind enough to alert me of a blatant, brain-dead bug in my IsSmallDate SQL function.

So, below is the corrected code, also to be found at Code Snippets

--Checks if a string is a valid smalldatetime
--Updated 04/03/06 by Oskar Austegard after bug find by Mounir Ben Hamed
CREATE FUNCTION dbo.IsSmallDate
(
  @SmallDateString varchar(20) --The input string to check
)
RETURNS BIT
AS
BEGIN
  DECLARE @Result bit
  SET @SmallDateString = LTRIM(RTRIM(@SmallDateString))

  IF ISDATE(@SmallDateString) = 1
   AND CONVERT(datetime, @SmallDateString) BETWEEN '1900-01-01' AND '2079-06-06'
    SET @Result = 1
  ELSE
    SET @Result = 0

  RETURN @Result
END

Tags:

Labels:

Wednesday, December 14, 2005

SQL: Bug in UDFs using COALESCE with SELECT and Multiple RETURNs?

I encountered a strange bug in some sql code I was refactoring this morning, when I replaced a number of IF .. ELSE statements with a simple COALESCE. The essence of the buggy code was as follows:

USE Northwind
GO

CREATE FUNCTION dbo.Foo (@EmployeeID int)
RETURNS nvarchar(20)
AS
BEGIN
 --Declare a variable that really shouldn't be used
 DECLARE @EmployeeName nvarchar(20)
 SET @EmployeeName = 'Not expected'
 RETURN COALESCE
  (
   (SELECT e.LastName FROM dbo.Employees e 
    WHERE e.EmployeeID = @EmployeeID), 
   '(Not Found)'
  )
 --The function SHOULD have exited by now...
 RETURN @EmployeeName
END

GO
--Both of these return unexpected results
SELECT dbo.Foo(1)          --Returns 'Not expected'
SELECT dbo.Foo(564654)  --Returns 'Not expected'
As can be seen from the code - I would have expected the first RETURN to exit the function. Instead what is actually returned is the contents of the @EmployeeName.

This appears to be a bug in how SQL handles the combination UDF, multiple RETURNs and a SELECT statement within a COALESCE.

(For the record, the following version of the function works fine:)

USE Northwind
GO

ALTER FUNCTION dbo.Foo (@EmployeeID int)
RETURNS nvarchar(20)
AS
BEGIN
 --Declare a variable that really shouldn't be used
 DECLARE @EmployeeName nvarchar(20)
 SET @EmployeeName = 'Not expected'
 RETURN COALESCE
  (
   (SELECT e.LastName FROM dbo.Employees e 
    WHERE e.EmployeeID = @EmployeeID), 
   '(Not Found)'
  )
 --The function SHOULD have exited by now...
 --RETURN @EmployeeName  <--SECOND RETURN COMMENTED OUT
END

GO
--These now return the expected results
SELECT dbo.Foo(1)           --Returns 'Davolio'
SELECT dbo.Foo(564654)   --Returns '(Not Found)'

Labels:

Tuesday, November 22, 2005

SQL: EMS SQLManager 2.1 Lite Review

I've been using EMS SQL Manager for SQL Server (v 2.1 Lite) with varying success for a few weeks now. It's a great tool for an unbeatable price, but my conclusion so far is that EMS doesn't quite get it. Or maybe I just don't get EMS:

For instance - there are (at least) 3 types of editors. Why 3? They're all slightly different:

The Script Editor:

  • it comes with a handy left-side Script Explorer, allowing quick access to code within a large script file,
  • it'll show output from stuff like PRINT 'foo' in a separate result pannel,
  • but it doesn't give you a result set for, say , SELECT * FROM Foo - it just tells you in a message box that the query executed (unless you check the box that says "Don't show this message", in which case the message is displayed only in the status bar).
  • It has no editing toolbar/navbar.
The SQL Editor:
  • it allows you to execute statements like SELECT * FROM Foo or INSERT INTO FOO SELECT * FROM Bar,
  • but does NOT support statements like PRINT 'foo' - it simply ignores them.
  • Unlike the other editors, the SQL Editor allows "sub-tabs"; additional queries within the same editor.
  • It has an Editing toolbar/navbar, listing Find, Load, Save and Save All. A Replace feature IS available in the editor, but only from the context menu.
The 'Procedure/Function' Editor:
  • This allows you to edit stored procs and UDFs
  • It has a tab each for the Procedure/Function, Parameters, Dependencies, Description and DDL.
  • As far as I can tell the only difference between the Procedure/Function and the DDL tabs is that the DDL tab is read-only.
  • There is no edit toolbar/navbar
  • There is an Object selector, which acts like a navigation tool to open a different stored proc/view. If you have made changes in the procedure and then select another, you're first asked "xxx has been changed. Do you wish to save it right now?" with the options Yes, No and Cancel. If you select No, you're asked again Discard changes? with a Yes and No option. -Superfluously superfluous.
My question is still (and I've asked EMS this) Why 3 separate editors? Why couldn't one editor suffice, as it does in MS Query Analyzer, MS Visual Studio, IMCEDA Speed SQL IDE, and Quest Toad?

Other oddities/annoyances:

  • No central Edit menu. Yes, an Editor without an Edit menu. The user is left to discover on his/her own features like:
    - Ctrl+Shift+I (indent (but only if the text is selected),
    - Ctrl+Shift+U (unindent),
    - Ctrl+Shift+L ("line-select" mode)
    - Ctrl+Shift+C ("column-select" mode)
    - Ctrl+Shift+N ("normal select" mode)
    - Ctrl+F (find - arguably this IS listed in the context menu, and there's a tool menu item in the SQL editor)
    - Ctrl+R (find/replace - this too is listed in the context menu, but nowhere else)
    - and a whole slew of other common Edit functions that I have yet to stumble across
  • There's a separate Config screen for the Editor Options (different from the Editor Options within the Environment Options config)
  • There's no intellisense for parameters (@foo).
  • There is no public disclosure of requests, feedback, or bugs, a la that of JetBrains. This means that every user operates in a vacuum between EMS and the user, with no ability to learn more about the product from other users. I can only imagine that this means the support personnel at EMS therefore has to field the same complaints/questions over and over.
  • There seems to have been a lack of testing before shipping - I keep encountering bugs that shouldn't be that hard to reproduce (like actually using the Save Settings feature).

So why do I use the tool, if it sucks this bad? Well, it doesn't really.

I have 5 reasons, primarily, for why I use EMS SQL Manager Lite:

  • Intellisense. Why MS doesn't have intellisense in their SQL tools is beyond me. EMS' intellisense feature works well (with the exception of the parameters).
  • Keyboard templates. These work similar to Live Templates in ReSharper. Essentially they're SQL snippets that you can insert by typing a specific text.
  • Database object hyperlinks. Within a stored procedure it is easy (Ctrl+Click) to navigate to the definition of any object used in the code.
  • It's free! You get a lot for nothing, which makes it that much easier to make it the standard tool for SQL editing throughout your organization.
  • Save Settings. Saving your settings and sharing the resulting registry files with other developers on your project can make it simpler to reuse and standardize code.

There are other reasons as well (tabs in DB Explorer, automatic updates, choice of MDI or windows, etc, etc), but this is getting too long already...

Labels: , ,

Wednesday, November 16, 2005

SQL: Updated IsReallyInteger UDF

I screwed up the first implementation of my IsReallyInteger user-defined SQL function - I'm not sure how it escaped notice until today. Anyway, I updated my original post with code that works. See http://austegard.blogspot.com/2005/10/sql-isreallyinteger.html.

Labels:

Thursday, October 20, 2005

SQL: IsReallyInteger

There are a number of problems with the ISNUMERIC function in TSQL, some of which were causing us grief recently. So I went looking for a solution, and found a UDF at aspfaq. What I really needed though, was a check for integers, so I concocted one as follows:

----------------------------------------------------------------------------
--Purpose: Checks that the input string is really an integer of the specified type.  
-- To be used in place of the ISNUMERIC function, as it can't be trusted.
-- See http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10194
--Inspired by: http://www.aspfaq.com/show.asp?id=2390 
--Created: 10/20/05 by Oskar Austegard.
--Updated: 11/16/05 by Oskar Austegard - fixed a rather serious bug...
----------------------------------------------------------------------------
ALTER FUNCTION dbo.IsReallyInteger
(  
  @Num varchar(64), --Input string to be checked
  @Type varchar(8) --Type of integer: bigint, int, smallint, tinyint
)  
RETURNS BIT  
BEGIN  
  --Get the absolute value of the number by removing a leading - or +
  DECLARE @AbsNum varchar(64)
  SET @AbsNum = CASE WHEN LEFT(@Num, 1) IN ('-', '+') THEN SUBSTRING(@Num, 2, LEN(@Num)) ELSE @Num END

  --Remove leading zeros
  WHILE LEN(@AbsNum) > 1 AND LEFT(@AbsNum, 1) = '0'
    SET @AbsNum = SUBSTRING(@AbsNum, 2, LEN(@AbsNum))

  SET @Length = LEN(@AbsNum) 
  --Reinsert the - in negative numbers
  SET @Num = CASE WHEN LEFT(@Num, 1) = '-' THEN '-' + @AbsNum ELSE @AbsNum END

  --Check for empty string or non-digits
  IF @AbsNum = '' OR PATINDEX('%[^0-9]%', @AbsNum) > 0
    RETURN 0

  --Check limits by type
  IF (@Type = 'bigint' AND (@Length < 19 OR (@Length = 19 AND (@AbsNum < '9223372036854775807' OR @Num = '-9223372036854775808'))))
    OR (@Type = 'int' AND (@Length < 10 OR (@Length = 10 AND (@AbsNum < '2147483648' OR @Num = '-2147483648'))))
    OR (@Type = 'smallint' AND (@Length < 5 OR (@Length = 5 AND (@AbsNum < '32768' OR @Num = '-32768'))))
    OR (@Type = 'tinyint' AND LEFT(@Num, 1) <> '-' AND (@Length < 3 OR (@Length = 3 AND @AbsNum < '256')))
    RETURN 1 --Success
  --Else
  RETURN 0 --Failure
END  

I then wrap this function in another for the most common usage:

ALTER FUNCTION dbo.IsReallyInt
(  
 @Num varchar(64) --Input to be checked
)  
RETURNS BIT  
BEGIN  
 RETURN dbo.IsReallyInteger(@Num, 'int')
END  
Tags:

Labels:

Thursday, September 29, 2005

SQL: fnAddToList() and fnAddVarToList()

Two functions that come in handy when building string lists from table values. The equivalent can also be done inline in the query, of course, for a slight boost in performance:

UPDATED 09/29/05: The inputs should check for empty string values and convert those to null:
--Adds an element (nvarchar) to the end of a list (nvarchar), after first inserting a delimiter (nvarchar)
--Updated 06/21/02 by Oskar Austegard
--Updated 09/02/05 by Oskar Austegard to use single COALESCE function
--Updated 09/29/05 by Oskar Austegard - Added empty string check
ALTER FUNCTION dbo.fnAddToList (@List nvarchar(4000), @New nvarchar(4000), @Del nvarchar(10))
RETURNS nvarchar(4000)
AS 
BEGIN
 --Treat ''s as NULLs
 SELECT @List = NULLIF(@List, ''), @Del = NULLIF(@Del, ''), @New = NULLIF(@New, '')
 --First try the concatened string, if null then just the list, 
  --if it too is null, just the new element
 RETURN COALESCE(@List + @Del + @New, @List, @New)
END
--Adds a sql_variant element to the end of a sql_variant list, after first inserting a delimiter (nvarchar)
--If both element and list are nvarchars, using dbo.fnAddToList will be faster
--Created 07/13/05 by Oskar Austegard
--Updated 09/02/05 by Oskar Austegard - implemented COALESCE based approach to building list
--Updated 09/29/05 by Oskar Austegard - Added empty string check
ALTER FUNCTION dbo.fnAddVarToList (@VarList sql_variant, @VarNew sql_variant, @Del nvarchar(10))
RETURNS nvarchar(4000)
AS 
BEGIN
 DECLARE @List nvarchar(4000), @New nvarchar(4000)
 SELECT @List = NULLIF(CONVERT(nvarchar(4000), @VarList), ''),
   @New = NULLIF(CONVERT(nvarchar(4000), @VarNew), '')
 --First try the concatened string, if null then just the list, 
  --if it too is null, just the new element
 RETURN COALESCE(@List + @Del + @New, @List, @New)
END

Labels:

Wednesday, September 21, 2005

SQL: Updated IsSmallDate function

Due to a number of unexpected features of the ISNUMERIC function in SQL, I rewrote my IsSmallDate function to not use the function:

--Checks if a string is a valid smalldatetime
ALTER FUNCTION dbo.IsSmallDate
(
  @SmallDateString varchar(20) --The input string to check
)
RETURNS BIT
AS
BEGIN
  DECLARE @Result bit
  SET @SmallDateString = LTRIM(RTRIM(@SmallDateString))
  --Check for numbers only, with a specific pattern
  IF @SmallDateString LIKE '[12][90][0-9][0-9]'
  BEGIN
    IF CAST(@SmallDateString AS int) BETWEEN 1900 AND 2079 
      SET @Result = 1
    ELSE
      SET @Result = 0
    END
  ELSE 
    SET @Result = ISDATE(@SmallDateString)
  RETURN @Result
END

Labels: