These posts are about the new things introduced in SQL Server Denali CTP3, below is a list of the posts
A Quick look at the new IIF function in Denali CTP3
A blog about SQL Server, Books, Movies and life in general
Download SQL Server 2005 Service Pack 3 - Beta here: http://www.microsoft.com/downloads/details.aspx?FamilyId=D22317E1-BC64-4936-A14B-7A632B50A4CA&displaylang=en
To obtain SQL Server 2005 SP3 Beta for Express Edition or Express Edition with Advanced Services go here: http://www.microsoft.com/downloads/details.aspx?FamilyId=80DFF99C-CC82-4639-92BB-BBDAFF677792&displaylang=en
Microsoft SQL Server 2008 Feature Pack October 2008 is a collection of stand-alone install packages that provide additional value for SQL Server 2008. It includes the latest versions of:
AND DATEDIFF(d, '08/10/2008', DateCreated) >= 0
AND DATEDIFF(d, DateCreated, '08/15/2008') >= 0
I told him to change it to this
AND DateCreated >= '08/10/2008'
and DateCreated <= '08/15/2008'
And that solved that query. If you are interested in some more performance, I have written some Query Optimization items on the LessThanDot Wiki. Below are some direct links
Case Sensitive Search
No Functions on Left Side of Operator
Query Optimizations With Dates
Optimization: Set Nocount On
No Math In Where Clause
Don't Use (select *), but List Columns
When SET NOEXEC is ON, Microsoft® SQL Server™ compiles each batch of
Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all
batches are executed after compilation.
The execution of statements in
SQL Server consists of two phases: compilation and execution. This setting is
useful for having SQL Server validate the syntax and object names in
Transact-SQL code when executing. It is also useful for debugging statements
that would usually be part of a larger batch of statements.
The setting
of SET NOEXEC is set at execute or run time and not at parse time.
The goal is to identify all friday the thirteenths for a given timeframe. We'll use a relatively small number of years, like 10. This should make it a little easier in procedural languages.
What about this?
SELECT * FROM #blaThis question came up today and here is one way of doing it. It requires running xp_cmdshell so this is probably not such a good idea.
The problem with functions is that you cannot just insert into any table. INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
EXECUTE statements calling an extended stored procedures are allowed.
So with this in mind we know that we can call xp_cmdshell, from xp_cmdshell we can use osql
Let's take a look
We will be using tempdb
--Create the table
USE tempdb
go
CREATE TABLE LogMeNow (SomeValue varchar(50), SomeDate datetime default getdate())
go
--Here is the proc
CREATE PROC prLog
@SomeValue varchar(50)
AS
INSERT LogMeNow (SomeValue) VALUES(@SomeValue)
go
--And here is the function
CREATE FUNCTION fnBla(@id int)
RETURNS int
AS
BEGIN
DECLARE @SQL varchar(500)
SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec tempdb..prLog ''fnBla''"'
EXEC master..xp_cmdshell @SQL
RETURN @id
END
Now call the function a couple of times
SELECT dbo.fnBla(1)
SELECT dbo.fnBla(2)
SELECT dbo.fnBla(4)
SELECT * FROM LogMeNow
What if you were to run this?
SELECT dbo.fnBla(4),* FROM sys.sysobjects
See the problem? The function will be called for every row, if you have a big table this can be problematic!!!!!!!!
I tested this on SQL 2000 and on SQL 2005(including a named instance). So there you have it, this is one way. does it smell kludgy and do I feel somewhat dirty now? yes it does indeed :-(
SQL Server 2005 makes it pretty easy to do it yourself
SELECT
specific_name,*FROM information_schema.routines
WHERE
object_definition(object_id(specific_name)) LIKE '%somefuction%'AND
routine_type = 'procedure'
BTW somefuction is not a type, I already had a somefunction but was too lazy to change more than one character
I decided to interview Stéphane Faroult, the author of The Art of SQL because it is one of my favorite SQL books. Stéphane told me he has a new book coming out soon, the name of this book is Refactoring SQL Applications and the majority of this interview is focused on that book.
You can find that interview here: Interview With Stéphane Faroult About Refactoring SQL Applications