David Troy the person who brought you twittervision has done it again this time with flickr. Check out flickrvision
Who needs animated desktops anymore? Be warned it can get very addictive.
Tuesday, May 15, 2007
Monday, May 14, 2007
Google: A Girl's Best Friend
Type these searches into a Google search box
she created
she invented
she discovered
she debugged
Look at what Google suggests.
she created
she invented
she discovered
she debugged
Look at what Google suggests.
Make Your Case Sensitive Searches 1000 Times Faster
I had an case sensitive update query that ran in about 9 minutes. Since it was a case sensitive query it did an index scan not an index seek.
Once I modified my WHERE clause the update took a little less than 3 seconds
Let's get started and see what I did
First create this table
CREATE TABLE #CaseSensitiveSearchTemp (Val CHAR(1))
INSERT #CaseSensitiveSearchTemp VALUES('A')
INSERT #CaseSensitiveSearchTemp VALUES('B')
INSERT #CaseSensitiveSearchTemp VALUES('C')
INSERT #CaseSensitiveSearchTemp VALUES('D')
INSERT #CaseSensitiveSearchTemp VALUES('E')
INSERT #CaseSensitiveSearchTemp VALUES('F')
INSERT #CaseSensitiveSearchTemp VALUES('G')
INSERT #CaseSensitiveSearchTemp VALUES('H')
Now we will insert some lowercase characters
INSERT #CaseSensitiveSearchTemp
SELECT LOWER(Val) FROM #CaseSensitiveSearchTemp
Now we will create our real table which will have 65536 rows
CREATE TABLE CaseSensitiveSearch (Val VARCHAR(50))
We will do a couple of cross joins to generate the data for our queries
INSERT CaseSensitiveSearch
SELECT t1.val + t2.val + t3.val + t4.val
FROM #CaseSensitiveSearchTemp t1
CROSS JOIN #CaseSensitiveSearchTemp t2
CROSS JOIN #CaseSensitiveSearchTemp t3
CROSS JOIN #CaseSensitiveSearchTemp t4
This should give you 65536 rows
SELECT * FROM CaseSensitiveSearch
Create an index on the table
CREATE INDEX IX_SearchVal ON CaseSensitiveSearch(Val)
This is how you do a case sensitive search
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
Now hit CRLK + K (SQL Server 2000) or CRLK + M(SQL Server 2005)
run these 2 queries in one batch by highlighting them both and hitting F5
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
AND Val LIKE 'ABCD'
Look at the execution plan, I get 98.71% for the first query and 1.29% for the second query. Just by adding the AND condition SQL server is able to do an index seek and run the query many times faster
Now try it with a lowercase a
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS
AND Val LIKE 'aBCD'
You see it all works without a problem, the correct result is returned
Once I modified my WHERE clause the update took a little less than 3 seconds
Let's get started and see what I did
First create this table
CREATE TABLE #CaseSensitiveSearchTemp (Val CHAR(1))
INSERT #CaseSensitiveSearchTemp VALUES('A')
INSERT #CaseSensitiveSearchTemp VALUES('B')
INSERT #CaseSensitiveSearchTemp VALUES('C')
INSERT #CaseSensitiveSearchTemp VALUES('D')
INSERT #CaseSensitiveSearchTemp VALUES('E')
INSERT #CaseSensitiveSearchTemp VALUES('F')
INSERT #CaseSensitiveSearchTemp VALUES('G')
INSERT #CaseSensitiveSearchTemp VALUES('H')
Now we will insert some lowercase characters
INSERT #CaseSensitiveSearchTemp
SELECT LOWER(Val) FROM #CaseSensitiveSearchTemp
Now we will create our real table which will have 65536 rows
CREATE TABLE CaseSensitiveSearch (Val VARCHAR(50))
We will do a couple of cross joins to generate the data for our queries
INSERT CaseSensitiveSearch
SELECT t1.val + t2.val + t3.val + t4.val
FROM #CaseSensitiveSearchTemp t1
CROSS JOIN #CaseSensitiveSearchTemp t2
CROSS JOIN #CaseSensitiveSearchTemp t3
CROSS JOIN #CaseSensitiveSearchTemp t4
This should give you 65536 rows
SELECT * FROM CaseSensitiveSearch
Create an index on the table
CREATE INDEX IX_SearchVal ON CaseSensitiveSearch(Val)
This is how you do a case sensitive search
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
Now hit CRLK + K (SQL Server 2000) or CRLK + M(SQL Server 2005)
run these 2 queries in one batch by highlighting them both and hitting F5
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
AND Val LIKE 'ABCD'
Look at the execution plan, I get 98.71% for the first query and 1.29% for the second query. Just by adding the AND condition SQL server is able to do an index seek and run the query many times faster
Now try it with a lowercase a
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS
AND Val LIKE 'aBCD'
You see it all works without a problem, the correct result is returned
Sunday, May 13, 2007
Great Programming Quote
"pasting code from the internet into production code is like chewing gum found in the street."
None of use pasted straight into production code from a newsgroup/forum right? <g>
None of use pasted straight into production code from a newsgroup/forum right? <g>
Thursday, May 10, 2007
Why Does OBJECTPROPERTY Have A TableIsFake Property?
You can run the following function (OBJECTPROPERTY(object_id, N'TableIsFake') ) on an object and it will return 1 if the table is fake and 0 otherwise.
What does BOL say? The table is not real. It is materialized internally on demand by SQL Server. So does this mean it is a table valued function?
Well one way to find out.
Run this
USE AdventureWorks;
GO
SELECT name, object_id, type_desc,OBJECT_DEFINITION(object_id) as object_definition
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') = 1
ORDER BY type_desc, name;
GO
And we get back a table valued function in the resultset
name: ufnGetContactInformation
object_id: 439672614
type_desc: SQL_TABLE_VALUED_FUNCTION
object_definition; CREATE FUNCTION .... (I truncated the rest)
Now create another function
CREATE FUNCTION [dbo].[ufnGetSomeTable]()
RETURNS @SomeTable TABLE (
[ContactID] int PRIMARY KEY NOT NULL)
BEGIN
INSERT @SomeTable VALUES(1)
INSERT @SomeTable VALUES(2)
RETURN
END
GO
Run this to make sure it works
SELECT * FROM ufnGetSomeTable()
run the same query again
USE AdventureWorks;
GO
SELECT name, object_id, type_desc,OBJECT_DEFINITION(object_id) as object_definition
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') = 1
ORDER BY type_desc, name;
GO
And yes we get 2 functions back ;-)
Now we will create some temp tables to see if those are fake
CREATE TABLE #testFakeTable1234 (id int)
CREATE TABLE ##testFakeTable1234 (id int)
Now run this and you will see that those are real and not fake
USE tempdb;
GO
SELECT name, object_id, type_desc,OBJECTPROPERTY(object_id, N'TableIsFake') as IsFake
FROM sys.objects
WHERE name LIKE '#testFakeTable1234%'
OR name LIKE '##testFakeTable1234%'
ORDER BY type_desc, name;
GO
DROP TABLE #testFakeTable1234,##testFakeTable1234
Now create this function
USE AdventureWorks;
GO
CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1) ,
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L0)
SELECT n FROM Nums WHERE n <= @n;
Test it out to make sure it works
SELECT * FROM dbo.fn_nums(2)
Run the following 2 queries
USE AdventureWorks;
GO
SELECT name, object_id, type_desc,OBJECTPROPERTY(object_id, N'TableIsFake') IsFake, OBJECTPROPERTY(object_id, N'IsTableFunction') IsTableFunction
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') <> OBJECTPROPERTY(object_id, N'IsTableFunction')
ORDER BY type_desc, name;
USE AdventureWorks;
GO
SELECT name, object_id, type_desc,OBJECTPROPERTY(object_id, N'TableIsFake') IsFake,
OBJECTPROPERTY(object_id, N'IsInlineFunction') IsInlineFunction
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') <> OBJECTPROPERTY(object_id, N'IsInlineFunction')
ORDER BY type_desc, name;
So a SQL_table valued function is a fake table but a SQL inline table valued function is not a fake table????
So there you have it, confused? Good!
What does BOL say? The table is not real. It is materialized internally on demand by SQL Server. So does this mean it is a table valued function?
Well one way to find out.
Run this
USE AdventureWorks;
GO
SELECT name, object_id, type_desc,OBJECT_DEFINITION(object_id) as object_definition
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') = 1
ORDER BY type_desc, name;
GO
And we get back a table valued function in the resultset
name: ufnGetContactInformation
object_id: 439672614
type_desc: SQL_TABLE_VALUED_FUNCTION
object_definition; CREATE FUNCTION .... (I truncated the rest)
Now create another function
CREATE FUNCTION [dbo].[ufnGetSomeTable]()
RETURNS @SomeTable TABLE (
[ContactID] int PRIMARY KEY NOT NULL)
BEGIN
INSERT @SomeTable VALUES(1)
INSERT @SomeTable VALUES(2)
RETURN
END
GO
Run this to make sure it works
SELECT * FROM ufnGetSomeTable()
run the same query again
USE AdventureWorks;
GO
SELECT name, object_id, type_desc,OBJECT_DEFINITION(object_id) as object_definition
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') = 1
ORDER BY type_desc, name;
GO
And yes we get 2 functions back ;-)
Now we will create some temp tables to see if those are fake
CREATE TABLE #testFakeTable1234 (id int)
CREATE TABLE ##testFakeTable1234 (id int)
Now run this and you will see that those are real and not fake
USE tempdb;
GO
SELECT name, object_id, type_desc,OBJECTPROPERTY(object_id, N'TableIsFake') as IsFake
FROM sys.objects
WHERE name LIKE '#testFakeTable1234%'
OR name LIKE '##testFakeTable1234%'
ORDER BY type_desc, name;
GO
DROP TABLE #testFakeTable1234,##testFakeTable1234
Now create this function
USE AdventureWorks;
GO
CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1) ,
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L0)
SELECT n FROM Nums WHERE n <= @n;
Test it out to make sure it works
SELECT * FROM dbo.fn_nums(2)
Run the following 2 queries
USE AdventureWorks;
GO
SELECT name, object_id, type_desc,OBJECTPROPERTY(object_id, N'TableIsFake') IsFake, OBJECTPROPERTY(object_id, N'IsTableFunction') IsTableFunction
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') <> OBJECTPROPERTY(object_id, N'IsTableFunction')
ORDER BY type_desc, name;
USE AdventureWorks;
GO
SELECT name, object_id, type_desc,OBJECTPROPERTY(object_id, N'TableIsFake') IsFake,
OBJECTPROPERTY(object_id, N'IsInlineFunction') IsInlineFunction
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') <> OBJECTPROPERTY(object_id, N'IsInlineFunction')
ORDER BY type_desc, name;
So a SQL_table valued function is a fake table but a SQL inline table valued function is not a fake table????
So there you have it, confused? Good!
Wednesday, May 09, 2007
Some Katmai (SQL Server 2008?) News
Microsoft issued a Katmai press release, you can read the full press release here: http://www.microsoft.com/presspass/press/2007/may07/05-09KatmaiPR.mspx?rss_fdn=Press%20Releases
Here are the important parts:
Scheduled to be available in 2008, SQL Server “Katmai” will deliver on Microsoft’s overall data platform vision to meet the needs of the coming data explosion and the next generation of data-driven applications.
Data Platform to Power Business Insights Regardless of Size or Budget
SQL Server “Katmai” is designed to do the following:
• Provide a scalable and reliable platform with advanced security technology for even the most demanding applications
• Reduce the time and cost of managing data infrastructure with innovative policy-based management
Redefining Pervasive Insight
SQL Server “Katmai” brings powerful BI capabilities and valuable data even closer to every user:
• Empowers users to easily consume information due to increased integration with front-end tools in the 2007 Microsoft Office system, including Office Excel® 2007, Excel Services, Office SharePoint® Server and Office PerformancePoint Server 2007
• Provides reports of any size or complexity internally within organizations and externally to partners and suppliers
• Aligns corporate decision-making by collaborating on key analysis and reports within Microsoft Office SharePoint Server
• Integrates all relevant data within the enterprise into a scalable and comprehensive data warehouse platform
Dynamic Development for Data Management Solutions
SQL Server “Katmai” offers these features for developers:
• Provides an integrated development environment with Microsoft Visual Studio® and .NET Framework that will accelerate development of new applications with a higher level of data abstraction
• Enables developers to synchronize data from virtually any device to the central data store
Beyond Relational Data
SQL Server “Katmai” enables users to do the following:
• Manage any type of data including relational data, documents, geographic information and XML
• Work with a consistent set of services and tools
Availability
SQL Server “Katmai” is scheduled to be delivered in 2008. More information is available at http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx
Download the Katmai data sheet here: http://download.microsoft.com/download/B/F/2/BF24C54E-5635-4C79-AFB4-0C3F840E79F4/Katmai_datasheet_Final.pdf
Here are the important parts:
Scheduled to be available in 2008, SQL Server “Katmai” will deliver on Microsoft’s overall data platform vision to meet the needs of the coming data explosion and the next generation of data-driven applications.
Data Platform to Power Business Insights Regardless of Size or Budget
SQL Server “Katmai” is designed to do the following:
• Provide a scalable and reliable platform with advanced security technology for even the most demanding applications
• Reduce the time and cost of managing data infrastructure with innovative policy-based management
Redefining Pervasive Insight
SQL Server “Katmai” brings powerful BI capabilities and valuable data even closer to every user:
• Empowers users to easily consume information due to increased integration with front-end tools in the 2007 Microsoft Office system, including Office Excel® 2007, Excel Services, Office SharePoint® Server and Office PerformancePoint Server 2007
• Provides reports of any size or complexity internally within organizations and externally to partners and suppliers
• Aligns corporate decision-making by collaborating on key analysis and reports within Microsoft Office SharePoint Server
• Integrates all relevant data within the enterprise into a scalable and comprehensive data warehouse platform
Dynamic Development for Data Management Solutions
SQL Server “Katmai” offers these features for developers:
• Provides an integrated development environment with Microsoft Visual Studio® and .NET Framework that will accelerate development of new applications with a higher level of data abstraction
• Enables developers to synchronize data from virtually any device to the central data store
Beyond Relational Data
SQL Server “Katmai” enables users to do the following:
• Manage any type of data including relational data, documents, geographic information and XML
• Work with a consistent set of services and tools
Availability
SQL Server “Katmai” is scheduled to be delivered in 2008. More information is available at http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx
Download the Katmai data sheet here: http://download.microsoft.com/download/B/F/2/BF24C54E-5635-4C79-AFB4-0C3F840E79F4/Katmai_datasheet_Final.pdf
Monday, May 07, 2007
Three Ways To Return All Rows That Contain Uppercase Characters Only
How do you select all the rows that contain uppercase characters only? There sre three ways to do this
1 Compare with BINARY_CHECKSUM
2 Use COLLATE
3 Cast to varbinary
Let's first create the table and also some test data
CREATE TABLE #tmp ( x VARCHAR(10) NOT NULL )
INSERT INTO #tmp
SELECT 'Word' UNION ALL
SELECT 'WORD' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'AbC' UNION ALL
SELECT 'ZxZ' UNION ALL
SELECT 'ZZZ' UNION ALL
SELECT 'word'
if we want only the uppercase columns then this is supposed to be our output
WORD
ABC
ZZZ
Let's get started, first up is BINARY_CHECKSUM
SELECT x
FROM #TMP
WHERE BINARY_CHECKSUM(x) = BINARY_CHECKSUM(UPPER(x))
Second is COLLATE
SELECT x
FROM #TMP
WHERE x = UPPER(x) COLLATE SQL_Latin1_General_CP1_CS_AS
Third is Cast to varbinary
SELECT x
FROM #TMP
WHERE CAST(x AS VARBINARY(10)) = CAST(UPPER(x) AS VARBINARY(10))
Of course if you database is already case sensitive you can just do the following
SELECT x
FROM #TMP
WHERE UPPER(x) = x
That will work, how do you find out what collation was used when your database was created? You can use DATABASEPROPERTYEX for that. I use the model DB here because when you create a new DB by default it inherits all the properties from the model DB.
When I run this
SELECT DATABASEPROPERTYEX( 'model' , 'collation' )
I get this as output: SQL_Latin1_General_CP1_CI_AS
What does all that junk mean? Well let's run the following function (yes those are 2 colons ::)
SELECT *
FROM ::fn_helpcollations ()
WHERE NAME ='SQL_Latin1_General_CP1_CI_AS'
The description column contains this info
Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data
You can read some more info about Selecting a SQL Collation here: http://msdn2.microsoft.com/en-us/library/aa176552(SQL.80).aspx
1 Compare with BINARY_CHECKSUM
2 Use COLLATE
3 Cast to varbinary
Let's first create the table and also some test data
CREATE TABLE #tmp ( x VARCHAR(10) NOT NULL )
INSERT INTO #tmp
SELECT 'Word' UNION ALL
SELECT 'WORD' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'AbC' UNION ALL
SELECT 'ZxZ' UNION ALL
SELECT 'ZZZ' UNION ALL
SELECT 'word'
if we want only the uppercase columns then this is supposed to be our output
WORD
ABC
ZZZ
Let's get started, first up is BINARY_CHECKSUM
SELECT x
FROM #TMP
WHERE BINARY_CHECKSUM(x) = BINARY_CHECKSUM(UPPER(x))
Second is COLLATE
SELECT x
FROM #TMP
WHERE x = UPPER(x) COLLATE SQL_Latin1_General_CP1_CS_AS
Third is Cast to varbinary
SELECT x
FROM #TMP
WHERE CAST(x AS VARBINARY(10)) = CAST(UPPER(x) AS VARBINARY(10))
Of course if you database is already case sensitive you can just do the following
SELECT x
FROM #TMP
WHERE UPPER(x) = x
That will work, how do you find out what collation was used when your database was created? You can use DATABASEPROPERTYEX for that. I use the model DB here because when you create a new DB by default it inherits all the properties from the model DB.
When I run this
SELECT DATABASEPROPERTYEX( 'model' , 'collation' )
I get this as output: SQL_Latin1_General_CP1_CI_AS
What does all that junk mean? Well let's run the following function (yes those are 2 colons ::)
SELECT *
FROM ::fn_helpcollations ()
WHERE NAME ='SQL_Latin1_General_CP1_CI_AS'
The description column contains this info
Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data
You can read some more info about Selecting a SQL Collation here: http://msdn2.microsoft.com/en-us/library/aa176552(SQL.80).aspx
Public CTP of Visual Studio Team Edition for Database Professionals first Service Release Available For Download
What is new?
3+4 part name resolution through a new feature called "database references"
File groups and files are now part of the project settings and can be fully parameterize then to fit your deployment needs (this replaces the need for the storage deployment script)
SQLCMD variable support; this is enabled through a new project property page for creating and editing variables that you can then use inside your scripts.
Variables are stored inside the project file and can be mapped to MSBuild properties.
Read more details and get the download from the Data Dude blog
3+4 part name resolution through a new feature called "database references"
File groups and files are now part of the project settings and can be fully parameterize then to fit your deployment needs (this replaces the need for the storage deployment script)
SQLCMD variable support; this is enabled through a new project property page for creating and editing variables that you can then use inside your scripts.
Variables are stored inside the project file and can be mapped to MSBuild properties.
Read more details and get the download from the Data Dude blog
Thursday, May 03, 2007
How Do Spammers/Phishers Get People To Click On A Link With An Email Like This?
Here is an email I received from 'Bank Of America'
Dear Customer, picopaco.
You are receiving this message, due to you protection, Our Online Technical Security Service Foreign IP Spy recently detected that your online account was recently logged on from am 81.206.87.152 without am International Access Code (I.A.C) and from an unregistered computer, which was not verified by the Our Online Service Department.
If you last logged in you online account on Thursday April 5th 2007, by the time 6:45 pm from an Foreign Ip their is no need for you to panic, but if you did log in your account on the above Date and Time, kindly take 2-3 minute of your online banking experince to verify and register your computer now to avoid identity theft, your protection is our future medal.
Verification Link
Notice: You can acess your account from a foreign IP or country by getting am (I.A.C) International Access Code, by contacting our local brances close to you.
I won't even count all the spelling mistakes but what the hell is a Our Online Technical Security Service Foreign IP Spy?
take 2-3 minute of your online banking experince. What?
your protection is our future medal
Hahaha ROFL, What the heck is a future medal?
picopaco? is that like Pico de gallo (yes it is almost Cinco de Mayo)
And here is the kicker; the spammer forgot to include a link. I hope he didn't rent a spambot army because he won't get his money out of this one
Dear Customer, picopaco.
You are receiving this message, due to you protection, Our Online Technical Security Service Foreign IP Spy recently detected that your online account was recently logged on from am 81.206.87.152 without am International Access Code (I.A.C) and from an unregistered computer, which was not verified by the Our Online Service Department.
If you last logged in you online account on Thursday April 5th 2007, by the time 6:45 pm from an Foreign Ip their is no need for you to panic, but if you did log in your account on the above Date and Time, kindly take 2-3 minute of your online banking experince to verify and register your computer now to avoid identity theft, your protection is our future medal.
Verification Link
Notice: You can acess your account from a foreign IP or country by getting am (I.A.C) International Access Code, by contacting our local brances close to you.
I won't even count all the spelling mistakes but what the hell is a Our Online Technical Security Service Foreign IP Spy?
take 2-3 minute of your online banking experince. What?
your protection is our future medal
Hahaha ROFL, What the heck is a future medal?
picopaco? is that like Pico de gallo (yes it is almost Cinco de Mayo)
And here is the kicker; the spammer forgot to include a link. I hope he didn't rent a spambot army because he won't get his money out of this one
Wednesday, May 02, 2007
Nice Silverlight Developer Reference Poster
The supported languages are C#, VB, JScript, IronPython, VB10 (soon), IronRuby (soon). VB10 soon???? VB9 isn't even out yet. LINQ is supported by Silverlight, but support for XLINQ (LINQ to XML) is coming soon. The Opera browser and Windows 2000 will also be supported soon as well as RSS/Atom support.
Get the full size poster here: http://download.microsoft.com/download/f/2/e/f2ecc2ad-c498-4538-8a2c-15eb157c00a7/SL_Map_FinalNET.png or click on the image
Tuesday, May 01, 2007
Microsoft SQL Server Community Samples: Service Broker On CodePlex, Astoria and Jasper Announced
Microsoft SQL Server Community Samples: Service Broker On CodePlex
Don't get all excited yet because nothing is available yet. I am just letting you know that this is a brand new project on CodePlex.
Here is the URL: http://www.codeplex.com/SQLSrvSrvcBrkr
Astoria
Project Codename “Astoria”
The goal of Microsoft Codename Astoria is to enable applications to expose data as a data service that can be consumed by web clients within a corporate network and across the internet. The data service is reachable over regular HTTP requests, and standard HTTP verbs such as GET, POST, PUT and DELETE are used to perform operations against the service. The payload format for the service is controllable by the application, but all options are simple, open formats such as plan XML and JSON. Web-friendly technologies make Astoria an ideal data back-end for AJAX-style applications, and other applications that need to operate against data that is across the web.
To learn more about Project Astoria or download the CTP, visit the Project Astoria website at http://astoria.mslivelabs.com.
Jasper
Project Codename “Jasper”
Project Jasper is geared towards iterative and agile development. You can start interacting with the data in your database without having to create mapping files or define classes. You can build user interfaces by naming controls according to your model without worrying about binding code. Project Jasper is also extensible, allowing you to provide your own business logic and class model. Since Project Jasper is built on top of the ADO.NET Entity Framework, it supports rich queries and complex mapping.
To learn more about Project Jasper visit the ADO.NET Blog at http://blogs.msdn.com/adonetTo download the Project Jasper CTP visit http://www.microsoft.com/downloads/details.aspx?FamilyId=471BB3AC-B31A-49CD-A567-F2E286715C8F&displaylang=en.
>>Since Project Jasper is built on top of the ADO.NET Entity Framework
Mmmm didn't they take the ADO.NET Entity Framework out of Orcas on Sunday?
Don't get all excited yet because nothing is available yet. I am just letting you know that this is a brand new project on CodePlex.
Here is the URL: http://www.codeplex.com/SQLSrvSrvcBrkr
Astoria
Project Codename “Astoria”
The goal of Microsoft Codename Astoria is to enable applications to expose data as a data service that can be consumed by web clients within a corporate network and across the internet. The data service is reachable over regular HTTP requests, and standard HTTP verbs such as GET, POST, PUT and DELETE are used to perform operations against the service. The payload format for the service is controllable by the application, but all options are simple, open formats such as plan XML and JSON. Web-friendly technologies make Astoria an ideal data back-end for AJAX-style applications, and other applications that need to operate against data that is across the web.
To learn more about Project Astoria or download the CTP, visit the Project Astoria website at http://astoria.mslivelabs.com.
Jasper
Project Codename “Jasper”
Project Jasper is geared towards iterative and agile development. You can start interacting with the data in your database without having to create mapping files or define classes. You can build user interfaces by naming controls according to your model without worrying about binding code. Project Jasper is also extensible, allowing you to provide your own business logic and class model. Since Project Jasper is built on top of the ADO.NET Entity Framework, it supports rich queries and complex mapping.
To learn more about Project Jasper visit the ADO.NET Blog at http://blogs.msdn.com/adonetTo download the Project Jasper CTP visit http://www.microsoft.com/downloads/details.aspx?FamilyId=471BB3AC-B31A-49CD-A567-F2E286715C8F&displaylang=en.
>>Since Project Jasper is built on top of the ADO.NET Entity Framework
Mmmm didn't they take the ADO.NET Entity Framework out of Orcas on Sunday?
Monday, April 30, 2007
SIMP = Silverlight + IIS + MySQL + PHP
Okay so SIMP is not LAMP but it is interesting that Microsoft is doing a demo with MySQL and PHP
Read more here: http://channel9.msdn.com/Showpost.aspx?postid=304526
Read more here: http://channel9.msdn.com/Showpost.aspx?postid=304526
I-Hate-Oracle Club Forum On Worse Than Failure
The daily WTF (Worse Than Failure) has a forum with the following name: the The I-Hate-Oracle Club
"Because it's time we senselessly bash someone besides Microsoft."
Here are some threads
Even Oracle hates Oracle
The empty string IS NULL
How Oracle ruined my holidays
ORA-00942: table or view does not exist
Hate is too weak an adjective
Real Application Cluster or real application pain in the ass
Anyway if you want to check it out yourself go here: http://forums.worsethanfailure.com/forums/17/ShowForum.aspx
"Because it's time we senselessly bash someone besides Microsoft."
Here are some threads
Even Oracle hates Oracle
The empty string IS NULL
How Oracle ruined my holidays
ORA-00942: table or view does not exist
Hate is too weak an adjective
Real Application Cluster or real application pain in the ass
Anyway if you want to check it out yourself go here: http://forums.worsethanfailure.com/forums/17/ShowForum.aspx
Saturday, April 28, 2007
Podcast With Jeff Atwood From Coding Horror
Jeff Atwood is on the latest podcast from DotNetRocks, Jeff Atwood is the guy who blogs on CodingHorror. If you have never heard of CodingHorror then you should definitely check it out
A couple of weeks ago there was a post titled Why Can't Programmers.. Program?
In that post there is the FizzBuzz challenge. What is the FizzBuzz challenge?
So what is the problem? Well this is what Jeff said
Interesting indeed. Anyway listen to the podcast which you can download here: Jeff Atwood on the Human Side of Software Development
In the meanwhile here is the T-SQL solution, of course if you had a number table you could skip the loop.
DECLARE @LoopInt INT
SET @LoopInt =1
WHILE @LoopInt <= 100 BEGIN
PRINT ISNULL(NULLIF(CASE WHEN @LoopInt % 3 = 0 THEN 'Fizz' ELSE '' END
+ CASE WHEN @LoopInt % 5 = 0 THEN 'Buzz' ELSE '' END, ''), @LoopInt)
SET @LoopInt= @LoopInt + 1
END
Output
------------------
1
2
Fizz
4
Buzz
Fizz
7
8
Fizz
Buzz
11
Fizz
13
14
FizzBuzz
16
17
Fizz
19
Buzz
Fizz
22
23
Fizz
Buzz
26
Fizz
28
29
FizzBuzz
31
32
Fizz
34
Buzz
Fizz
37
38
Fizz
Buzz
41
Fizz
43
44
FizzBuzz
46
47
Fizz
49
Buzz
Fizz
52
53
Fizz
Buzz
56
Fizz
58
59
FizzBuzz
61
62
Fizz
64
Buzz
Fizz
67
68
Fizz
Buzz
71
Fizz
73
74
FizzBuzz
76
77
Fizz
79
Buzz
Fizz
82
83
Fizz
Buzz
86
Fizz
88
89
FizzBuzz
91
92
Fizz
94
Buzz
Fizz
97
98
Fizz
Buzz
A couple of weeks ago there was a post titled Why Can't Programmers.. Program?
In that post there is the FizzBuzz challenge. What is the FizzBuzz challenge?
Write a program that prints the numbers from 1 to 100. But for multiples of
three print "Fizz" instead of the number and for the multiples of five print
"Buzz". For numbers which are multiples of both three and five print "FizzBuzz".
So what is the problem? Well this is what Jeff said
Most good programmers should be able to write out on paper a program which does
this in a under a couple of minutes. Want to know something scary? The majority
of comp sci graduates can't. I’ve also seen self-proclaimed senior programmers
take more than 10-15 minutes to write a solution.
Interesting indeed. Anyway listen to the podcast which you can download here: Jeff Atwood on the Human Side of Software Development
In the meanwhile here is the T-SQL solution, of course if you had a number table you could skip the loop.
DECLARE @LoopInt INT
SET @LoopInt =1
WHILE @LoopInt <= 100 BEGIN
PRINT ISNULL(NULLIF(CASE WHEN @LoopInt % 3 = 0 THEN 'Fizz' ELSE '' END
+ CASE WHEN @LoopInt % 5 = 0 THEN 'Buzz' ELSE '' END, ''), @LoopInt)
SET @LoopInt= @LoopInt + 1
END
Output
------------------
1
2
Fizz
4
Buzz
Fizz
7
8
Fizz
Buzz
11
Fizz
13
14
FizzBuzz
16
17
Fizz
19
Buzz
Fizz
22
23
Fizz
Buzz
26
Fizz
28
29
FizzBuzz
31
32
Fizz
34
Buzz
Fizz
37
38
Fizz
Buzz
41
Fizz
43
44
FizzBuzz
46
47
Fizz
49
Buzz
Fizz
52
53
Fizz
Buzz
56
Fizz
58
59
FizzBuzz
61
62
Fizz
64
Buzz
Fizz
67
68
Fizz
Buzz
71
Fizz
73
74
FizzBuzz
76
77
Fizz
79
Buzz
Fizz
82
83
Fizz
Buzz
86
Fizz
88
89
FizzBuzz
91
92
Fizz
94
Buzz
Fizz
97
98
Fizz
Buzz
Lambda Expressions, LINQ, DLINQ And XLINQ Samples For Orcas
Microsoft has a bunch of samples on their sitethat show how Lambda Expressions, LINQ, DLINQ And XLINQ work in Visual Studio Orcas Beta 1
This is what is available right now
C# LINQ Samples and content for Beta 1
C# LINQ Samples and content for March CTP
VB LINQ Samples for Beta 1
If you install C# LINQ Samples and content for Beta 1 then you will see the following examples
Data
DynamicQuery
ExpressionTreeVisualizer
LinqToNorthwind
ObjectDumper
PasteXmlAsXLinq
Reflector
Rss
SampleQueries
SimpleLambdas
WinFormsDataBinding
XLinqIntro
XQuery
In addidion to the samples there are also these five whitepapers in the whitepapers folder.
LINQ to SQL Overview for CSharp Developers.doc
XLinq_Overview - Feb.doc
LINQ Project Overview.doc
CSharp 3.0 Specification.doc
Standard Query Operators.doc
This is all a must read if you want to get started with .NET 3.5
Here is the download link in case the URLs above changes: http://msdn2.microsoft.com/en-us/bb330936.aspx
This is what is available right now
C# LINQ Samples and content for Beta 1
C# LINQ Samples and content for March CTP
VB LINQ Samples for Beta 1
If you install C# LINQ Samples and content for Beta 1 then you will see the following examples
Data
DynamicQuery
ExpressionTreeVisualizer
LinqToNorthwind
ObjectDumper
PasteXmlAsXLinq
Reflector
Rss
SampleQueries
SimpleLambdas
WinFormsDataBinding
XLinqIntro
XQuery
In addidion to the samples there are also these five whitepapers in the whitepapers folder.
LINQ to SQL Overview for CSharp Developers.doc
XLinq_Overview - Feb.doc
LINQ Project Overview.doc
CSharp 3.0 Specification.doc
Standard Query Operators.doc
This is all a must read if you want to get started with .NET 3.5
Here is the download link in case the URLs above changes: http://msdn2.microsoft.com/en-us/bb330936.aspx
Thursday, April 26, 2007
Virus A Very Addictive Game
Reminds me of the first time I played tetris, very addictive. And no my wife doesn't appreciate the fact that I found this game URL below the pic http://www.rfshq.com/forum/games/virus2.swf
Tuesday, April 24, 2007
Microsoft SQL Server Reporting Services Management Changes In Katmai
Brian Welcker has posted a change that Microsoft is considering making in the next version of SQL Server (Katmai) for SQL Server Reporting Services . From the site:
"For Katmai we are considering the removal of namespace management (folders, reports, data sources, models) from the Reporting Services Add-in for SQL Server Management Studio (SSMS). In other words, we are considering removing the 'Home' folder under the Reporting Server node in SSMS."
Read the rest here:
http://blogs.msdn.com/bwelcker/archive/2007/04/24/watusi-ssrs-mangament-tools-changes-for-katmai.aspx
If that link doesn't work because management is misspelled try the home page: http://blogs.msdn.com/bwelcker/default.aspx
"For Katmai we are considering the removal of namespace management (folders, reports, data sources, models) from the Reporting Services Add-in for SQL Server Management Studio (SSMS). In other words, we are considering removing the 'Home' folder under the Reporting Server node in SSMS."
Read the rest here:
http://blogs.msdn.com/bwelcker/archive/2007/04/24/watusi-ssrs-mangament-tools-changes-for-katmai.aspx
If that link doesn't work because management is misspelled try the home page: http://blogs.msdn.com/bwelcker/default.aspx
You Should Never Use IN In SQL To JOIN With Another Table
Do you use the following syntax?
SELECT *
FROM TABLE
WHERE CoLumn IN (SELECT Column FROM TABLE)
OR this?
SELECT *
FROM TABLE
WHERE CoLumn NOT IN (SELECT Column FROM TABLE)
Do NOT use that, it will cause problems sooner or later. Don't believe me? Let's take a look
First create these 2 tables and populate them with some sample data
CREATE TABLE TestTable1 (id1 int)
CREATE TABLE TestTable2 (id2 int)
INSERT TestTable1 VALUES(1)
INSERT TestTable1 VALUES(2)
INSERT TestTable1 VALUES(3)
INSERT TestTable2 VALUES(1)
INSERT TestTable2 VALUES(2)
Now let's run the IN query
SELECT *
FROM TestTable1
WHERE id1 IN (SELECT id2 FROM TestTable2)
-----
id1
1
2
No problems here right?
What if by mistake you wrote id1 instead of id2?
SELECT *
FROM TestTable1
WHERE id1 IN (SELECT id1 FROM TestTable2)
-----
id1
1
2
3
Oops all 3 rows are returned, if you just run this SELECT id1 FROM TestTable2 you will get this error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'id1'.
So what happens? SQL Server sees column id1 and says "yes I have that it is in the TestTable1 table, I can use that"
What can we do? Use EXISTS because you will get an error instead of a wrong resultset
SELECT *
FROM t1
WHERE EXISTS (SELECT * FROM TestTable2 t2 WHERE t2.id2 = t1.id1 )
-----
id1
1
2
A JOIN will do the same as EXISTS
SELECT t1.*
FROM TestTable1 t1
JOIN TestTable2 t2 ON t2.id2 = t1.id1
-----
id1
1
2
Now let's try NOT IN
SELECT *
FROM TestTable1
WHERE id1 NOT IN (SELECT id2 FROM TestTable2)
-----
id1
3
No problem right?
Add a NULL value to the TestTable2 table
INSERT TestTable2 VALUES(NULL)
Let's try running it again
SELECT *
FROM TestTable1
WHERE id1 NOT IN (SELECT id2 FROM TestTable2)
Where are my rows? Nowhere, since NULL is not equal to anything including another NULL SQL just returns nothing
What happens when you use NOT EXISTS?
SELECT *
FROM TestTable1 t1
WHERE NOT EXISTS (SELECT * FROM TestTable2 t2 WHERE t2.id2 = t1.id1 )
-----
id1
3
That works without a problem
What about a LEFT JOIN?
SELECT t1.*
FROM TestTable1 t1
LEFT JOIN TestTable2 t2 ON t2.id2 = t1.id1
WHERE t2.id2 IS NULL
-----
id1
3
That works without a problem also
So from now on use EXISTS, NOT EXISTS, JOIN and LEFT JOIN
DO NOT use IN or NOT IN ever again. Okay?
SELECT *
FROM TABLE
WHERE CoLumn IN (SELECT Column FROM TABLE)
OR this?
SELECT *
FROM TABLE
WHERE CoLumn NOT IN (SELECT Column FROM TABLE)
Do NOT use that, it will cause problems sooner or later. Don't believe me? Let's take a look
First create these 2 tables and populate them with some sample data
CREATE TABLE TestTable1 (id1 int)
CREATE TABLE TestTable2 (id2 int)
INSERT TestTable1 VALUES(1)
INSERT TestTable1 VALUES(2)
INSERT TestTable1 VALUES(3)
INSERT TestTable2 VALUES(1)
INSERT TestTable2 VALUES(2)
Now let's run the IN query
SELECT *
FROM TestTable1
WHERE id1 IN (SELECT id2 FROM TestTable2)
-----
id1
1
2
No problems here right?
What if by mistake you wrote id1 instead of id2?
SELECT *
FROM TestTable1
WHERE id1 IN (SELECT id1 FROM TestTable2)
-----
id1
1
2
3
Oops all 3 rows are returned, if you just run this SELECT id1 FROM TestTable2 you will get this error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'id1'.
So what happens? SQL Server sees column id1 and says "yes I have that it is in the TestTable1 table, I can use that"
What can we do? Use EXISTS because you will get an error instead of a wrong resultset
SELECT *
FROM t1
WHERE EXISTS (SELECT * FROM TestTable2 t2 WHERE t2.id2 = t1.id1 )
-----
id1
1
2
A JOIN will do the same as EXISTS
SELECT t1.*
FROM TestTable1 t1
JOIN TestTable2 t2 ON t2.id2 = t1.id1
-----
id1
1
2
Now let's try NOT IN
SELECT *
FROM TestTable1
WHERE id1 NOT IN (SELECT id2 FROM TestTable2)
-----
id1
3
No problem right?
Add a NULL value to the TestTable2 table
INSERT TestTable2 VALUES(NULL)
Let's try running it again
SELECT *
FROM TestTable1
WHERE id1 NOT IN (SELECT id2 FROM TestTable2)
Where are my rows? Nowhere, since NULL is not equal to anything including another NULL SQL just returns nothing
What happens when you use NOT EXISTS?
SELECT *
FROM TestTable1 t1
WHERE NOT EXISTS (SELECT * FROM TestTable2 t2 WHERE t2.id2 = t1.id1 )
-----
id1
3
That works without a problem
What about a LEFT JOIN?
SELECT t1.*
FROM TestTable1 t1
LEFT JOIN TestTable2 t2 ON t2.id2 = t1.id1
WHERE t2.id2 IS NULL
-----
id1
3
That works without a problem also
So from now on use EXISTS, NOT EXISTS, JOIN and LEFT JOIN
DO NOT use IN or NOT IN ever again. Okay?
Sunday, April 22, 2007
AdventureWorksLT Sample Database Available For Download, Don't Forget To Attach The DB To See It
There is a new sample database available for SQL Server 2005. The name of this database is AdventureWorksLT , this database is a stripped down version of AdventureWorks.
You can download the installers for the AdventureWorksLT sample databases here
x86 AdventureWorksLT.msi -- 2,251 KB
x64 AdventureWorksLT_x64.msi -- 2,251 KB
Itanium (IA64) AdventureWorksLT_IA64.msi -- 2,251 KB
If you want to read more on the download page go here: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en
Okay so you have downloaded the file and installed the database. But where is the database? You will have to attach the DB, you can use the wizard or this script below (make sure that you change the path and username, both are in bold font)
USE [master]
GO
CREATE DATABASE [AdventureWorksLT] ON
( FILENAME = N'C:\YourPathHere\MSSQL\Data\AdventureWorksLT_Data.mdf' ),
( FILENAME = N'C:\YourPathHere\MSSQL\Data\AdventureWorksLT_Log.ldf' )
FOR ATTACH
GO
if exists (select name from master.sys.databases sd where name = N'AdventureWorksLT' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [AdventureWorksLT].dbo.sp_changedbowner @loginame=N'LoginName', @map=false
GO
Now you can run these scripts to see how many tables, views, procedures and functions there are
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS FullTableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY FullTableName
-------------------------
dbo.BuildVersion
dbo.ErrorLog
SalesLT.Address
SalesLT.Customer
SalesLT.CustomerAddress
SalesLT.Product
SalesLT.ProductCategory
SalesLT.ProductDescription
SalesLT.ProductModel
SalesLT.ProductModelProductDescription
SalesLT.SalesOrderDetail
SalesLT.SalesOrderHeader
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS FullTableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
ORDER BY FullTableName
-------------------------------------
SalesLT.vGetAllCategories
SalesLT.vProductAndDescription
SalesLT.vProductModelCatalogDescription
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='FUNCTION'
-------------------------
ufnGetCustomerInformation
ufnGetSalesOrderStatusText
ufnGetAllCategories
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
-------------------------
uspPrintError
uspLogError
You can download the installers for the AdventureWorksLT sample databases here
x86 AdventureWorksLT.msi -- 2,251 KB
x64 AdventureWorksLT_x64.msi -- 2,251 KB
Itanium (IA64) AdventureWorksLT_IA64.msi -- 2,251 KB
If you want to read more on the download page go here: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en
Okay so you have downloaded the file and installed the database. But where is the database? You will have to attach the DB, you can use the wizard or this script below (make sure that you change the path and username, both are in bold font)
USE [master]
GO
CREATE DATABASE [AdventureWorksLT] ON
( FILENAME = N'C:\YourPathHere\MSSQL\Data\AdventureWorksLT_Data.mdf' ),
( FILENAME = N'C:\YourPathHere\MSSQL\Data\AdventureWorksLT_Log.ldf' )
FOR ATTACH
GO
if exists (select name from master.sys.databases sd where name = N'AdventureWorksLT' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [AdventureWorksLT].dbo.sp_changedbowner @loginame=N'LoginName', @map=false
GO
Now you can run these scripts to see how many tables, views, procedures and functions there are
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS FullTableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY FullTableName
-------------------------
dbo.BuildVersion
dbo.ErrorLog
SalesLT.Address
SalesLT.Customer
SalesLT.CustomerAddress
SalesLT.Product
SalesLT.ProductCategory
SalesLT.ProductDescription
SalesLT.ProductModel
SalesLT.ProductModelProductDescription
SalesLT.SalesOrderDetail
SalesLT.SalesOrderHeader
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS FullTableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
ORDER BY FullTableName
-------------------------------------
SalesLT.vGetAllCategories
SalesLT.vProductAndDescription
SalesLT.vProductModelCatalogDescription
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='FUNCTION'
-------------------------
ufnGetCustomerInformation
ufnGetSalesOrderStatusText
ufnGetAllCategories
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
-------------------------
uspPrintError
uspLogError
Saturday, April 21, 2007
ORDER BY 1 Will Not Work In LINQ The Same As In SQL
I installed Visual Studio Orcas Beta 1 last night and now I am playing around with LINQ
You know how you can do the following in SQL
SELECT *
FROM SomeTable
ORDER BY 1
This will order the resultset by the first column. This of course is not good practice but that is not the point here. In LINQ if you do orderby 1 (that is right no space between order and by) it looks like that line is skipped, no error or exception is thrown
Dump the following code in a C# console application and try for yourself
You know how you can do the following in SQL
SELECT *
FROM SomeTable
ORDER BY 1
This will order the resultset by the first column. This of course is not good practice but that is not the point here. In LINQ if you do orderby 1 (that is right no space between order and by) it looks like that line is skipped, no error or exception is thrown
Dump the following code in a C# console application and try for yourself
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string[] names = { "Burke", "Connor", "Frank",
"Everett", "Albert", "George",
"Harris", "David" };
IEnumerable expr = from s in names
where s.Length == 6
orderby 1
//order by s
select s.ToUpper();
foreach (string item in expr)
Console.WriteLine(item);
Console.ReadLine();
}
}
}
Friday, April 20, 2007
Stop messing around with SQL Server 2005 SP2 and give us SP3 ASAP!
Do you know which hotfix you have to instal to fix SQL Server 2005 Service Pack 2?
If SQL Server is between 3042 and 3053, get build 3054
If SQL Server is between 3150 and 3158, get build 3159
Why not move to Service Pack 3 instead?
Here is what is proposed on the Microsoft connect site:
Description
The SP2 is a mess.
Solution
Admit it.
Move over and stop releasing all those hotfixes, GDRs, QFEs and what not.
Give us clean and coherent SP3 now!
Here is the URL (http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=267610) to cast your vote
If SQL Server is between 3042 and 3053, get build 3054
If SQL Server is between 3150 and 3158, get build 3159
Why not move to Service Pack 3 instead?
Here is what is proposed on the Microsoft connect site:
Description
The SP2 is a mess.
Solution
Admit it.
Move over and stop releasing all those hotfixes, GDRs, QFEs and what not.
Give us clean and coherent SP3 now!
Here is the URL (http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=267610) to cast your vote
Visual Studio Orcas Beta 1 Available For Download
Visual Studio Code Name “Orcas” Beta 1
Visual Studio code name "Orcas" is the next generation development tool for Windows Vista, the 2007 Office system, and the Web. Beta 1 consists of multiple releases including, Visual Studio Professional Edition, Visual Studio Team Suite and Visual Studio Team Foundation Server, which are available as installation media ISO images you can use to install the products. Alternatively, you can download VPC images with the software pre-installed. In addition, you can download prerelease versions of Visual Basic Express, Visual C++ Express, Visual C# Express, and Visual Web Developer.
For a better download experience, MSDN Subscribers should use MSDN Subscriber Downloads for both installation media ISO images and VPC images.
These downloads are currently unavailable on Microsoft Downloads, but will be available soon. MSDN Subscribers can download these files from MSDN Subscriber Downloads.
Visual Studio Professional Edition ISO Image
Visual Studio Team Suite ISO Image
Visual Studio Team Foundation Server ISO Image
VPC Images
If you prefer the convenience of evaluating prerelease software in a virtual machine environment, you can download VPC images that include pre-installed instances of the prerelease software indicated, including any required prerequisite software.
Visual Studio Team Suite-Only VPC
Visual Studio Team Suite with Team Foundation Server VPC
Note: If you wish to use the Virtual PC image you will need Virtual PC or Virtual Server to run this image. Depending on your hardware the download files make take between 30-60 minutes to decompress these self-extracting files.
Visual Studio Express Editions
Visual Studio Express Editions are lightweight, easy-to-use and easy-to-learn tools for the hobbyist, novice and student developer.
For more information, see Visual Studio Code Name "Orcas" Express Editions.
Sunday, April 15, 2007
Interview With Itzik Ben-Gan Author Of Inside Microsoft SQL Server 2005: T-SQL Querying
If you are a SQL developer then you know who Itzik Ben-Gan is. If for some strange reason you don’t know then listen up. Itzik is a SQL Server MVP, he writes a monthly column for SQL Server Magazine and he is also a principal mentor and founder of Solid Quality Learning. You can also find him in the SQL Server programming public newsgroups. If you browse the MSDN site you will find several articles written by Itzik.
I purchased Inside Microsoft SQL Server 2005:T-SQL Querying and must say it is my favorite SQL Server 2005 book so far. Chapters 3, 6 and 9 alone are worth the money for the book. I have posted a link to a sample chapter (6) at the end of this post if you are interested to learn more about the style of writing and just to see how awesome this book really is.
Let’s get started with the interview
Q. What is the target audience for this book?
A. T-SQL developers with intermediate to advanced background.
By the way, the book is actually split to two volumes: Inside Microsoft SQL Server 2005: T-SQL Querying
and Inside Microsoft SQL Server 2005: T-SQL Programming
. Originally I intended to write one book, but I ended up with over 1200 pages; so I had to split it to two. But you should consider them as volumes 1 and 2 of one book. T-SQL Querying should be read before T-SQL Programming.
Also, I’d like to mention that several people contributed to the books, and I had great pleasure working with them. Lubor Kollar wrote Chapter 2 - Physical Query Processing of the T-SQL Querying volume and also provided great help and advice. Dejan Sarka wrote the CLR and XML content and explained what they have to do with the relational model. Roger Wolter wrote Chapter 11 - Service Broker of the T-SQL Programming volume. Steve Kass was the technical editor of the books, but contributed way more than what you would normally expect from technical editors. You can read about the contributors here:
http://www.insidetsql.com/
Q. What new technologies in SQL Server 2005 do you think are the most beneficial for developers?
A. I don’t think that it would be right of me to name two technologies in general, rather, depending on need. For example, for applications that involve XML manipulation, developers will find XML enhancements beneficial (and there are major ones in the product). For applications that need to implement at the database computationally intensive calculations, complex calculations, iterative logic, string manipulations, parsing, and so on, developers will find CLR integration beneficial. For applications that need queuing support, developers will find the new queuing infrastructure and the service broker platform beneficial. And then there’s row versioning embedded in the engine supporting the new snapshot isolations and other aspects of SQL Server; programmers will find this technology beneficial for certain types of systems that suffer from concurrency problems.
As for things that are closer to my heart; in regards to T-SQL, the two new features that I find most beneficial for developers are the OVER clause (e.g., with ranking calculations) and Common Table Expressions (CTE).
The OVER clause is really profound; I feel that it helps bridging the gap (or maybe I should say abyss) between cursors and sets. The OVER clause wasn’t implemented fully in SQL Server 2005, but even the existing implementation (especially with ranking calculations) allows simplifying and optimizing many pre-2005 solutions.
As for CTEs, they have both nonrecursive and recursive forms. The nonrecursive form has several important advantages over derived tables, allowing less code and better code maintenance. The recursive form allows for the first time in SQL Server’s history to write recursive queries, and very elegant ones. These are especially useful in manipulation of graphs (e.g., employee organizational chart, bill of materials, etc.).
Q. What will a person who reads this book gain in terms of understanding how the query engine works?
A. The approach I took in the books was first to understand logical query processing, then physical query processing (the engine), and then tackle practical problems where you need to combine your knowledge in both. It’s a problem based approach; I didn’t attempt to explain the engine for the sake of understanding how the engine works; rather to look at common practical problems developers face, write several solutions to each problem, analyze how the engine processes the solutions, optimize their performance, and simplify them. Ideal solutions are those that are both simple and perform well. A complex solution may have the side-effect of making the author proud of the ability to write such a solution, but is more prone to bugs and obviously involves maintenance overhead. The real beauty is in simple solutions that also perform well.
Regarding querying logic, the books put a lot of emphasis on correct understanding of SQL and thinking in terms of sets; this is a very though phase that developers have to go through since for most of us mere mortals it’s not a natural way of thinking.
Q. What are the most important things a person can do to master Transact-SQL?A. The quickest way is to issue the following code:
USE master ;-)
On a more serious note, I believe that there’s no quick way—there’s a way; it’s an ongoing thing. I can give recommendations based on what I try to follow. But some aspects of the way are personal; different things may work differently for different people. Anyway, my two cents worth…
Most importantly, you need to master the basics; or maybe I should use the term fundamentals. Ego and vanity can be big obstacles here. People try to jump directly to what they consider “advanced” and don’t get the fundamentals right. Advanced techniques are a matter of combining fundamental techniques. So be diligent and invest a lot of effort in understanding the fundamentals well. In terms of querying logic this means logical query processing, sets, NULLs, three-valued-logic, all-at-once operations, and so on. In terms of physical processing, this means getting to know internal structures, access methods, analyzing execution plans, and so on.
When facing new problems, try to solve them yourself before looking at others’ solutions. When you try something yourself you understand it best. This will also help you become more creative and develop your own techniques. Also, try different approaches, not just one, and compare the solutions.
Never consider yourself as being finished. Strive for perfection but never consider your solution perfect. Constantly work on problem solving; look for new problems and keep going back to old problems and try to improve the solutions. This way you polish and perfect your techniques and enhance your vocabulary.
I also feel that you get a much better understanding of a subject when you need to explain it to others.
I truly believe in all of the above, but ideally, you don’t need me to tell you these things. In fact I feel awkward and uncomfortable giving such recommendations; so if at this point you think that I’m a fart I won’t hold it against you. I feel more comfortable writing queries and talking about them. ;-)
As I said earlier, this simply feels natural to me. Though some things are common in the way to master any profession, some things are and should be personal; namely, you have to find your own way and not necessarily follow what someone else recommends to the letter. :-)
Q. Itzik , the first time I came across your name was in the Microsoft public SQL Server programming group. Can you explain to us why you participate in newsgroups and forums?
A. In the past I used to be much more active in the newsgroups; today less, but whenever I participate I feel revived—especially when visiting the public programming newsgroup. If I had to name one thing that had the greatest impact on my knowledge in T-SQL it would doubtless be my participation in the newsgroups. It’s a very fertile soil to grow. So many practical problems are posted there daily, and by trying to solve other people’s problems, you end up learning more and more. Also, many practical problems are repeated there frequently, and as time goes by, you keep perfecting your techniques, and also sometimes get to see very unique and interesting solutions posted by other people. People pay so much money to study in schools, colleges and universities; here’s a great place to learn for free, and while you’re at it, you end up helping other people. :-) It’s the perfect Dojo for T-SQL practitioners.
Q. How did the massive changes between SQL Server 2000 and SQL Server 2005 affect the research for your book?
A. I think I went through a different experience than some other authors. I’m so immersed in the T-SQL language that I feel that it’s my mother tongue. Seriously, in many cases I feel that I can express myself better with T-SQL than with English or even Hebrew which is supposed to be my native tongue. I was deeply involved in writing T-SQL code using new language elements from the very early builds of Yukon. Also, our company, Solid Quality Learning, works closely with the SQL Server development team and customer advisory team (CAT). We worked on whitepapers for beta builds and got access to specs in very early stages of the product. I also developed and delivered courses on Advanced T-SQL before writing the books. So writing the books did not involve research, rather the material was essentially part of me, and all I had to do was to convert my thoughts to words.
I have similar experience with writing in general. I have a monthly T-SQL column in SQL Server Magazine, and often people ask me where do I get ideas, and what’s the process involved. I work on so many ideas constantly not for the sake of writing, rather, it’s a natural part of me—the way I was talking about earlier. So when it’s time to write a column, it’s basically pulling something from one of the drawers in my mind and putting it into words.
Q. Name three things that you wish would be in Katmai (the next version of SQL Server)?
A. These are easy to name, but I doubt that my wishes will come true in Katmai:
1. Support the ORDER BY sub-clause of the OVER clause for aggregate functions; BTW, this has nothing to do with ordered aggregates, rather it’s a logical way to define to a window function which window to work with. I’ll provide a link to a paper which I wrote recently for people looking for more details.
2. Support the ROWS and RANGE windowing sub-clauses of the OVER clause for aggregate functions.
3. Add support for row value constructors/vector expressions.
In general I wish to see more focus on T-SQL enhancements, and specifically, a more complete implementation of the profound OVER clause. It bears great benefits within it that I believe many people are not aware of yet. You can find more details in the following paper:
http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc
Q. Do you think that every developer should have a numbers table in their database?
A. That’s a good question that makes me smile. :-) Yes! If not a table, a table function. I’ve used an auxiliary table of numbers in so many solutions; I find it a great helper tool to solve many querying problems. Just as an example, here’s a query using an auxiliary table of numbers (called Nums with a column called n) that splits strings containing comma separated lists of values (stored in a table called Arrays, with columns arrid and string):
And if you need to operate on a single array (e.g., one that you get as an input to a stored procedure), you can encapsulate the logic in an inline table function:
pos element
---- --------
1 10248
2 10249
3 10250
This implementation has major advantages (both in terms of security and performance) over the commonly practiced solution utilizing dynamic SQL.
Erland Sommarskog, a fellow MVP, wrote a great paper on the subject. You can find details here:
http://www.sommarskog.se/arrays-in-sql.html
By the way, you can vote for adding the table of numbers as part of the product here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=258733
As a tip, here’s a very fast inline table function that will return a table of numbers for you:
Output:
n
---
1
2
3
4
5
6
7
8
9
10
It’s even faster than querying a real table of numbers. ;-)
Q. Why is it important to think in sets instead of procedural code?A. SQL is based on the relational model, which in turn is based on set-theory and predicate logic. The relational model is a proven model for handling data—for several decades. It’s important to adopt set-based thinking in order to write simple and efficient solutions, otherwise, you defeat the whole premise on which the model is based. SQL queries (set-based) have several advantages over procedural/iterative code (e.g., using cursors) as far as data manipulation is concerned. Set-based solutions focus on the “what” part of the solution; namely their logic. Iterative solutions spend a lot of code on the “how” part, clouding the logic behind all the code; lengthy code naturally has maintenance overhead. Set-based solutions allow the optimizer to choose an execution plan among several possible plans based on cost estimations, while iterative solutions typically force a very rigid plan. In short, set-based solutions allow you to focus on logic, and speak with the database in its native language. Also, cursors have a lot of overhead associated with the record-by-record manipulation.
I’m not saying that ALL problems are best solved with set-based solutions, but the majority of data manipulation related problems are. Tasks that involve less data manipulation, and more computations, string manipulation, parsing, inherently iterative logic, these can benefit from procedural solutions. But T-SQL (and SQL in general) was not designed to handle such tasks efficiently. That’s an area where .NET is much stronger, richer and more efficient. These are the areas where I see the benefit in the CLR integration in SQL Server 2005.
Q. Which chapter was the hardest to write and can you explain why?
A. Chapter 3 – Query Tuning in the T-SQL Querying volume. It was the hardest for several reasons.
In this chapter I cover internal structures and access methods. It was very important for me to depict internal structures and diagrams of access methods graphically as accurately and as detailed as possible. I created many figures and worked on each for many hours at the pixel level. Several figures took a day of work each. I actually took snapshots of real internal structures like pages of B-Trees with DBCC PAGE and DBCC IND commands, and incorporated real bits of data in the diagrams to make them as reliable as possible.
Also, the chapter appears early in the first volume. I wanted it early in the books to provide important foundations and background that is required for the rest of the books. But I had to include queries used for performance analysis that use advanced techniques that I explain later in the book.
There were also other obstacles.
This was the chapter I wrote last, and it took me several very draining weeks to complete. It ended up with over 100 pages. There’s a lot of query tuning coverage throughout the books, but it’s not the tuning part that was hard, rather the aforementioned reasons.
Q. Why do you write technical books?
A. I’m a teacher in heart and sole. I think that writing is an integral part of teaching. It’s a flowing river; you acquire knowledge and you pass on your knowledge to others. It’s very hard and requires a lot of discipline, but ultimately it’s a process from which you also gain a lot.
Also, there’s a more selfish reason. As I said, I’m a teacher and I want students in my classes. Books give you exposure and I hoped the books would get more students in my courses.
Q. Who are your favorite authors?
A. JRR Tolkien, William Goldman, Douglas Adams, Edgar Allan Poe, Niccolo Machiavelli, Yamamoto Tsunetomo, Miyamoto Musashi, Sun Tzu, J.K. Rowling
By the way, some of my favorite authors did not write any books, yet. :)
Here are a few examples:
David Campbell, who is the General Manager of Strategy, Infrastructure and Architecture of Microsoft SQL Server used to participate in the past in some private SQL newsgroups back in the days when he owned SQL Server’s Storage Engine. His depth of knowledge, teaching and explanatory skills, passion, humility, great English, all were evident in his posts. I asked him to write the foreword to the T-SQL Querying book; you have to read it to understand what I’m talking about. It’s pure joy to read his writings, and I sorely miss his participation in the newsgroups. I wish he had written books back in the days when he was very deeply involved in the technicalities and small details of SQL Server’s engine.
Roy Harvey, a fellow SQL Server MVP. He’s active in the SQL Server community especially in the SQL Server Programming newsgroup. When you read his posts you feel the experience, wisdom and humility flowing from them. I would have very much loved to see work published by him, but you know, some very wise and knowledgeable people shy away from publicity and exposure—unfortunately for us.
Steve Kass is another good example. Steve was the technical editor of my books and there are no words that I can use to describe his capabilities. He has a very unique combination of knowledge, logic, mathematics, SQL, genius, perfect English, and a way of explaining things in a simple manner. Oh, how much I’d love to see his work published in books some day!
I also like reading posts by David Portas; another fellow MVP. I’d love to see a book on modeling published by him some day.
Q. What SQL Server books are on your bookshelf?
A. With your permission, I’d like to broaden my reply to books covering topics that I believe are important for SQL practitioners. On my bookshelf you will find The Art of Computer Programming by Donald E. Knuth, books about logic puzzles, The Thirteen Books of The Elements by Euclid, Elements of Set Theory, Graph Theory, Regular Expressions Pocket Reference by Tony Stubblebine, Mastering Regular Expressions by Jeffrey E. F. Friedl, several SQL books by Joe Celko, SQL-99 Complete, Really by Gultzan and Peltzer, Inside Microsoft SQL Server (various versions; early by Ron Soukup, later by Kalen Delaney), The Art of SQL by Stephane Faroult with Peter Robson, and others…
You can find a more extensive list here:
http://www.sql.co.il/books/insidetsql2005/resources.htm#Books
I have to say though that I probably spend more time writing stuff than reading…
Q. I know you like math and logic puzzles, do you think it is beneficial for a SQL developer to be somewhat good in math?
A. Yes, but I would say it is beneficial to be good in logic and not necessarily generally in math. That’s why I included a whole appendix with pure logic puzzles in the T-SQL Querying volume. Some people find it odd, while people that truly follow the SQL way find it natural (at least that’s what I believe). Almost every SQL querying problem in its hart is a logic puzzle. It’s very common to see that people who are deeply immersed in SQL are also very fond of logic puzzles. It’s not by chance. My belief is that if you practice logic puzzles you can improve your SQL. Also, logic puzzles are fun and can be practiced by the whole family.
And there’s another reason to dwell on logic—one coined by Sir Arthur Conan Doyle:
"Crime is common. Logic is rare. Therefore it is upon the logic rather than upon the crime that you should dwell."
Q. What is the target audience for this book?
A. T-SQL developers with intermediate to advanced background.
By the way, the book is actually split to two volumes: Inside Microsoft SQL Server 2005: T-SQL Querying
and Inside Microsoft SQL Server 2005: T-SQL Programming. Originally I intended to write one book, but I ended up with over 1200 pages; so I had to split it to two. But you should consider them as volumes 1 and 2 of one book. T-SQL Querying should be read before T-SQL Programming.Also, I’d like to mention that several people contributed to the books, and I had great pleasure working with them. Lubor Kollar wrote Chapter 2 - Physical Query Processing of the T-SQL Querying volume and also provided great help and advice. Dejan Sarka wrote the CLR and XML content and explained what they have to do with the relational model. Roger Wolter wrote Chapter 11 - Service Broker of the T-SQL Programming volume. Steve Kass was the technical editor of the books, but contributed way more than what you would normally expect from technical editors. You can read about the contributors here:
http://www.insidetsql.com/
Q. What new technologies in SQL Server 2005 do you think are the most beneficial for developers?
A. I don’t think that it would be right of me to name two technologies in general, rather, depending on need. For example, for applications that involve XML manipulation, developers will find XML enhancements beneficial (and there are major ones in the product). For applications that need to implement at the database computationally intensive calculations, complex calculations, iterative logic, string manipulations, parsing, and so on, developers will find CLR integration beneficial. For applications that need queuing support, developers will find the new queuing infrastructure and the service broker platform beneficial. And then there’s row versioning embedded in the engine supporting the new snapshot isolations and other aspects of SQL Server; programmers will find this technology beneficial for certain types of systems that suffer from concurrency problems.
As for things that are closer to my heart; in regards to T-SQL, the two new features that I find most beneficial for developers are the OVER clause (e.g., with ranking calculations) and Common Table Expressions (CTE).
The OVER clause is really profound; I feel that it helps bridging the gap (or maybe I should say abyss) between cursors and sets. The OVER clause wasn’t implemented fully in SQL Server 2005, but even the existing implementation (especially with ranking calculations) allows simplifying and optimizing many pre-2005 solutions.
As for CTEs, they have both nonrecursive and recursive forms. The nonrecursive form has several important advantages over derived tables, allowing less code and better code maintenance. The recursive form allows for the first time in SQL Server’s history to write recursive queries, and very elegant ones. These are especially useful in manipulation of graphs (e.g., employee organizational chart, bill of materials, etc.).
Q. What will a person who reads this book gain in terms of understanding how the query engine works?
A. The approach I took in the books was first to understand logical query processing, then physical query processing (the engine), and then tackle practical problems where you need to combine your knowledge in both. It’s a problem based approach; I didn’t attempt to explain the engine for the sake of understanding how the engine works; rather to look at common practical problems developers face, write several solutions to each problem, analyze how the engine processes the solutions, optimize their performance, and simplify them. Ideal solutions are those that are both simple and perform well. A complex solution may have the side-effect of making the author proud of the ability to write such a solution, but is more prone to bugs and obviously involves maintenance overhead. The real beauty is in simple solutions that also perform well.
Regarding querying logic, the books put a lot of emphasis on correct understanding of SQL and thinking in terms of sets; this is a very though phase that developers have to go through since for most of us mere mortals it’s not a natural way of thinking.
Q. What are the most important things a person can do to master Transact-SQL?A. The quickest way is to issue the following code:
USE master ;-)
On a more serious note, I believe that there’s no quick way—there’s a way; it’s an ongoing thing. I can give recommendations based on what I try to follow. But some aspects of the way are personal; different things may work differently for different people. Anyway, my two cents worth…
Most importantly, you need to master the basics; or maybe I should use the term fundamentals. Ego and vanity can be big obstacles here. People try to jump directly to what they consider “advanced” and don’t get the fundamentals right. Advanced techniques are a matter of combining fundamental techniques. So be diligent and invest a lot of effort in understanding the fundamentals well. In terms of querying logic this means logical query processing, sets, NULLs, three-valued-logic, all-at-once operations, and so on. In terms of physical processing, this means getting to know internal structures, access methods, analyzing execution plans, and so on.
When facing new problems, try to solve them yourself before looking at others’ solutions. When you try something yourself you understand it best. This will also help you become more creative and develop your own techniques. Also, try different approaches, not just one, and compare the solutions.
Never consider yourself as being finished. Strive for perfection but never consider your solution perfect. Constantly work on problem solving; look for new problems and keep going back to old problems and try to improve the solutions. This way you polish and perfect your techniques and enhance your vocabulary.
I also feel that you get a much better understanding of a subject when you need to explain it to others.
I truly believe in all of the above, but ideally, you don’t need me to tell you these things. In fact I feel awkward and uncomfortable giving such recommendations; so if at this point you think that I’m a fart I won’t hold it against you. I feel more comfortable writing queries and talking about them. ;-)
As I said earlier, this simply feels natural to me. Though some things are common in the way to master any profession, some things are and should be personal; namely, you have to find your own way and not necessarily follow what someone else recommends to the letter. :-)
Q. Itzik , the first time I came across your name was in the Microsoft public SQL Server programming group. Can you explain to us why you participate in newsgroups and forums?
A. In the past I used to be much more active in the newsgroups; today less, but whenever I participate I feel revived—especially when visiting the public programming newsgroup. If I had to name one thing that had the greatest impact on my knowledge in T-SQL it would doubtless be my participation in the newsgroups. It’s a very fertile soil to grow. So many practical problems are posted there daily, and by trying to solve other people’s problems, you end up learning more and more. Also, many practical problems are repeated there frequently, and as time goes by, you keep perfecting your techniques, and also sometimes get to see very unique and interesting solutions posted by other people. People pay so much money to study in schools, colleges and universities; here’s a great place to learn for free, and while you’re at it, you end up helping other people. :-) It’s the perfect Dojo for T-SQL practitioners.
Q. How did the massive changes between SQL Server 2000 and SQL Server 2005 affect the research for your book?
A. I think I went through a different experience than some other authors. I’m so immersed in the T-SQL language that I feel that it’s my mother tongue. Seriously, in many cases I feel that I can express myself better with T-SQL than with English or even Hebrew which is supposed to be my native tongue. I was deeply involved in writing T-SQL code using new language elements from the very early builds of Yukon. Also, our company, Solid Quality Learning, works closely with the SQL Server development team and customer advisory team (CAT). We worked on whitepapers for beta builds and got access to specs in very early stages of the product. I also developed and delivered courses on Advanced T-SQL before writing the books. So writing the books did not involve research, rather the material was essentially part of me, and all I had to do was to convert my thoughts to words.
I have similar experience with writing in general. I have a monthly T-SQL column in SQL Server Magazine, and often people ask me where do I get ideas, and what’s the process involved. I work on so many ideas constantly not for the sake of writing, rather, it’s a natural part of me—the way I was talking about earlier. So when it’s time to write a column, it’s basically pulling something from one of the drawers in my mind and putting it into words.
Q. Name three things that you wish would be in Katmai (the next version of SQL Server)?
A. These are easy to name, but I doubt that my wishes will come true in Katmai:
1. Support the ORDER BY sub-clause of the OVER clause for aggregate functions; BTW, this has nothing to do with ordered aggregates, rather it’s a logical way to define to a window function which window to work with. I’ll provide a link to a paper which I wrote recently for people looking for more details.
2. Support the ROWS and RANGE windowing sub-clauses of the OVER clause for aggregate functions.
3. Add support for row value constructors/vector expressions.
In general I wish to see more focus on T-SQL enhancements, and specifically, a more complete implementation of the profound OVER clause. It bears great benefits within it that I believe many people are not aware of yet. You can find more details in the following paper:
http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc
Q. Do you think that every developer should have a numbers table in their database?
A. That’s a good question that makes me smile. :-) Yes! If not a table, a table function. I’ve used an auxiliary table of numbers in so many solutions; I find it a great helper tool to solve many querying problems. Just as an example, here’s a query using an auxiliary table of numbers (called Nums with a column called n) that splits strings containing comma separated lists of values (stored in a table called Arrays, with columns arrid and string):
SELECT
arrid,
n - LEN(REPLACE(LEFT(string, n), ',', '')) + 1 AS pos,
SUBSTRING(string, n,
CHARINDEX(',', string + ',', n) - n) AS val
FROM Arrays JOIN Nums
ON n <= LEN(string) AND SUBSTRING(',' + string, n, 1) = ','
ORDER BY arrid, pos;
And if you need to operate on a single array (e.g., one that you get as an input to a stored procedure), you can encapsulate the logic in an inline table function:
CREATE FUNCTION dbo.fn_split(@arr AS VARCHAR(MAX)) RETURNS TABLE
AS
RETURN
SELECT
n - LEN(REPLACE(LEFT(@arr, n), ',', '')) + 1 AS pos,
SUBSTRING(@arr, n,
CHARINDEX(',', @arr + ',', n) - n) AS element
FROM dbo.Nums
WHERE n <= LEN(@arr) AND SUBSTRING(',' + @arr, n, 1) = ',';
GO
SELECT * FROM dbo.fn_split('10248,10249,10250');
Output:pos element
---- --------
1 10248
2 10249
3 10250
This implementation has major advantages (both in terms of security and performance) over the commonly practiced solution utilizing dynamic SQL.
Erland Sommarskog, a fellow MVP, wrote a great paper on the subject. You can find details here:
http://www.sommarskog.se/arrays-in-sql.html
By the way, you can vote for adding the table of numbers as part of the product here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=258733
As a tip, here’s a very fast inline table function that will return a table of numbers for you:
CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums
WHERE n <= @n;
GO
-- Test function
SELECT * FROM dbo.fn_nums(10) AS F;
GO
Output:
n
---
1
2
3
4
5
6
7
8
9
10
It’s even faster than querying a real table of numbers. ;-)
Q. Why is it important to think in sets instead of procedural code?A. SQL is based on the relational model, which in turn is based on set-theory and predicate logic. The relational model is a proven model for handling data—for several decades. It’s important to adopt set-based thinking in order to write simple and efficient solutions, otherwise, you defeat the whole premise on which the model is based. SQL queries (set-based) have several advantages over procedural/iterative code (e.g., using cursors) as far as data manipulation is concerned. Set-based solutions focus on the “what” part of the solution; namely their logic. Iterative solutions spend a lot of code on the “how” part, clouding the logic behind all the code; lengthy code naturally has maintenance overhead. Set-based solutions allow the optimizer to choose an execution plan among several possible plans based on cost estimations, while iterative solutions typically force a very rigid plan. In short, set-based solutions allow you to focus on logic, and speak with the database in its native language. Also, cursors have a lot of overhead associated with the record-by-record manipulation.
I’m not saying that ALL problems are best solved with set-based solutions, but the majority of data manipulation related problems are. Tasks that involve less data manipulation, and more computations, string manipulation, parsing, inherently iterative logic, these can benefit from procedural solutions. But T-SQL (and SQL in general) was not designed to handle such tasks efficiently. That’s an area where .NET is much stronger, richer and more efficient. These are the areas where I see the benefit in the CLR integration in SQL Server 2005.
Q. Which chapter was the hardest to write and can you explain why?
A. Chapter 3 – Query Tuning in the T-SQL Querying volume. It was the hardest for several reasons.
In this chapter I cover internal structures and access methods. It was very important for me to depict internal structures and diagrams of access methods graphically as accurately and as detailed as possible. I created many figures and worked on each for many hours at the pixel level. Several figures took a day of work each. I actually took snapshots of real internal structures like pages of B-Trees with DBCC PAGE and DBCC IND commands, and incorporated real bits of data in the diagrams to make them as reliable as possible.
Also, the chapter appears early in the first volume. I wanted it early in the books to provide important foundations and background that is required for the rest of the books. But I had to include queries used for performance analysis that use advanced techniques that I explain later in the book.
There were also other obstacles.
This was the chapter I wrote last, and it took me several very draining weeks to complete. It ended up with over 100 pages. There’s a lot of query tuning coverage throughout the books, but it’s not the tuning part that was hard, rather the aforementioned reasons.
Q. Why do you write technical books?
A. I’m a teacher in heart and sole. I think that writing is an integral part of teaching. It’s a flowing river; you acquire knowledge and you pass on your knowledge to others. It’s very hard and requires a lot of discipline, but ultimately it’s a process from which you also gain a lot.
Also, there’s a more selfish reason. As I said, I’m a teacher and I want students in my classes. Books give you exposure and I hoped the books would get more students in my courses.
Q. Who are your favorite authors?
A. JRR Tolkien, William Goldman, Douglas Adams, Edgar Allan Poe, Niccolo Machiavelli, Yamamoto Tsunetomo, Miyamoto Musashi, Sun Tzu, J.K. Rowling
By the way, some of my favorite authors did not write any books, yet. :)
Here are a few examples:
David Campbell, who is the General Manager of Strategy, Infrastructure and Architecture of Microsoft SQL Server used to participate in the past in some private SQL newsgroups back in the days when he owned SQL Server’s Storage Engine. His depth of knowledge, teaching and explanatory skills, passion, humility, great English, all were evident in his posts. I asked him to write the foreword to the T-SQL Querying book; you have to read it to understand what I’m talking about. It’s pure joy to read his writings, and I sorely miss his participation in the newsgroups. I wish he had written books back in the days when he was very deeply involved in the technicalities and small details of SQL Server’s engine.
Roy Harvey, a fellow SQL Server MVP. He’s active in the SQL Server community especially in the SQL Server Programming newsgroup. When you read his posts you feel the experience, wisdom and humility flowing from them. I would have very much loved to see work published by him, but you know, some very wise and knowledgeable people shy away from publicity and exposure—unfortunately for us.
Steve Kass is another good example. Steve was the technical editor of my books and there are no words that I can use to describe his capabilities. He has a very unique combination of knowledge, logic, mathematics, SQL, genius, perfect English, and a way of explaining things in a simple manner. Oh, how much I’d love to see his work published in books some day!
I also like reading posts by David Portas; another fellow MVP. I’d love to see a book on modeling published by him some day.
Q. What SQL Server books are on your bookshelf?
A. With your permission, I’d like to broaden my reply to books covering topics that I believe are important for SQL practitioners. On my bookshelf you will find The Art of Computer Programming by Donald E. Knuth, books about logic puzzles, The Thirteen Books of The Elements by Euclid, Elements of Set Theory, Graph Theory, Regular Expressions Pocket Reference by Tony Stubblebine, Mastering Regular Expressions by Jeffrey E. F. Friedl, several SQL books by Joe Celko, SQL-99 Complete, Really by Gultzan and Peltzer, Inside Microsoft SQL Server (various versions; early by Ron Soukup, later by Kalen Delaney), The Art of SQL by Stephane Faroult with Peter Robson, and others…
You can find a more extensive list here:
http://www.sql.co.il/books/insidetsql2005/resources.htm#Books
I have to say though that I probably spend more time writing stuff than reading…
Q. I know you like math and logic puzzles, do you think it is beneficial for a SQL developer to be somewhat good in math?
A. Yes, but I would say it is beneficial to be good in logic and not necessarily generally in math. That’s why I included a whole appendix with pure logic puzzles in the T-SQL Querying volume. Some people find it odd, while people that truly follow the SQL way find it natural (at least that’s what I believe). Almost every SQL querying problem in its hart is a logic puzzle. It’s very common to see that people who are deeply immersed in SQL are also very fond of logic puzzles. It’s not by chance. My belief is that if you practice logic puzzles you can improve your SQL. Also, logic puzzles are fun and can be practiced by the whole family.
And there’s another reason to dwell on logic—one coined by Sir Arthur Conan Doyle:
"Crime is common. Logic is rare. Therefore it is upon the logic rather than upon the crime that you should dwell."
Amazon Link: Inside Microsoft SQL Server 2005: T-SQL Querying
Sample Chapter: http://www.sql.co.il/books/insidetsql2005/sample_chapters.htm#T-SQL_Querying_Sample_Chapters
Friday, April 13, 2007
Flamed By Celko?
I found this funny pic of the man who gave us newsgroup gems like this for example: ">> Any suggestion is greatly appreciated. <<
EVERYTHING you are doing is TOTALLY wrong. You have just been cussed
out by one of the people who wrote this language. If you have brain
instead of an ego, you might want to listen.
This is a (bad) COBOL program written in SQL! There is so much
formatting done in SQL code! The bad news -- for me-- is that this
code is so awful I cannot use it in my next book as a bad example
because it is too proprietary! You could be famous!
Your code is so awful, you even use the "tbl-" prefixes to tell us you
have no idea about RDBMS! You keep converting dates to strings because
you are writing COBOL in SQL and want strings!
Why do your have "CREATE TABLE #tmp_sched" when view would work?
Answer: because magnetic tape files have to be materialized
Why do you spit on ISO-11179 rules and use a "tbl-" prefix? Because
you know only BASIC programming, which needs the prefixes for the one
pass compiler.
You write SQL with flags like it was 1950's Assembly language! Flags
in SQL!! Ghod Damn!! Varying length identifiers!? And I loved the way
spit on ANSI/ISO Standards with "SET QUOTED_IDENTIFIER OFF", etc.?
You need help you cannot get on a newsgroup.
"
Thursday, April 12, 2007
The Chapter On SQL Server 2005 Service Pack 2 Is Closed
This is the final word on SQL Server 2005 Service Pack 2 (at least from me, I promise)
It is all explained here: http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx
So as you can see from that link, after applying the critical updates you should be on either build 9.00.3054 or 9.00.3159
It is all explained here: http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx
So as you can see from that link, after applying the critical updates you should be on either build 9.00.3054 or 9.00.3159
Wednesday, April 11, 2007
Does Anyone Use ~ (Bitwise NOT)?
I have Used ¦ (Bitwise OR) and & (Bitwise AND) but I have never used ~ (Bitwise NOT)
Lets' see what it does
SELECT ~170
this returns -171, OK Fair enough.
SELECT ~convert(tinyint,170)
this returns 85, mmmmmmm
Here is some more interesting stuff
DECLARE @i int, @i2 tinyint,@i3 smallint, @i4 bigint
SELECT @i =170,@i2 = 170,@i3 =170,@i4 = 170
SELECT ~@i int, ~@i2 tinyint,~@i3 smallint, ~@i4 bigint
output
-171 85 -171 -171
So for the tinyint it is 85, for the other datatypes it is -171
What does BOL say?
Remarks
The bitwise ~ operator performs a bitwise logical NOT for the expression, taking each corresponding bit. The bits in the result are set to 1 if one bit (for the current bit being resolved) in expression has a value of 0; otherwise, the bit in the result is cleared to a value of 1.
The ~ bitwise operator can be used only on columns of the integer data type category.
Important
When performing any kind of bitwise operation, the storage length of the expression used in the bitwise operation is important. It is recommended that you use the same number of bytes when storing values. For example, storing the decimal value of 5 as a tinyint, smallint, or int produces a value stored with different numbers of bytes. tinyint stores data using 1 byte, smallint stores data using 2 bytes, and int stores data using 4 bytes. Therefore, performing a bitwise operation on an int decimal value can produce different results as compared to a direct binary or hexidecimal translation, especially when the ~ (bitwise NOT) operator is used. The bitwise NOT operation may occur on a variable of a shorter length that, when converted to a longer data type variable, may not have the bits in the upper 8 bits set to the expected value. It is recommended that you convert the smaller data type variable to the larger data type, and then perform the NOT operation on the result.
There you have it.
Lets' see what it does
SELECT ~170
this returns -171, OK Fair enough.
SELECT ~convert(tinyint,170)
this returns 85, mmmmmmm
Here is some more interesting stuff
DECLARE @i int, @i2 tinyint,@i3 smallint, @i4 bigint
SELECT @i =170,@i2 = 170,@i3 =170,@i4 = 170
SELECT ~@i int, ~@i2 tinyint,~@i3 smallint, ~@i4 bigint
output
-171 85 -171 -171
So for the tinyint it is 85, for the other datatypes it is -171
What does BOL say?
Remarks
The bitwise ~ operator performs a bitwise logical NOT for the expression, taking each corresponding bit. The bits in the result are set to 1 if one bit (for the current bit being resolved) in expression has a value of 0; otherwise, the bit in the result is cleared to a value of 1.
The ~ bitwise operator can be used only on columns of the integer data type category.
Important
When performing any kind of bitwise operation, the storage length of the expression used in the bitwise operation is important. It is recommended that you use the same number of bytes when storing values. For example, storing the decimal value of 5 as a tinyint, smallint, or int produces a value stored with different numbers of bytes. tinyint stores data using 1 byte, smallint stores data using 2 bytes, and int stores data using 4 bytes. Therefore, performing a bitwise operation on an int decimal value can produce different results as compared to a direct binary or hexidecimal translation, especially when the ~ (bitwise NOT) operator is used. The bitwise NOT operation may occur on a variable of a shorter length that, when converted to a longer data type variable, may not have the bits in the upper 8 bits set to the expected value. It is recommended that you convert the smaller data type variable to the larger data type, and then perform the NOT operation on the result.
There you have it.
Tuesday, April 10, 2007
Hilarious Paris Hilton Subway Ad
What else needs to be said. Poor Tinkerbell or whatever the name of that dog is.
Script Multiple Procs With Create And Drop Statements In SQL Server 2005
In SQL Server 2005 Management studio you can not script out multiple procedures with a create and drop statement
This is what you have to do:
1. Open you favorite database in the management studio.
2. Make sure the summary window is visible. (Press F7)
3. In the summary window, double click databases.
4. Double click 'YourDatabase'
5. Double Click 'Programability'
6. Double Click 'Stored Procedures'
7. Select the stored procedures you want to script. (using CTRL/SHIFT clicking).
8. Right click -> Script Stored Procedures As -> Drop -> New Query Editor Window.
Repeat step 8 for the Create.
Who wants to do that? Not me.
Here is a pure T-SQL solution, I didn't feel like using SMO. I can just run this code, copy and paste the output in a new window and I am done.
This code takes care of schema's it also grabs just procedures and only the ones that are created by users (OBJECTPROPERTY 'IsMSShipped' takes care of that)
USE AdventureWorks
GO
SET NOCOUNT ON
CREATE TABLE #ProcHolder (ID int identity,ProcName VARCHAR(8000), ProcDefinition VARCHAR(MAX))
INSERT #ProcHolder
SELECT QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME),OBJECT_DEFINITION(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME)))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME)),'IsMSShipped') = 0
--SELECT * FROM #ProcHolder --if you want to test
DECLARE @LoopID int,@MaxLoopID int,@ProcDefinition VARCHAR(MAX),@ProcName VARCHAR(8000)
SELECT @LoopID =1,@MaxLoopID = MAX(id) FROM #ProcHolder
WHILE @LoopID < @MaxLoopID
BEGIN
SELECT @ProcDefinition = ProcDefinition,@ProcName =ProcName
FROM #ProcHolder
WHERE id = @LoopID
PRINT 'if exists (select * from dbo.sysobjects where id = OBJECT_ID(N''' + @ProcName + ''') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure ' + @ProcName
PRINT 'GO'
PRINT ''
PRINT ''
PRINT @ProcDefinition
PRINT 'GO'
PRINT ''
PRINT ''
SET @LoopID = @LoopID +1
END
DROP TABLE #ProcHolder
That is it, let me know what you think
This is what you have to do:
1. Open you favorite database in the management studio.
2. Make sure the summary window is visible. (Press F7)
3. In the summary window, double click databases.
4. Double click 'YourDatabase'
5. Double Click 'Programability'
6. Double Click 'Stored Procedures'
7. Select the stored procedures you want to script. (using CTRL/SHIFT clicking).
8. Right click -> Script Stored Procedures As -> Drop -> New Query Editor Window.
Repeat step 8 for the Create.
Who wants to do that? Not me.
Here is a pure T-SQL solution, I didn't feel like using SMO. I can just run this code, copy and paste the output in a new window and I am done.
This code takes care of schema's it also grabs just procedures and only the ones that are created by users (OBJECTPROPERTY 'IsMSShipped' takes care of that)
USE AdventureWorks
GO
SET NOCOUNT ON
CREATE TABLE #ProcHolder (ID int identity,ProcName VARCHAR(8000), ProcDefinition VARCHAR(MAX))
INSERT #ProcHolder
SELECT QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME),OBJECT_DEFINITION(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME)))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME)),'IsMSShipped') = 0
--SELECT * FROM #ProcHolder --if you want to test
DECLARE @LoopID int,@MaxLoopID int,@ProcDefinition VARCHAR(MAX),@ProcName VARCHAR(8000)
SELECT @LoopID =1,@MaxLoopID = MAX(id) FROM #ProcHolder
WHILE @LoopID < @MaxLoopID
BEGIN
SELECT @ProcDefinition = ProcDefinition,@ProcName =ProcName
FROM #ProcHolder
WHERE id = @LoopID
PRINT 'if exists (select * from dbo.sysobjects where id = OBJECT_ID(N''' + @ProcName + ''') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure ' + @ProcName
PRINT 'GO'
PRINT ''
PRINT ''
PRINT @ProcDefinition
PRINT 'GO'
PRINT ''
PRINT ''
SET @LoopID = @LoopID +1
END
DROP TABLE #ProcHolder
That is it, let me know what you think
Monday, April 09, 2007
Sopranos: Bobby Baccalla beats the crap out of Tony Soprano
That was a great episode last night, I am glad to see that this season started much better than last season. I watched the last episode of last season on Saturday night and I was quite excited to start watching the final season. Bobby Baccalla beat the crap out of Tony Soprano who insulted Janice after everyone got drunk while playing monopoly. Did you know that my wife’s family does the same thing with that extra rule that they made up to increase the pot?
Janice is one of those people you just cannot stand; I hate her with a passion. They way she acts towards the baby sitter is just plain wrong. BTW it was Tony’s birthday yesterday and he got golf clubs as a present among other things ;-). Yesterday was also my birthday but I did not get any golf clubs ;-(. But then again I don’t play golf or will ever play golf since it seems so boring to me. What is the point? Hit a ball then walk for 15 minutes and hit another ball, golf is not a sport I don’t care what they say otherwise.
Back to the Sopranos, I hope the other episodes are as good as the one from yesterday. Remember last season? The first episode was good; Tony got shot by Uncle Junior. Then the next episode went into the coma/dream nonsense and it was all down the hill after that.
Janice is one of those people you just cannot stand; I hate her with a passion. They way she acts towards the baby sitter is just plain wrong. BTW it was Tony’s birthday yesterday and he got golf clubs as a present among other things ;-). Yesterday was also my birthday but I did not get any golf clubs ;-(. But then again I don’t play golf or will ever play golf since it seems so boring to me. What is the point? Hit a ball then walk for 15 minutes and hit another ball, golf is not a sport I don’t care what they say otherwise.
Back to the Sopranos, I hope the other episodes are as good as the one from yesterday. Remember last season? The first episode was good; Tony got shot by Uncle Junior. Then the next episode went into the coma/dream nonsense and it was all down the hill after that.
Sunday, April 08, 2007
Thursday, April 05, 2007
Two New Hotfixes Available To Fix The Maintenance Plan Issues Caused By SQL Server 2005 SP2
Run the following query
SELECT @@VERSION.
If the SQL Server 2005 version is between 3042 and 3053, download build 3054 from http://support.microsoft.com/kb/934458
If the SQL Server 2005 version is between 3150 and 3158, download build 3159 from http://support.microsoft.com/kb/934459
SELECT @@VERSION.
If the SQL Server 2005 version is between 3042 and 3053, download build 3054 from http://support.microsoft.com/kb/934458
If the SQL Server 2005 version is between 3150 and 3158, download build 3159 from http://support.microsoft.com/kb/934459
Monday, April 02, 2007
April 8th Is Almost Here
Why should you care? Here are 4 reasons
1) My Birthday
2) Final season of Sopranos starts
3) New season of Entourage starts
4) Easter
So finally the Sopranos will end ;-( I must admit that the last season wasn’t that great, the episode in which Tony had that crazy dream with that suitcase was just horrible. Watch out for the ducks in the last episode (remember the ducks in the first episode? They will be back and we've come full circle with the show)
I did not start watching Entourage until last season, since then I have watched all the episodes (thank you Comcast on demand). Jeremy Piven (Ari Gold) and Kevin Dillon (Johnny "Drama" Chase) are my favorite characters.
Easter, oh yes the day that I have to go to Church with my wife (I made a wedding day promise). My older son will have a lot of fun with all the eggs/chocolates and gifts that the Easter Bunny will bring
It is also my birthday on April 8th, I will be getting closer to midlife crisis. I will turn 37 that day, wait isn’t 37 the new 27?
1) My Birthday
2) Final season of Sopranos starts
3) New season of Entourage starts
4) Easter
So finally the Sopranos will end ;-( I must admit that the last season wasn’t that great, the episode in which Tony had that crazy dream with that suitcase was just horrible. Watch out for the ducks in the last episode (remember the ducks in the first episode? They will be back and we've come full circle with the show)
I did not start watching Entourage until last season, since then I have watched all the episodes (thank you Comcast on demand). Jeremy Piven (Ari Gold) and Kevin Dillon (Johnny "Drama" Chase) are my favorite characters.
Easter, oh yes the day that I have to go to Church with my wife (I made a wedding day promise). My older son will have a lot of fun with all the eggs/chocolates and gifts that the Easter Bunny will bring
It is also my birthday on April 8th, I will be getting closer to midlife crisis. I will turn 37 that day, wait isn’t 37 the new 27?
Sunday, April 01, 2007
SQL Server 2007 Beta1 CTP Released
Microsoft released SQL Server 2007 Beta1 CTP earlier today. You can download it here
A ton of new things, support for spatial data. New functions to deal with numeric data (IsInt, IsBigInt, IsSmallInt). Also new is the addition of Natural Join, a bunch of new dynamic management views that make it easier to prevent deadlocks. Another cool thing is the addition of the new BigDateTime datatype, this makes it possible to enter dates before 1753, you can actually go back to -3000. That is such a big plus for databases that deal with data that's older than 1753. What are you waiting for? Download it here now
A ton of new things, support for spatial data. New functions to deal with numeric data (IsInt, IsBigInt, IsSmallInt). Also new is the addition of Natural Join, a bunch of new dynamic management views that make it easier to prevent deadlocks. Another cool thing is the addition of the new BigDateTime datatype, this makes it possible to enter dates before 1753, you can actually go back to -3000. That is such a big plus for databases that deal with data that's older than 1753. What are you waiting for? Download it here now
Friday, March 30, 2007
SQL Server 2005 Books Online Scoped Search Provided By search.live.com
Check out the new Books On Line search provided by live.com. The link below provides a scoped search of Books Online. It will only return entries for the online version of Books On Line.http://search.live.com/macros/sql_server_user_education/booksonline
Thursday, March 29, 2007
TEMPDB and SQL Server 2005 Tip
When upgrading to SQL server 2005 you already know that you have to rebuild your indexes and update the statistics. In addition to that, create 1 data file per CPU core for the TEMPDB (Dual core counts as 2 CPUs; logical procs (hyperthreading) do not)
This will make a big difference, SQL Server 2005 uses the tempdb different than 2000, by adding more files you will see a dramatic performance improvement for big queries
Our queries on SQL Server 2005 ran slower than on 2000, by adding more files the queries are twice as fast than on the SQL Server 2000 box now
This behaviour is documented here: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
Look at number 7 (Consider configuration of TEMPDB database)
and number 8 (Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads. )
This will make a big difference, SQL Server 2005 uses the tempdb different than 2000, by adding more files you will see a dramatic performance improvement for big queries
Our queries on SQL Server 2005 ran slower than on 2000, by adding more files the queries are twice as fast than on the SQL Server 2000 box now
This behaviour is documented here: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
Look at number 7 (Consider configuration of TEMPDB database)
and number 8 (Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads. )
Wednesday, March 28, 2007
A list of SQL Server 2005 Whitepapers and SQL Server 2000 Whitepapers
Kimberly Tripp compiled a nice list of all the top whitepapers available for SQL Server 2000 and SQL server 2005. Some of them are written by Bob Beauchemin and some of them are written by Kimberly herself. The number of available whitepapers for SQL server 2005 is 29 at this moment.
Get them all here: http://www.sqlskills.com/whitepapers.asp
Get them all here: http://www.sqlskills.com/whitepapers.asp
Sunday, March 25, 2007
How Not To Do Search Engine Optimization
Just ask Alex Chiu. This is what he wrote:
" Google controls 50% of the world's searches. This famous website is so controversial that it has been banned by the most popular search engine in the world 'Google'. That's right. You cannot find alexchiu.com in Google system. Some very important people don't want you to know about Alex Chiu. Alex Chiu is on more than 30 TV interviews, 250 radio interviews, and in business ever since 1996. Yet AlexChiu.com cannot show up on Google? "
Why is that? Very simple, take a look at his site http://alexchiu.com/
At the bottom of the page you will find a text area with over a thousand keywords, some of these keywords are:
System Heart Aorta Arteries Arterioles Capillaries Venules Veins Vena cava Pulmonaryarteries Lungs veins Blood Digestive Mouth Pharynx Esophagus Stomach Pancreas Gallbladder
This list goes on and on.
End result? Banned by Google.
" Google controls 50% of the world's searches. This famous website is so controversial that it has been banned by the most popular search engine in the world 'Google'. That's right. You cannot find alexchiu.com in Google system. Some very important people don't want you to know about Alex Chiu. Alex Chiu is on more than 30 TV interviews, 250 radio interviews, and in business ever since 1996. Yet AlexChiu.com cannot show up on Google? "
Why is that? Very simple, take a look at his site http://alexchiu.com/
At the bottom of the page you will find a text area with over a thousand keywords, some of these keywords are:
System Heart Aorta Arteries Arterioles Capillaries Venules Veins Vena cava Pulmonaryarteries Lungs veins Blood Digestive Mouth Pharynx Esophagus Stomach Pancreas Gallbladder
This list goes on and on.
End result? Banned by Google.
Saturday, March 24, 2007
SQL Server 2005 SP2 Has Added The OBJECT_SCHEMA_NAME Function, OBJECT_NAME Has Been Enhanced
SQL Server 2005 SP2 has an important enhancement to the OBJECT_NAME metadata function and a new OBJECT_SCHEMA_NAME metadata function. Because you pass object_id, and database_id to the OBJECT_NAME function it is not needed anymore to write dynamic SQL to get multi DB results.
The OBJECT_SCHEMA_NAME metadata function can be used to return the schema name of a schema-scoped object like a table or a view by specifying the object identifier and optional database identifier.
Read more about these 2 functions here, there is also SQL code available that show you how to use the functions on the site.
The OBJECT_SCHEMA_NAME metadata function can be used to return the schema name of a schema-scoped object like a table or a view by specifying the object identifier and optional database identifier.
Read more about these 2 functions here, there is also SQL code available that show you how to use the functions on the site.
30 + Tools and Hacks for Gmail
This makeuseof site has some cool Gmail hacks and tools. There are FireFox Extensions, GreaseMonkey scripts,Desktop APPS & Web TOOLS and plain old tips and hacks. The ones that I like the best are Gmail attachment icons and label colors. below is an image of all these things in action
Link: http://www.makeuseof.com/tag/gmail-craze-30-tools-to-make-your-gmail-better/
Link: http://www.makeuseof.com/tag/gmail-craze-30-tools-to-make-your-gmail-better/
Thursday, March 22, 2007
Microsoft SQL Server Samples and Community Projects On CodePlex
Codeplex is the project hosting site for Microsoft SQL Server Samples and Community Projects. This portal page for SQL Server on Codeplex catalogs samples included with Product Releases, samples from the community, and independent SQL Server Community projects.
Link: http://www.codeplex.com/SqlServerSamples
Make sure you check out the DBScript Utility
Link: http://www.codeplex.com/SqlServerSamples
Make sure you check out the DBScript Utility
The Ten Most Asked SQL Server Related Programming Questions In Newsgroups
I have been participating in newsgroups/forums for about five years now and lately I have noticed that at least 60% of the questions are the same ten questions.
That is why I will create a blogpost that lists these ten questions and also provides an answer for them. I think that to write this will take me about a week.
Here is what I have so far
1 selecting only current day/yesterday from a table
2 adding days/weeks/years/months to dates
3 splitting string values
4 select * from one table that doesn't exist in another table
5 getting all rows from one table and only the latest from the child table
6 getting all characters until some character (charindex + left)
7 NULL stuff ( a bunch of stuff here dealing with nulls)
8 Row values to column (PIVOT)
9 Show 0001 as 1 and show 1 as 0001
10 CASE and checking for NULLs
I guess I could have added date display formatting. Did I miss any other obvious questions?
That is why I will create a blogpost that lists these ten questions and also provides an answer for them. I think that to write this will take me about a week.
Here is what I have so far
1 selecting only current day/yesterday from a table
2 adding days/weeks/years/months to dates
3 splitting string values
4 select * from one table that doesn't exist in another table
5 getting all rows from one table and only the latest from the child table
6 getting all characters until some character (charindex + left)
7 NULL stuff ( a bunch of stuff here dealing with nulls)
8 Row values to column (PIVOT)
9 Show 0001 as 1 and show 1 as 0001
10 CASE and checking for NULLs
I guess I could have added date display formatting. Did I miss any other obvious questions?
Wednesday, March 21, 2007
SQL Server Speaks Almost As Many Languages As I Do
Saw this error while running a job
Error Code: 4860
Cannot bulk load. The file "%ls" does not exist.
Massenladen ist nicht möglich. Die Datei "%1!" ist nicht vorhanden.
Chargement en bloc impossible. Le fichier "%1!" n'existe pas.
???????????????? "%1!" ???????? No se puede realizar la carga masiva......
This is the first time that I have seen a multi-language error. It appears that SQL Server 2005 is a multi-language 'speaker'.
So we have 4 languages in this error
English
Cannot bulk load. The file "%ls" does not exist.
German
Massenladen ist nicht möglich. Die Datei "%1!" ist nicht vorhanden.
French
Chargement en bloc impossible. Le fichier "%1!" n'existe pas.
Spanish
???????????????? "%1!" ???????? No se puede realizar la carga masiva (don't know what the deal is with those question marks, probabaly a missing font)
So I speak 2 of these languages and 2.5 other languages and also a dialect. But I can curse in many more ;-)
Error Code: 4860
Cannot bulk load. The file "%ls" does not exist.
Massenladen ist nicht möglich. Die Datei "%1!" ist nicht vorhanden.
Chargement en bloc impossible. Le fichier "%1!" n'existe pas.
???????????????? "%1!" ???????? No se puede realizar la carga masiva......
This is the first time that I have seen a multi-language error. It appears that SQL Server 2005 is a multi-language 'speaker'.
So we have 4 languages in this error
English
Cannot bulk load. The file "%ls" does not exist.
German
Massenladen ist nicht möglich. Die Datei "%1!" ist nicht vorhanden.
French
Chargement en bloc impossible. Le fichier "%1!" n'existe pas.
Spanish
???????????????? "%1!" ???????? No se puede realizar la carga masiva (don't know what the deal is with those question marks, probabaly a missing font)
So I speak 2 of these languages and 2.5 other languages and also a dialect. But I can curse in many more ;-)
Tuesday, March 20, 2007
Microsoft Joins OpenAjax
OpenAjax Alliance, an open industry collaboration dedicated to developing and expanding Ajax, today announced that its membership has grown to 72 with the addition of Microsoft Corporation and 30 other companies.
"Microsoft is joining the OpenAJAX Alliance to collaborate with other industry leaders to help evolve AJAX-style development by ensuring a high degree of interoperability," said Keith Smith, group product manager of the Core Web Platform & Tools to UX Web/Client Platform & Tools team at Microsoft Corp. "By joining OpenAJAX, Microsoft is continuing its commitment to empower Web developers with technology that works cross-browser and cross-platform."
The newest OpenAjax Alliance members include: 24SevenOffice, ActiveGrid, ActiveState, Appeon, Aptana, Arimaan Global Consulting, Custom Credit Systems (Thinwire), ESRI, Getahead (DWR), Global Computer Enterprises, GoETC, Helmi Technologies, HR-XML, iPolipo, Isomorphic Software, JSSL, Lightstreamer, Microsoft, MobileAware, NetScript Technologies, OpenSpot, OpenSymphony (OpenQA), OpSource, OS3.IT, Redmonk, Tealeaf Technology, Teleca Mobile, Transmend, Visible Measures, Visual WebGui and Volantis Systems.
The Alliance is also announcing that the following members have all been awarded OpenAjax Interoperability certificates: Apache XAP, Dojo Foundation, ICEsoft, ILOG, Isomorphic, IT Mill, Lightstreamer, Open Link, Open Spot, Nexaweb, Software AG and TIBCO. The interoperability certificates represent progress by both OpenAjax Alliance and its members towards defining and achieving industry support for OpenAjax Conformance.
Read the press release here: http://www.marketwire.com/mw/release_html_b1?release_id=228535
"Microsoft is joining the OpenAJAX Alliance to collaborate with other industry leaders to help evolve AJAX-style development by ensuring a high degree of interoperability," said Keith Smith, group product manager of the Core Web Platform & Tools to UX Web/Client Platform & Tools team at Microsoft Corp. "By joining OpenAJAX, Microsoft is continuing its commitment to empower Web developers with technology that works cross-browser and cross-platform."
The newest OpenAjax Alliance members include: 24SevenOffice, ActiveGrid, ActiveState, Appeon, Aptana, Arimaan Global Consulting, Custom Credit Systems (Thinwire), ESRI, Getahead (DWR), Global Computer Enterprises, GoETC, Helmi Technologies, HR-XML, iPolipo, Isomorphic Software, JSSL, Lightstreamer, Microsoft, MobileAware, NetScript Technologies, OpenSpot, OpenSymphony (OpenQA), OpSource, OS3.IT, Redmonk, Tealeaf Technology, Teleca Mobile, Transmend, Visible Measures, Visual WebGui and Volantis Systems.
The Alliance is also announcing that the following members have all been awarded OpenAjax Interoperability certificates: Apache XAP, Dojo Foundation, ICEsoft, ILOG, Isomorphic, IT Mill, Lightstreamer, Open Link, Open Spot, Nexaweb, Software AG and TIBCO. The interoperability certificates represent progress by both OpenAjax Alliance and its members towards defining and achieving industry support for OpenAjax Conformance.
Read the press release here: http://www.marketwire.com/mw/release_html_b1?release_id=228535
Perfect SQL Developer Setup
Got this pic in the mail today. I have been working with a dual-monitor setup since 2001 but this is unquestionably better. Here is how I would use it.
Monitor1: Outlook, Word and Excel
Monitor2: Production SQL Servers
Monitor3: Staging and Development SQL Servers
Monitor4: Visual Studio, EditPlus, XML Spy, Sybase Power Designer, Visio, SQL Compare etc etc
You see, all work and no fun (makes Jack a dull boy)
Very SQLicious don’t you think? How would you use it?
Subscribe to:
Posts (Atom)