Since I have nothing interesting or fascinating to say I decided to put some links up to some posts of people who do have something interesting to say.
We will start with Hugo Kornelis who has two posts about why clustered index ordering is not guaranteed in SQL Server. The first article can be found here: http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx
And the second article that explains the behaviour can be found here: Beatles vs Stones Explanation
Developer.com has announced their Database Tool or Add-in winner and the winner is......SQL Server 2005. You can read the whole article here http://www.developer.com/db/article.php/10920_3653956_1
Euan Garden has a post telling us that SQL Server Compact Edition launches (finally)
Tim O'Reilly posted his latest State of the Computer Book Market article and SQL server is still going strong. You can fnd that article here: State of the Computer Book Market, Q4 06, Part 1, Overall Market Trend
And we will end with Internet Maverick Marc Cuban who wrote an interesting article titled Why I Don't Wear a Suit and Can't Figure Out Why Anyone Does !
I don't wear a suit either, I actually wear jeans and sneakers most of the time. When i used to work in Silicon Alley (Broadway and 21st street in New York City) I actually came to work in shorts in the summer but then again what do you expect when you have 2 lizards and a cat roaming around the office?
Wednesday, January 17, 2007
Monday, January 15, 2007
sp_refreshview
Did you know that when you create a view and then later change the table the view is not updated?
Don't believe me? Run the following block of code
CREATE TABLE TestTable (id INT,SomeCol VARCHAR(666))
GO
INSERT TestTable VALUES(1,'ABC')
GO
SELECT * FROM TestTable
GO
CREATE VIEW TestView
AS
SELECT * FROM TestTable
GO
SELECT * FROM TestView
GO
ALTER TABLE TestTable
ADD Col2 DATETIME DEFAULT CURRENT_TIMESTAMP
GO
INSERT TestTable(id,SomeCol) VALUES(2,'XYZ')
GO
SELECT * FROM TestTable
GO
SELECT * FROM TestView
GO
See what happened? The TestView does not include the Col2 column. So what can you do? There are at least two things that you can do. You can recreate the view with a create or alter statement or you can use sp_refreshview, run the code below to see how that works
sp_refreshview TestView
GO
--All good now
SELECT * FROM TestView
GO
--Clean up this mess--
DROP VIEW TestView
GO
DROP TABLE TestTable
GO
And yes I know 'real' SQL programmers never use SELECT * and 'real' SQL programmers name their defaults ;-)
Don't believe me? Run the following block of code
CREATE TABLE TestTable (id INT,SomeCol VARCHAR(666))
GO
INSERT TestTable VALUES(1,'ABC')
GO
SELECT * FROM TestTable
GO
CREATE VIEW TestView
AS
SELECT * FROM TestTable
GO
SELECT * FROM TestView
GO
ALTER TABLE TestTable
ADD Col2 DATETIME DEFAULT CURRENT_TIMESTAMP
GO
INSERT TestTable(id,SomeCol) VALUES(2,'XYZ')
GO
SELECT * FROM TestTable
GO
SELECT * FROM TestView
GO
See what happened? The TestView does not include the Col2 column. So what can you do? There are at least two things that you can do. You can recreate the view with a create or alter statement or you can use sp_refreshview, run the code below to see how that works
sp_refreshview TestView
GO
--All good now
SELECT * FROM TestView
GO
--Clean up this mess--
DROP VIEW TestView
GO
DROP TABLE TestTable
GO
And yes I know 'real' SQL programmers never use SELECT * and 'real' SQL programmers name their defaults ;-)
Saturday, January 13, 2007
Visual Studio Code Name "Orcas" - January Community Technology Preview (CTP) Released
Visual Studio Code Name "Orcas" - January Community Technology Preview (CTP) has been released.
This CTP is available as a Virtual PC image or as a self-extracting install. If you wish to use the Virtual PC image you will need Virtual PC or Virtual Server to run this image. If you wish to use the self extracting install, we advise that you do not install this on a production machine. Depending on your hardware the download files make take between 30-60 minutes to decompress.
The highlights of this CTP include:
Thi CTP is available both as a regular install, and on a virtual machine.
Have fun
This CTP is available as a Virtual PC image or as a self-extracting install. If you wish to use the Virtual PC image you will need Virtual PC or Virtual Server to run this image. If you wish to use the self extracting install, we advise that you do not install this on a production machine. Depending on your hardware the download files make take between 30-60 minutes to decompress.
The highlights of this CTP include:
- Extended, more powerful data APIs with the ADO.NET Entity Framework and LINQ to ADO.NET
- With the ADO.NET Entity Framework developers will be able to model the view of the data that is appropriate for each one of the applications they are building, independently of the structure of the data in the underlying database. The use of the Entity Data Model (EDM) enables developers to design models that follow the concepts built into the application, instead of having to map them to constructs available in relational stores. Once the model is in place, the powerful ADO.NET Entity Framework API is used to access and manipulate the data as .NET classes or as rows and columns, whatever is appropriate for each application.
- ADO.NET is fully integrated with LINQ and offers many options for using LINQ in various scenarios: LINQ to SQL provides direct access to database tables from the programming environment, LINQ to Entities enables developers to use LINQ over EDM models, and LINQ to DataSet allows the full expressivity of LINQ to be used over DataSets.
- With the ADO.NET Entity Framework developers will be able to model the view of the data that is appropriate for each one of the applications they are building, independently of the structure of the data in the underlying database. The use of the Entity Data Model (EDM) enables developers to design models that follow the concepts built into the application, instead of having to map them to constructs available in relational stores. Once the model is in place, the powerful ADO.NET Entity Framework API is used to access and manipulate the data as .NET classes or as rows and columns, whatever is appropriate for each application.
- C# 3.0 Language Support: This CTP implements all of the C#3.0 language features from the May LINQ CTP including:
- Query Expressions
- Object and Collection Initializers
- Extension Methods
- Local Variable Type Inference and Anonymous Types
- Lambdas bound to Delegates and Expression trees
- VB 9.0 Language Support: This CTP implements all of the VB 9.0 language features from the May LINQ CTP including:
- Query Expressions
- Object Initializers
- Extension Methods
- Local Variable Type Inference
- Anonymous Types
- LINQ to Objects API
- The LINQ to Objects API supports queries over any .NET collection, such as arrays and Generic Lists. This API is defined in the System.Linq namespaces inside System.Core.dll. Click here for more details about LINQ.
- The LINQ to Objects API supports queries over any .NET collection, such as arrays and Generic Lists. This API is defined in the System.Linq namespaces inside System.Core.dll. Click here for more details about LINQ.
- ClickOnce improvements
- This CTP delivers ClickOnce improvements for the deployment of Windows Presentation Foundation applications, alternative browser support and ISV rebranding.
- This CTP delivers ClickOnce improvements for the deployment of Windows Presentation Foundation applications, alternative browser support and ISV rebranding.
- Managed classes for Elliptic Curve Diffie Hellman and Elliptic Curve Digital Signature Algorithm cryptographic functionality
- With the addition of these classes, cryptographic developers now have managed classes for Elliptic Curve Diffie Hellman secret agreement and Elliptic Curve Digital Signature Algorithm signing. These classes are built on the new CNG cryptographic libraries in Windows Vista, but still follow the familiar patterns of the cryptographic classes in .NET Framework 2.0.
- With the addition of these classes, cryptographic developers now have managed classes for Elliptic Curve Diffie Hellman secret agreement and Elliptic Curve Digital Signature Algorithm signing. These classes are built on the new CNG cryptographic libraries in Windows Vista, but still follow the familiar patterns of the cryptographic classes in .NET Framework 2.0.
- Runtime and design-time support for Office 2007 (including Outlook 2007)
- Customers can build managed code add-ins with a consistent development experience, regardless of which version of Office they target, which Office application(s) they target, and which programming language they choose. Managed code add-ins enable developers to use strongly-typed class members, with the help of modern development tools, including intellisense and auto-complete. Additionally add-ins can potentially run in multiple versions of Office, enabled by abstracting version-specific code and supported by a version-resilient infrastructure.
- Customers can build managed code add-ins with a consistent development experience, regardless of which version of Office they target, which Office application(s) they target, and which programming language they choose. Managed code add-ins enable developers to use strongly-typed class members, with the help of modern development tools, including intellisense and auto-complete. Additionally add-ins can potentially run in multiple versions of Office, enabled by abstracting version-specific code and supported by a version-resilient infrastructure.
- Support for advanced lifetime management of add-ins and their AppDomains
- We’ve added the helper classes that manage the lifetime of add-ins, the objects passed between the host and add-ins, and even of the AppDomains the add-ins live in. By using the ContractBase and LifetimeToken handle, pipeline developer can let the hosts and add-ins act as if everything, including the AppDomain the add-in was activated in, was controlled by the garbage collector even though .Net Remoting would normally make that impossible.
- We’ve added the helper classes that manage the lifetime of add-ins, the objects passed between the host and add-ins, and even of the AppDomains the add-ins live in. By using the ContractBase and LifetimeToken handle, pipeline developer can let the hosts and add-ins act as if everything, including the AppDomain the add-in was activated in, was controlled by the garbage collector even though .Net Remoting would normally make that impossible.
- Client service support for Login/Logout, Role management and Profiles
- ASP.NET 2.0 shipped with new application services for authentication, authorization and personalization. Most of these services are not tied to ASP.NET and can work in non-web applications. This CTP enables the use of these services in smart client applications for Logon/Logoff, Role management and profiles.
- ASP.NET 2.0 shipped with new application services for authentication, authorization and personalization. Most of these services are not tied to ASP.NET and can work in non-web applications. This CTP enables the use of these services in smart client applications for Logon/Logoff, Role management and profiles.
- A trace listener that logs event to ETW, event tracing for Windows in Vista
- Event tracing for windows is greatly improved in Vista and the most performant loggings facility available in Windows. The System.Diagnostics.EventProviderTraceListener allows managed tracing to provide events to the Vista’s ETW infrastructure. This is a highly performant, thread-safe listener.
- Event tracing for windows is greatly improved in Vista and the most performant loggings facility available in Windows. The System.Diagnostics.EventProviderTraceListener allows managed tracing to provide events to the Vista’s ETW infrastructure. This is a highly performant, thread-safe listener.
- Jscript Intellisense support
- Jscript code formatting and Intellisense support provide developers with a richer editing experience. These improvements enable the IDE to provide statement completion, color syntax highlighting and in-place documentation to Jscript and associated script models such as ASP.NET AJAX.
- Jscript code formatting and Intellisense support provide developers with a richer editing experience. These improvements enable the IDE to provide statement completion, color syntax highlighting and in-place documentation to Jscript and associated script models such as ASP.NET AJAX.
- A new numeric type that provides support for very large numbers (Beyond the range of In64)
- All existing numeric types in the Framework have a limited range. This is the first type that supports arbitrary range and will extend to accommodate any large number as needed. This type lives in the new System.Numeric namespace where all new numeric and arithmetic features are going to reside. It supports all the basic arithmetic operations including things like Pow, DivRem and GreatestCommonDivisor. It implements the following interfaces: IFormattable, IComparable, IComparable<BigInteger> and IEquatable<BigInteger>. It is serliazable and immutable. It has implicit casts from all basic integral types and explicit casts to/from all numeric type. To learn more about this type – please visit the BCL team blog.
- All existing numeric types in the Framework have a limited range. This is the first type that supports arbitrary range and will extend to accommodate any large number as needed. This type lives in the new System.Numeric namespace where all new numeric and arithmetic features are going to reside. It supports all the basic arithmetic operations including things like Pow, DivRem and GreatestCommonDivisor. It implements the following interfaces: IFormattable, IComparable, IComparable<BigInteger> and IEquatable<BigInteger>. It is serliazable and immutable. It has implicit casts from all basic integral types and explicit casts to/from all numeric type. To learn more about this type – please visit the BCL team blog.
- LINQ over XML (XLinq)
- Enable further LINQ over XML feature support (in addition to the functionality available in the Oct 2006 CTP) such as the ability to apply XLST to transform into and out of XLinq trees, support for System.XML reader/writer interfaces for improved XML sharing with DOM applications and System.XML schema validation for XLinq nodes.
- Enable further LINQ over XML feature support (in addition to the functionality available in the Oct 2006 CTP) such as the ability to apply XLST to transform into and out of XLinq trees, support for System.XML reader/writer interfaces for improved XML sharing with DOM applications and System.XML schema validation for XLinq nodes.
- SQL Server Compact Edition (SSCE)
- SQL Server Compact Edition (SSCE) provides a local relational data store for occasionally connected client applications from desktops to devices. SSCE is light weight, embeddable and is easy to deploy with your client applications without requiring complex administration work from users. Timestamp (row version id) data type, improved table designer, Query processor enhancements and support for local transaction scope are some of the new features you find in this version of SSCE.
- SQL Server Compact Edition (SSCE) provides a local relational data store for occasionally connected client applications from desktops to devices. SSCE is light weight, embeddable and is easy to deploy with your client applications without requiring complex administration work from users. Timestamp (row version id) data type, improved table designer, Query processor enhancements and support for local transaction scope are some of the new features you find in this version of SSCE.
Thi CTP is available both as a regular install, and on a virtual machine.
Have fun
Friday, January 12, 2007
How To Check If Any, ALL Or No Parameters Have A NULL Value
Let's say you have a procedure that accepts 6 parameters. depending on if these parameters are all null, all not null, some null and some not null you want to do different things.
How can you test this the easiest? This depends what you are testing for but COALESCE and concatenation are the easiest (read least to type) to do.
So let's start with all nulls
You can do a bunch of ANDs
You can concatenate into a string but you have to first set CONCAT_NULL_YIELDS_NULL OFF because the default CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL
You can also union the parameters and do a NOT EXIST NOT NULL test
Finally you can use COALESCE which will return NULL if ALL the values are NULL
--ALL NULL
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT
IF @Param1 IS NULL
AND @Param2 IS NULL
AND @Param3 IS NULL
AND @Param4 IS NULL
AND @Param5 IS NULL
AND @Param6 IS NULL
PRINT 'ALL NULL'
SET CONCAT_NULL_YIELDS_NULL OFF
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NULL
PRINT 'ALL NULL'
IF NOT EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NOT NULL)
PRINT 'ALL NULL'
IF (SELECT COALESCE(@Param1,@Param2,@Param3,@Param4,@Param5,CONVERT(VARCHAR(10),@Param6))) IS NULL
PRINT 'ALL NULL'
Next up is to test that at least one value is not NULL
You can do a bunch of Ors
You can concatenate into a string but you have to first set CONCAT_NULL_YIELDS_NULL OFF because the default CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL
You can also union the parameters and do a EXIST NOT NULL test
Finally you can use COALESCE which will not return NULL if ANY of the values is not NULL
--Some Non Nulls
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT
SELECT @Param1 ='A'
SET CONCAT_NULL_YIELDS_NULL OFF
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NOT NULL
PRINT 'At least One Value is not NULL'
IF @Param1 IS NOT NULL
OR @Param2 IS NOT NULL
OR @Param3 IS NOT NULL
OR @Param4 IS NOT NULL
OR @Param5 IS NOT NULL
OR @Param6 IS NOT NULL
PRINT 'At least One Not NULL'
IF EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NOT NULL)
PRINT 'At least One Not NULL'
IF (SELECT COALESCE(@Param1,@Param2,@Param3,@Param4,@Param5,CONVERT(VARCHAR(10),@Param6))) IS NOT NULL
PRINT 'At least One Not NULL'
Next up is to test that there are no NULLs
You can do a bunch of ANDs
You can also union the parameters and do a NOT EXIST NULL test
Finally you can use COALESCE which will not return NULL if ANY of the values is not NULL
--No NULLS
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT
SELECT @Param1 ='A',
@Param2 ='A',
@Param3 ='A',
@Param4 ='A',
@Param5 ='A',
@Param6 =1
IF @Param1 IS NOT NULL
AND @Param2 IS NOT NULL
AND @Param3 IS NOT NULL
AND @Param4 IS NOT NULL
AND @Param5 IS NOT NULL
AND @Param6 IS NOT NULL
PRINT 'No NULLs'
IF NOT EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NULL)
PRINT 'No NULLs'
SET CONCAT_NULL_YIELDS_NULL ON
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NOT NULL
PRINT 'No NULLs'
The final test is to test that at least one value is NULL
You can do a bunch of Ors
You can concatenate into a string and leave the default CONCAT_NULL_YIELDS_NULL ON because CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL
You can also union the parameters and do a EXIST NOT NULL test
--Some Nulls
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT
SELECT @Param1 ='A',
@Param2 ='A',
@Param3 ='A',
@Param4 =null,
@Param5 ='A',
@Param6 =1
IF @Param1 IS NULL
OR @Param2 IS NULL
OR @Param3 IS NULL
OR @Param4 IS NULL
OR @Param5 IS NULL
OR @Param6 IS NULL
PRINT 'At least One NULL'
SET CONCAT_NULL_YIELDS_NULL ON
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NULL
PRINT 'At least One NULL'
ELSE
PRINT 'No NULLs'
IF EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NULL)
PRINT 'At least One NULL'
How can you test this the easiest? This depends what you are testing for but COALESCE and concatenation are the easiest (read least to type) to do.
So let's start with all nulls
You can do a bunch of ANDs
You can concatenate into a string but you have to first set CONCAT_NULL_YIELDS_NULL OFF because the default CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL
You can also union the parameters and do a NOT EXIST NOT NULL test
Finally you can use COALESCE which will return NULL if ALL the values are NULL
--ALL NULL
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT
IF @Param1 IS NULL
AND @Param2 IS NULL
AND @Param3 IS NULL
AND @Param4 IS NULL
AND @Param5 IS NULL
AND @Param6 IS NULL
PRINT 'ALL NULL'
SET CONCAT_NULL_YIELDS_NULL OFF
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NULL
PRINT 'ALL NULL'
IF NOT EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NOT NULL)
PRINT 'ALL NULL'
IF (SELECT COALESCE(@Param1,@Param2,@Param3,@Param4,@Param5,CONVERT(VARCHAR(10),@Param6))) IS NULL
PRINT 'ALL NULL'
Next up is to test that at least one value is not NULL
You can do a bunch of Ors
You can concatenate into a string but you have to first set CONCAT_NULL_YIELDS_NULL OFF because the default CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL
You can also union the parameters and do a EXIST NOT NULL test
Finally you can use COALESCE which will not return NULL if ANY of the values is not NULL
--Some Non Nulls
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT
SELECT @Param1 ='A'
SET CONCAT_NULL_YIELDS_NULL OFF
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NOT NULL
PRINT 'At least One Value is not NULL'
IF @Param1 IS NOT NULL
OR @Param2 IS NOT NULL
OR @Param3 IS NOT NULL
OR @Param4 IS NOT NULL
OR @Param5 IS NOT NULL
OR @Param6 IS NOT NULL
PRINT 'At least One Not NULL'
IF EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NOT NULL)
PRINT 'At least One Not NULL'
IF (SELECT COALESCE(@Param1,@Param2,@Param3,@Param4,@Param5,CONVERT(VARCHAR(10),@Param6))) IS NOT NULL
PRINT 'At least One Not NULL'
Next up is to test that there are no NULLs
You can do a bunch of ANDs
You can also union the parameters and do a NOT EXIST NULL test
Finally you can use COALESCE which will not return NULL if ANY of the values is not NULL
--No NULLS
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT
SELECT @Param1 ='A',
@Param2 ='A',
@Param3 ='A',
@Param4 ='A',
@Param5 ='A',
@Param6 =1
IF @Param1 IS NOT NULL
AND @Param2 IS NOT NULL
AND @Param3 IS NOT NULL
AND @Param4 IS NOT NULL
AND @Param5 IS NOT NULL
AND @Param6 IS NOT NULL
PRINT 'No NULLs'
IF NOT EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NULL)
PRINT 'No NULLs'
SET CONCAT_NULL_YIELDS_NULL ON
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NOT NULL
PRINT 'No NULLs'
The final test is to test that at least one value is NULL
You can do a bunch of Ors
You can concatenate into a string and leave the default CONCAT_NULL_YIELDS_NULL ON because CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL
You can also union the parameters and do a EXIST NOT NULL test
--Some Nulls
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT
SELECT @Param1 ='A',
@Param2 ='A',
@Param3 ='A',
@Param4 =null,
@Param5 ='A',
@Param6 =1
IF @Param1 IS NULL
OR @Param2 IS NULL
OR @Param3 IS NULL
OR @Param4 IS NULL
OR @Param5 IS NULL
OR @Param6 IS NULL
PRINT 'At least One NULL'
SET CONCAT_NULL_YIELDS_NULL ON
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NULL
PRINT 'At least One NULL'
ELSE
PRINT 'No NULLs'
IF EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NULL)
PRINT 'At least One NULL'
Thursday, January 11, 2007
SQL Server Integration Services 2005 Connectivity Whitepaper Available At ssis.wiki.com
Bob Beauchemin author of A Developer's Guide to SQL Server 2005 (Microsoft .NET Development Series)
has written a SSIS Connectivity Whitepaper(still a draft)
This whitepaper enumerates the connectivity options for databases with SQL Server Integration Services 2005 and mentions special considerations that are required when using some database sources with some of the more complex transformations.
It is a word document and contains 27 pages. You can download it here: http://ssis.wiki.com/Connectivity_White_Paper
Enjoy
has written a SSIS Connectivity Whitepaper(still a draft)This whitepaper enumerates the connectivity options for databases with SQL Server Integration Services 2005 and mentions special considerations that are required when using some database sources with some of the more complex transformations.
It is a word document and contains 27 pages. You can download it here: http://ssis.wiki.com/Connectivity_White_Paper
Enjoy
Friday, January 05, 2007
Increase Your Productivity With Query Analyzer
Did you know that you can have CTRL + Number key combinations/shortcuts in Query Analyzer? You can set it up so that CTRL + 5 executes sp_who2 for example. I thought everyone already knew this but apparently not; I showed this to 3 coworkers yesterday and they didn't know about it. So how do you set this up? It is pretty easy, select Tools -->Customize from the toolbar. In the Customize tab you will see 3 shortcuts already:
ALT + F1 sp_help
CTRL + 1 sp_who
CTRL + 2 sp_lock
To add your own queries/procedures type or paste the query or stored procedure that you want to execute into one of the empty ones (see picture below)
If you have 4 Query Analyzers applications open it will be available in all 4 of them. I usually have 4 Query Analyzer applications open, one for staging, one for development and one each for one of the production boxes. so if i have to quickly check that a table is the same on all 4 machines I just hit CTRL + 6 in all 4 Query Analyzer applications and I am done.
I created a Word document that looks like this
3 - errorlog
4 - product table
5 - country table
6 - vendor codes
7 - sp_who2
etc etc etc
The font-size is 32, I printed this out and put it next to my monitor so that I can quickly see which key combination I have to hit. Once you set this up you will see how much time it will save you.
[wrong]I did not see this functionality in SQL Server 2005 Management Studio so maybe it is something we should request from Microsoft to be added in SP3?[/wrong]
It is there in SS 2005. Click Tools -> Customize -> "Commands" tab -> "Keyboard" button (Lower left corner of screen).On the options window, click the Keyboard option (under Environment). See pictures below:
ALT + F1 sp_help
CTRL + 1 sp_who
CTRL + 2 sp_lock
To add your own queries/procedures type or paste the query or stored procedure that you want to execute into one of the empty ones (see picture below)
If you have 4 Query Analyzers applications open it will be available in all 4 of them. I usually have 4 Query Analyzer applications open, one for staging, one for development and one each for one of the production boxes. so if i have to quickly check that a table is the same on all 4 machines I just hit CTRL + 6 in all 4 Query Analyzer applications and I am done.
I created a Word document that looks like this
3 - errorlog
4 - product table
5 - country table
6 - vendor codes
7 - sp_who2
etc etc etc
The font-size is 32, I printed this out and put it next to my monitor so that I can quickly see which key combination I have to hit. Once you set this up you will see how much time it will save you.
[wrong]I did not see this functionality in SQL Server 2005 Management Studio so maybe it is something we should request from Microsoft to be added in SP3?[/wrong]
It is there in SS 2005. Click Tools -> Customize -> "Commands" tab -> "Keyboard" button (Lower left corner of screen).On the options window, click the Keyboard option (under Environment). See pictures below:
Thursday, January 04, 2007
SQL Server 2005 Express Edition for Beginners Learning Videos Available
Microsoft has a set of learning videos available on the SQL Server Express site, these videos are geared towards beginners.
The series includes almost 9 hours of video-based instruction that walks SQL Server beginners through the steps of learning about SQL Server databases to actually connecting a SQL Server database to a Web application. Select your starting point below based on your skill set.
Introduction
Learning Video 1: What is a database?
Designing Tables
Learning Video 2: Understanding Database Tables and Records
Learning Video 3: More about Column Data Types and Other Properties
Learning Video 4: Designing Relational Database Tables
Database Functions
Learning Video 5: Manipulating Database Data
Learning Video 6: More Structured Query Language
Learning Video 12: Creating and Using Stored Procedures
Learning Video 13: Enabling Full-Text Search in your Text Data
Creating and Using Reports
Learning Video 10: Getting Started with Reporting Services
Learning Video 11: Embedding, Packaging and Deploying SQL Server Express Reporting Services
Database Security
Learning Video 7: Understanding Security and Network Connectivity
Database Management
Learning Video 9: Using SQL Server Management Studio Express
Publishing to the Web
Learning Video 8: Connecting your Web Application to SQL Server 2005 Express Edition
So what are you waiting for? go to the link below to check it out.
http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx
The series includes almost 9 hours of video-based instruction that walks SQL Server beginners through the steps of learning about SQL Server databases to actually connecting a SQL Server database to a Web application. Select your starting point below based on your skill set.
Introduction
Learning Video 1: What is a database?
Designing Tables
Learning Video 2: Understanding Database Tables and Records
Learning Video 3: More about Column Data Types and Other Properties
Learning Video 4: Designing Relational Database Tables
Database Functions
Learning Video 5: Manipulating Database Data
Learning Video 6: More Structured Query Language
Learning Video 12: Creating and Using Stored Procedures
Learning Video 13: Enabling Full-Text Search in your Text Data
Creating and Using Reports
Learning Video 10: Getting Started with Reporting Services
Learning Video 11: Embedding, Packaging and Deploying SQL Server Express Reporting Services
Database Security
Learning Video 7: Understanding Security and Network Connectivity
Database Management
Learning Video 9: Using SQL Server Management Studio Express
Publishing to the Web
Learning Video 8: Connecting your Web Application to SQL Server 2005 Express Edition
So what are you waiting for? go to the link below to check it out.
http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx
Wednesday, January 03, 2007
Difference of two datetime columns caused overflow at runtime
You want to get the difference in seconds between 2 dates, you can do that by using datediff(s,date1,date2)
However if the difference in seconds is greater than a value that can be stored in an integer then you will get the following message
Server: Msg 535, Level 16, State 1, Line 1
Difference of two datetime columns caused overflow at runtime.
Run this to see that message
SELECT DATEDIFF(s,'19000101','20060101')
So what can you do to eliminate this?
You can convert to bigint and then multiply by 60
SELECT CONVERT(BIGINT,DATEDIFF(mi,'19000101','20060101'))* 60
To make sure that the match is correct you can compare these two
SELECT DATEDIFF(s,'19700101','20060101')
SELECT CONVERT(BIGINT,DATEDIFF(mi,'19700101','20060101'))* 60
They both return 1136073600
However if the difference in seconds is greater than a value that can be stored in an integer then you will get the following message
Server: Msg 535, Level 16, State 1, Line 1
Difference of two datetime columns caused overflow at runtime.
Run this to see that message
SELECT DATEDIFF(s,'19000101','20060101')
So what can you do to eliminate this?
You can convert to bigint and then multiply by 60
SELECT CONVERT(BIGINT,DATEDIFF(mi,'19000101','20060101'))* 60
To make sure that the match is correct you can compare these two
SELECT DATEDIFF(s,'19700101','20060101')
SELECT CONVERT(BIGINT,DATEDIFF(mi,'19700101','20060101'))* 60
They both return 1136073600
Tuesday, January 02, 2007
Blogstats For The Year 2006
So the year 2006 is finally over; here are some stats
Visits By Source
As you can see from the picture Google is king in terms of people reaching this blog, Tek-Tips is number two and MSN is number three. Actually the real number two is a direct link; this is either from people who type the URL in their address bar or maybe use a RSS reader or even outlook express to read the SQL server newsgroups. I assume if people use the Google groups that this counts towards the overall Google number
Visits By New And Returning
Of the people who visit this site 23.39 percent are returning visitors. I don’t know if this is good or bad and what other people’s numbers are; I have nothing to compare against.
Visits and Pageviews
So during 2006 this blog had 45375 page views, the average is 124 page views a day. I guess that this is not a bad thing; my average is higher when looking only at the last month. At least it looks like the average is going up not down ;-)
The big spike that you see is when one of my articles got posted to digg and then to dzone.
Geo Map Overlay
Most people who visit this site come from North America, Europe, India and Singapore
Nothing more to say here really; the picture speaks for itself
And that is it for the year 2006; from now on it is all 2007
Visits By Source
As you can see from the picture Google is king in terms of people reaching this blog, Tek-Tips is number two and MSN is number three. Actually the real number two is a direct link; this is either from people who type the URL in their address bar or maybe use a RSS reader or even outlook express to read the SQL server newsgroups. I assume if people use the Google groups that this counts towards the overall Google number
Visits By New And Returning
Of the people who visit this site 23.39 percent are returning visitors. I don’t know if this is good or bad and what other people’s numbers are; I have nothing to compare against.
Visits and Pageviews
So during 2006 this blog had 45375 page views, the average is 124 page views a day. I guess that this is not a bad thing; my average is higher when looking only at the last month. At least it looks like the average is going up not down ;-)
The big spike that you see is when one of my articles got posted to digg and then to dzone.
Geo Map Overlay
Most people who visit this site come from North America, Europe, India and Singapore
Nothing more to say here really; the picture speaks for itself
And that is it for the year 2006; from now on it is all 2007
Top Google Searches For December 2006 And For 2006
Below are the top searches for December 2006 and also for the whole year of 2006. These searches were made from the Google search box on this site. I removed out racists and derogatory terms but there are still some interesting searches out there
December 2006
Fleury
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
www.sampiyonhersekli.com
how to analyze logical reads in sql server?
mpentoo modules
To be is to be something in particular; to be nothing in particular or everything in general is to be nothing
Free download of projects in java
money
pages/per sec
Heidi Hanson figure competition
169.254.x.x
WHAT DOES IT MEAN TO MASK DIFFERENT TABLESIN SQL
performances COALESCE vs Case in SQL Server
redneck timeout
DSN and ODBC
Top Searches for the whole year of 2006
dtsrun from sp
query multiple databases
macrovision remover
ToBase64String sql procedure
/pub /books ansys ftp
HOW TO IMPROVE SQL SERVER Query PERFORMANCE
increase maximum row size in mssql
dbreindex microsoft sqlserver forum
Line 1: Incorrect syntax near 'TRY'.
sql server CONNECTIVITY ERRORS thro dsl after installing xp sp2
heather mills
deadlock
MS SQL zero fill
cfd freeware software
sex
cad cam software prodajem
body types
type of database backup
/tutorials/ .pdf ftp ansys
sql server management studio
December 2006
Fleury
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
www.sampiyonhersekli.com
how to analyze logical reads in sql server?
mpentoo modules
To be is to be something in particular; to be nothing in particular or everything in general is to be nothing
Free download of projects in java
money
pages/per sec
Heidi Hanson figure competition
169.254.x.x
WHAT DOES IT MEAN TO MASK DIFFERENT TABLESIN SQL
performances COALESCE vs Case in SQL Server
redneck timeout
DSN and ODBC
Top Searches for the whole year of 2006
dtsrun from sp
query multiple databases
macrovision remover
ToBase64String sql procedure
/pub /books ansys ftp
HOW TO IMPROVE SQL SERVER Query PERFORMANCE
increase maximum row size in mssql
dbreindex microsoft sqlserver forum
Line 1: Incorrect syntax near 'TRY'.
sql server CONNECTIVITY ERRORS thro dsl after installing xp sp2
heather mills
deadlock
MS SQL zero fill
cfd freeware software
sex
cad cam software prodajem
body types
type of database backup
/tutorials/ .pdf ftp ansys
sql server management studio
Top 10 Posts For The Year 2006
Below are the top 10 posts for the year 2006
SQL Query Optimizations
Five Ways To Return Values From Stored Procedures
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
COALESCE And ISNULL Differences
OPENROWSET And Excel Problems
NULL Trouble In SQL Server Land
Do You Know How Between Works With Dates?
SQL Server 2000 Undocumented Procedures For Files, Drives and Directories
Three Ways To Display Two Counts From a Table Side By Side
How Are Dates Stored In SQL server?
I will posts some more blog stats for 2006 later today
SQL Query Optimizations
Five Ways To Return Values From Stored Procedures
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
COALESCE And ISNULL Differences
OPENROWSET And Excel Problems
NULL Trouble In SQL Server Land
Do You Know How Between Works With Dates?
SQL Server 2000 Undocumented Procedures For Files, Drives and Directories
Three Ways To Display Two Counts From a Table Side By Side
How Are Dates Stored In SQL server?
I will posts some more blog stats for 2006 later today
Monday, January 01, 2007
Dynamic Search Conditions In T-SQL Has Been Updated
SQL Server MVP Erland Sommarskog has updated his Dynamic Search Conditions in T-SQL article on 2006-12-27.
Here is what is new
So what are you waiting for? It is a new year and there is new stuff to read.
Dynamic Search Conditions in T-SQL
And yes I will post my new years resolutions sometime this week
Here is what is new
Added a section on a new fascinating hybrid method built on inline table functions, discovered by SQL Server MVP Marcello Poletti.
Put back the section Using EXEC() from the appendix.
Added new section When Caching Is Really Not What You Want.
Some general review on comments of performance.
So what are you waiting for? It is a new year and there is new stuff to read.
Dynamic Search Conditions in T-SQL
And yes I will post my new years resolutions sometime this week
Sunday, December 31, 2006
The Road to Hell By Chris Rea Or Highway to Hell By AC/DC
So here is my last goofy post of the year, I will have some posts that have at least something to do with SQL and databases from tomorrow on. I just happened to hear both of these songs today and since their titles are so similar I decided to do a little poll. So the question is which of these two do you prefer?
The Road to Hell by Chris Rea from the album Road to Hell
Or
Highway to Hell by AC/DC from the album Highway to Hell
The title Highway to Hell has nothing to do with Satanism (Malcolm once said "me mum would kill me for that!"). The title came after a reporter asked AC/DC if they could describe what life was like constantly touring around the globe. Angus replied that it was "a highway to Hell", and the name stuck
Believe it or not I will pick The Road to Hell as my favorite song. If you asked me yesterday I would have picked Highway to Hell; maybe I am not angry enough today and am in a pleasant mood, It is New Years Eve after all.
The first four songs on Highway to Hell are the best, I actually prefer Walk All Over You over Highway to Hell. While Highway to Hell is a hardrock song The Road to Hell is not; you will find that it reminds you of Dire Straits when listening to it (I believe Mark Knopfler plays guitar on the song)
The Road to Hell Track listing
------------------------------------
The Road to Hell (Pt. 1)
The Road to Hell (Pt. 2)
You Must Be Evil
Texas
Looking for a Rainbow
Your Warm and Tender Love
Daytona
That's What They Always Say
I Just Wanna Be With You
Tell Me There's a Heaven
Highway to Hell Track listing
---------------------------------------
Highway to Hell
Girls Got Rhythm
Walk All Over You
Touch Too Much
Beating Around the Bush
Shot Down in Flames
Get It Hot
If You Want Blood (You've Got It)
Love Hungry Man
Night Prowler
This was of course the last album that AC/DC recorded with Bon Scott; he died 6 months later. The cause of death listed on his death certificate was "Acute alcoholic poisoning" and the verdict of the inquest "Death by misadventure". After Bon Scott's death the band hired Johnson as their new lead singer. They recorded Back in Black
in 1980 which would become the fourth best-selling album in the United States, tied with Billy Joel's Greatest Hits Vol. 1 & II with 21 million copies sold
So which one do you prefer?
The Road to Hell by Chris Rea from the album Road to Hell
Or
Highway to Hell by AC/DC from the album Highway to Hell
The title Highway to Hell has nothing to do with Satanism (Malcolm once said "me mum would kill me for that!"). The title came after a reporter asked AC/DC if they could describe what life was like constantly touring around the globe. Angus replied that it was "a highway to Hell", and the name stuck
Believe it or not I will pick The Road to Hell as my favorite song. If you asked me yesterday I would have picked Highway to Hell; maybe I am not angry enough today and am in a pleasant mood, It is New Years Eve after all.
The first four songs on Highway to Hell are the best, I actually prefer Walk All Over You over Highway to Hell. While Highway to Hell is a hardrock song The Road to Hell is not; you will find that it reminds you of Dire Straits when listening to it (I believe Mark Knopfler plays guitar on the song)
The Road to Hell Track listing
------------------------------------
The Road to Hell (Pt. 1)
The Road to Hell (Pt. 2)
You Must Be Evil
Texas
Looking for a Rainbow
Your Warm and Tender Love
Daytona
That's What They Always Say
I Just Wanna Be With You
Tell Me There's a Heaven
Highway to Hell Track listing
---------------------------------------
Highway to Hell
Girls Got Rhythm
Walk All Over You
Touch Too Much
Beating Around the Bush
Shot Down in Flames
Get It Hot
If You Want Blood (You've Got It)
Love Hungry Man
Night Prowler
This was of course the last album that AC/DC recorded with Bon Scott; he died 6 months later. The cause of death listed on his death certificate was "Acute alcoholic poisoning" and the verdict of the inquest "Death by misadventure". After Bon Scott's death the band hired Johnson as their new lead singer. They recorded Back in Black
in 1980 which would become the fourth best-selling album in the United States, tied with Billy Joel's Greatest Hits Vol. 1 & II with 21 million copies soldSo which one do you prefer?
Friday, December 29, 2006
SQL Server 2005 SP2 Backup With Vardecimal Enabled Can't Be Restored On SQL Server 2005 SP1 or Earlier
SQL Server 2005 SP2 introduces the Vardecimal storage format. The Vardecimal storage format is a new storage format to store decimal/numeric data. This new storage format is not understood by SQL Server 2005 or SQL Server 2005/SP1. Just like you cannot attach a SQL Server 2005 database to SQL Server 2000, attaching/restoring a SQL Server 2005/SP2 database that has been enabled for Vardecimal storage format to earlier versions of SQL Server 2005 will fail. SQL Sever implements this by incrementing the database version number when the database is enabled for Vardecimal storage format. When you disable Vardecimal storage format on a database, its database version is decremented so that the database can now be attached to earlier versions of SQL Server 2005.
Read more about this and what the implications are here: http://blogs.msdn.com/sqlserverstorageengine/archive/2006/12/29/vardecimal-storage-format-and-its-implications-on-backup-recovery.aspx
Read more about this and what the implications are here: http://blogs.msdn.com/sqlserverstorageengine/archive/2006/12/29/vardecimal-storage-format-and-its-implications-on-backup-recovery.aspx
Thursday, December 28, 2006
Guess Who Gave This Answer In A Newsgroup?
Try to guess who gave this answer. Here is a small hint, the person looks like Anton Szandor LaVey
Question: I guess the key question for me is, can this be done entirely in SQL?
Answer: The answer is always "Yes, we can do it in SQL!"
The right answer is "But, like a size 26 thong, just because you can
does not mean you should!"
Google around for "Bin packing" and/or "Knapsack" problems on some math
websites. This is a known NP-complete problem. In English, it means
that the only way to solve it is to try all possible combinations, so
the execution time grows fastrer than any polynominal expression (i.e
think about factorials or worse).
There are often several valid solutions, too. Being a set-oriented
language, SQL will attempt to find the set of ALL solutions. And run
forever.
This is a job for a procedure (yes, [name here] is saying nice thing about
procedural code!) which will stop at the first usable answer, even if
it is not optimal. Now you have to pick your algorithm. This is
usually the Greedy algorithm ("grab the biggest bite you can and add it
to the answer; see if you met the goal; if not, repeat") modified to do
some back tracking.
So who gave this answer?
Question: I guess the key question for me is, can this be done entirely in SQL?
Answer: The answer is always "Yes, we can do it in SQL!"
The right answer is "But, like a size 26 thong, just because you can
does not mean you should!"
Google around for "Bin packing" and/or "Knapsack" problems on some math
websites. This is a known NP-complete problem. In English, it means
that the only way to solve it is to try all possible combinations, so
the execution time grows fastrer than any polynominal expression (i.e
think about factorials or worse).
There are often several valid solutions, too. Being a set-oriented
language, SQL will attempt to find the set of ALL solutions. And run
forever.
This is a job for a procedure (yes, [name here] is saying nice thing about
procedural code!) which will stop at the first usable answer, even if
it is not optimal. Now you have to pick your algorithm. This is
usually the Greedy algorithm ("grab the biggest bite you can and add it
to the answer; see if you met the goal; if not, repeat") modified to do
some back tracking.
So who gave this answer?
Wednesday, December 27, 2006
Sweet Child O' Mine By Guns N' Roses Or Child in Time By Deep Purple?
Yesterday we talked about Whole Lotta Love and whole Lotta Rosie today we are talking about Sweet Child O' Mine and Child In Time. So which is your favorite?
Sweet Child O' Mine by Guns N' Roses from the album Appetite for Destruction
Child in Time by Deep Purple from the album Deep Purple in Rock
This is a very tough pick for me because I love both songs but I would have to go for Sweet Child O' Mine. This song is perfect in every way from the vocals to the guitar solo to the introduction's famous D-flat based riff. The lyrics were written by Axl Rose as a love letter to his girlfriend at the time Erin Everly. No matter how many times I listen to the song it never bores me; something I can’t say about Paradise City; I just can’t stand that song anymore
Child in time is a song that is over 10 minutes long; It starts slowly with only an organ then builds up to a faster pace and then stops abruptly to start over again. Guitarist Ritchie Blackmore solo is just amazing and Ian Gillan’s voice goes from quiet singing to loud screaming. If you never heard this song I urge you to check it out.
Lyrically, the song is deep and dark; here are the lyrics:
Sweet child in time,
You'll see the line.
The line that's drawn between,
The good and the bad.
See the blind man,
He's shooting at the world.
The bullets flying,
Mmm... they're taking toll.
If you've been bad,
Lord I bet you have.
And youve not been hit,
You've not been hit by flying lead.
You'd better close your eyes,
You'd better bow your head.
Wait for the ricochet...
I have at least 3 versions of this song; the original, the live version from Made In Japan and the live version from Scandinavian Nights.
My favorite Guns N' Roses songs:
Mr. Browstone
Live and Let Die
Don't Cry
Civil War
Knockin' on Heaven's Door
You Could Be Mine
It's So Easy
Nightrain
Out Ta Get Me
Mr. Brownstone
My Michelle
Think About You
Sweet Child O' Mine
You're Crazy
My favorite Deep Purple songs:
Child In Time
Smoke On The Water
Hush
Soldier Of Fortune (David Coverdale is the singer on this one)
Speed King (the longer version with crazy guitar intro)
So which are your favorites?
Sweet Child O' Mine by Guns N' Roses from the album Appetite for Destruction
Child in Time by Deep Purple from the album Deep Purple in Rock
This is a very tough pick for me because I love both songs but I would have to go for Sweet Child O' Mine. This song is perfect in every way from the vocals to the guitar solo to the introduction's famous D-flat based riff. The lyrics were written by Axl Rose as a love letter to his girlfriend at the time Erin Everly. No matter how many times I listen to the song it never bores me; something I can’t say about Paradise City; I just can’t stand that song anymore
Child in time is a song that is over 10 minutes long; It starts slowly with only an organ then builds up to a faster pace and then stops abruptly to start over again. Guitarist Ritchie Blackmore solo is just amazing and Ian Gillan’s voice goes from quiet singing to loud screaming. If you never heard this song I urge you to check it out.
Lyrically, the song is deep and dark; here are the lyrics:
Sweet child in time,
You'll see the line.
The line that's drawn between,
The good and the bad.
See the blind man,
He's shooting at the world.
The bullets flying,
Mmm... they're taking toll.
If you've been bad,
Lord I bet you have.
And youve not been hit,
You've not been hit by flying lead.
You'd better close your eyes,
You'd better bow your head.
Wait for the ricochet...
I have at least 3 versions of this song; the original, the live version from Made In Japan and the live version from Scandinavian Nights.
My favorite Guns N' Roses songs:
Mr. Browstone
Live and Let Die
Don't Cry
Civil War
Knockin' on Heaven's Door
You Could Be Mine
It's So Easy
Nightrain
Out Ta Get Me
Mr. Brownstone
My Michelle
Think About You
Sweet Child O' Mine
You're Crazy
My favorite Deep Purple songs:
Child In Time
Smoke On The Water
Hush
Soldier Of Fortune (David Coverdale is the singer on this one)
Speed King (the longer version with crazy guitar intro)
So which are your favorites?
Tuesday, December 26, 2006
sys.dm_db_session_space_usage
Okay so today we will talk about the sys.dm_db_session_space_usage dynamic management view.
This is the third dynamic management view that I have covered; a list if all of them and the date that I covered some of them can be found below
http://sqlservercode.blogspot.com/2006/08/dynamic-management-views-blog-post.html
Before we start here are a couple of remarks
IAM pages are not included in any of the allocation or deallocation counts reported by this view.
Page counters are initialized to zero (0) at the start of a session. The counters track the total number of pages that have been allocated or deallocated for tasks that are already completed in the session. The counters are updated only when a task ends; they do not reflect running tasks.
A session can have multiple requests active at the same time. A request can start multiple threads, tasks, if it is a parallel query.
Here is some information about the columns in this view
session_id
smallint
Session ID.
-- session_id maps to session_id in sys.dm_exec_sessions.
database_id
smallint
Database ID.
user_objects_alloc_page_count
bigint
Number of pages reserved or allocated for user objects by this session.
user_objects_dealloc_page_count
bigint
Number of pages deallocated and no longer reserved for user objects by this session.
internal_objects_alloc_page_count
bigint
Number of pages reserved or allocated for internal objects by this session.
internal_objects_dealloc_page_count
bigint
Number of pages deallocated and no longer reserved for internal objects by this session.
First we will talk about user objects
User Objects
The following objects are included in the user object page counters:
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid
Values in the columns
--------------------------------------
user_objects_alloc_page_count =0
user_objects_dealloc_page_count = 0
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0
CREATE TABLE #temp(id VARCHAR(MAX))
INSERT #TEMP VALUES(REPLICATE('a',25000))
INSERT #TEMP VALUES(REPLICATE('b',25000))
INSERT #TEMP VALUES(REPLICATE('c',25000))
INSERT #TEMP VALUES(REPLICATE('d',25000))
INSERT #TEMP VALUES(REPLICATE('e',25000))
SELECT *
FROM #temp
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid
user_objects_alloc_page_count =5
user_objects_dealloc_page_count = 0
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0
DROP TABLE #temp
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid
Values in the columns
--------------------------------------
user_objects_alloc_page_count =5
user_objects_dealloc_page_count = 5
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0
CREATE TABLE #temp(id VARCHAR(MAX))
INSERT #TEMP VALUES(REPLICATE('a',25000))
INSERT #TEMP VALUES(REPLICATE('b',25000))
INSERT #TEMP VALUES(REPLICATE('c',25000))
INSERT #TEMP VALUES(REPLICATE('d',25000))
INSERT #TEMP VALUES(REPLICATE('e',25000))
SELECT *
FROM #temp
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid
Values in the columns
--------------------------------------
user_objects_alloc_page_count =10
user_objects_dealloc_page_count = 5
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0
TRUNCATE TABLE #temp
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid
Values in the columns
--------------------------------------
user_objects_alloc_page_count =10
user_objects_dealloc_page_count = 10
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0
DROP TABLE #temp1
so those were the use objects now we will talk about internal objects
Internal Objects
Internal objects are only in tempdb. The following objects are included in the internal object page counters:
--Create the tables
CREATE TABLE #temp1(id INT, SomeCol VARCHAR(MAX))
INSERT #TEMP1 VALUES(1,REPLICATE('a',25000))
INSERT #TEMP1 VALUES(2,REPLICATE('b',25000))
INSERT #TEMP1 VALUES(3,REPLICATE('c',25000))
INSERT #TEMP1 VALUES(4,REPLICATE('d',25000))
INSERT #TEMP1 VALUES(5,REPLICATE('e',25000))
CREATE TABLE #temp2(id INT, SomeCol VARCHAR(MAX))
INSERT #TEMP2 VALUES(1,REPLICATE('a',25000))
INSERT #TEMP2 VALUES(2,REPLICATE('b',25000))
INSERT #TEMP2 VALUES(3,REPLICATE('c',25000))
INSERT #TEMP2 VALUES(4,REPLICATE('d',25000))
INSERT #TEMP2 VALUES(5,REPLICATE('e',25000))
--Do a sort operation
SELECT *
FROM#TEMP2 t2
JOIN #TEMP1 t1 ON t1.id = t2.id
ORDER BY t1.id,t1.SomeCol,t2.id,t2.SomeCol
--Check the view
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid
Values in the columns
--------------------------------------
user_objects_alloc_page_count =20
user_objects_dealloc_page_count = 10
internal_objects_alloc_page_count = 16
internal_objects_dealloc_page_count = 8
What is this dmv useful for? You can use it to to help you diagnose and troubleshoot problems caused by insufficient disk space in the tempdb database.
The following error messages indicate insufficient disk space in the tempdb database. These errors can be found in the SQL Server error log, and may also be returned to any running application.
1101 or 1105
Any session must allocate space in tempdb.
3959
The version store is full. This error usually appears after a 1105 or 1101 error in the log.
3967
The version store is forced to shrink because tempdb is full.
3958 or 3966
A transaction cannot find the required version record in tempdb.
Some of these queries that you can use can be found here: Troubleshooting Insufficient Disk Space in tempdb
This is the third dynamic management view that I have covered; a list if all of them and the date that I covered some of them can be found below
http://sqlservercode.blogspot.com/2006/08/dynamic-management-views-blog-post.html
Before we start here are a couple of remarks
IAM pages are not included in any of the allocation or deallocation counts reported by this view.
Page counters are initialized to zero (0) at the start of a session. The counters track the total number of pages that have been allocated or deallocated for tasks that are already completed in the session. The counters are updated only when a task ends; they do not reflect running tasks.
A session can have multiple requests active at the same time. A request can start multiple threads, tasks, if it is a parallel query.
Here is some information about the columns in this view
session_id
smallint
Session ID.
-- session_id maps to session_id in sys.dm_exec_sessions.
database_id
smallint
Database ID.
user_objects_alloc_page_count
bigint
Number of pages reserved or allocated for user objects by this session.
user_objects_dealloc_page_count
bigint
Number of pages deallocated and no longer reserved for user objects by this session.
internal_objects_alloc_page_count
bigint
Number of pages reserved or allocated for internal objects by this session.
internal_objects_dealloc_page_count
bigint
Number of pages deallocated and no longer reserved for internal objects by this session.
First we will talk about user objects
User Objects
The following objects are included in the user object page counters:
- User-defined tables and indexes
- System tables and indexes
- Global temporary tables and indexes
- Local temporary tables and indexes
- Table variables
- Tables returned in the table-valued functions
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid
Values in the columns
--------------------------------------
user_objects_alloc_page_count =0
user_objects_dealloc_page_count = 0
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0
CREATE TABLE #temp(id VARCHAR(MAX))
INSERT #TEMP VALUES(REPLICATE('a',25000))
INSERT #TEMP VALUES(REPLICATE('b',25000))
INSERT #TEMP VALUES(REPLICATE('c',25000))
INSERT #TEMP VALUES(REPLICATE('d',25000))
INSERT #TEMP VALUES(REPLICATE('e',25000))
SELECT *
FROM #temp
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid
user_objects_alloc_page_count =5
user_objects_dealloc_page_count = 0
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0
DROP TABLE #temp
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid
Values in the columns
--------------------------------------
user_objects_alloc_page_count =5
user_objects_dealloc_page_count = 5
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0
CREATE TABLE #temp(id VARCHAR(MAX))
INSERT #TEMP VALUES(REPLICATE('a',25000))
INSERT #TEMP VALUES(REPLICATE('b',25000))
INSERT #TEMP VALUES(REPLICATE('c',25000))
INSERT #TEMP VALUES(REPLICATE('d',25000))
INSERT #TEMP VALUES(REPLICATE('e',25000))
SELECT *
FROM #temp
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid
Values in the columns
--------------------------------------
user_objects_alloc_page_count =10
user_objects_dealloc_page_count = 5
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0
TRUNCATE TABLE #temp
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid
Values in the columns
--------------------------------------
user_objects_alloc_page_count =10
user_objects_dealloc_page_count = 10
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0
DROP TABLE #temp1
so those were the use objects now we will talk about internal objects
Internal Objects
Internal objects are only in tempdb. The following objects are included in the internal object page counters:
- Work tables for cursor or spool operations and temporary large object (LOB) storage
- Work files for operations such as a hash join
- Sort runs
--Create the tables
CREATE TABLE #temp1(id INT, SomeCol VARCHAR(MAX))
INSERT #TEMP1 VALUES(1,REPLICATE('a',25000))
INSERT #TEMP1 VALUES(2,REPLICATE('b',25000))
INSERT #TEMP1 VALUES(3,REPLICATE('c',25000))
INSERT #TEMP1 VALUES(4,REPLICATE('d',25000))
INSERT #TEMP1 VALUES(5,REPLICATE('e',25000))
CREATE TABLE #temp2(id INT, SomeCol VARCHAR(MAX))
INSERT #TEMP2 VALUES(1,REPLICATE('a',25000))
INSERT #TEMP2 VALUES(2,REPLICATE('b',25000))
INSERT #TEMP2 VALUES(3,REPLICATE('c',25000))
INSERT #TEMP2 VALUES(4,REPLICATE('d',25000))
INSERT #TEMP2 VALUES(5,REPLICATE('e',25000))
--Do a sort operation
SELECT *
FROM#TEMP2 t2
JOIN #TEMP1 t1 ON t1.id = t2.id
ORDER BY t1.id,t1.SomeCol,t2.id,t2.SomeCol
--Check the view
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid
Values in the columns
--------------------------------------
user_objects_alloc_page_count =20
user_objects_dealloc_page_count = 10
internal_objects_alloc_page_count = 16
internal_objects_dealloc_page_count = 8
What is this dmv useful for? You can use it to to help you diagnose and troubleshoot problems caused by insufficient disk space in the tempdb database.
The following error messages indicate insufficient disk space in the tempdb database. These errors can be found in the SQL Server error log, and may also be returned to any running application.
1101 or 1105
Any session must allocate space in tempdb.
3959
The version store is full. This error usually appears after a 1105 or 1101 error in the log.
3967
The version store is forced to shrink because tempdb is full.
3958 or 3966
A transaction cannot find the required version record in tempdb.
Some of these queries that you can use can be found here: Troubleshooting Insufficient Disk Space in tempdb
Whole Lotta Rosie By AC/DC or Whole Lotta Love By Led Zeppelin?
I got a new MP3 player from my sister in law for Christmas and have been listening to some good old R & R
So the question is which of these similar named songs do you prefer?
Whole Lotta Rosie by AC/DC with Bon Scott from the album Let There Be Rock
.
Whole Lotta Love by Led Zeppelin from the album Led Zeppelin II
.
Between these two my pick is Whole Lotta Rosie by AC/DC and I think that this is still my favorite AC/DC song.
My favorite Led Zeppelin song is not Whole Lotta Love but one of these
Since I've Been Loving You from Led Zeppelin III
Black Dog from Led Zeppelin IV
Over the Hills and Far Away from Houses of the Holy
Travelling Riverside Blues from the Led Zeppelin Box Set
I am still undecided on the Led Zep favorite. Which is your favorite Led Zep song? And no Stairway to Heaven is not allowed as your pick
So the question is which of these similar named songs do you prefer?
Whole Lotta Rosie by AC/DC with Bon Scott from the album Let There Be Rock
.Whole Lotta Love by Led Zeppelin from the album Led Zeppelin II
.Between these two my pick is Whole Lotta Rosie by AC/DC and I think that this is still my favorite AC/DC song.
My favorite Led Zeppelin song is not Whole Lotta Love but one of these
Since I've Been Loving You from Led Zeppelin III
Black Dog from Led Zeppelin IV
Over the Hills and Far Away from Houses of the Holy
Travelling Riverside Blues from the Led Zeppelin Box Set
I am still undecided on the Led Zep favorite. Which is your favorite Led Zep song? And no Stairway to Heaven is not allowed as your pick
Thursday, December 21, 2006
Display 1 When Any Column Is 1 And 0 Otherwise by Using SIGN, SUM Or (Bitwise OR)
You have three columns; if any of the columns is 1 you want to display 1 if they are all 0 you want to display 0. how can you do that?
there are basically three ways to do this
1 SUM
2 SIGN
3 | (Bitwise OR)
Here are the examples
--create table and insert rows
CREATE TABLE #test (test_A INT,test_B INT,test_C INT)
INSERT INTO #test VALUES(0,0,0)
INSERT INTO #test VALUES(1,0,0)
INSERT INTO #test VALUES(0,1,0)
INSERT INTO #test VALUES(0,0,1)
INSERT INTO #test VALUES(0,1,1)
INSERT INTO #test VALUES(1,1,1)
First we will display 0 if all columns are 0; if any of the columns is 1 then we will display a one
--Using SIGN
SELECT SIGN(test_A+test_B+test_C) ,*
FROM #test
--Using (Bitwise OR)
SELECT (test_A | test_B | test_C),*
FROM #test
--Using Sum
SELECT CASE SUM(test_A + test_B + test_C)
WHEN 0 THEN 0 ELSE 1 END, *
FROM #test
GROUP BY test_A , test_B , test_C
Now let's say we want to display 1 if all the values are 1.
we can do that by using &; this is the Bitwise AND operator
--Using (Bitwise AND)
SELECT (test_A & test_B & test_C),*
FROM #test
-- SUM can also be used
SELECT CASE SUM(test_A + test_B + test_C)
WHEN 3 THEN 1 ELSE 0 END, *
FROM #test
GROUP BY test_A , test_B , test_C
Now let's reverse the process and display 0 if any of the columns are 1 and 1 if all the columns are 0.
We have to use CASE to accomplish this
--Using CASE with SIGN
SELECT CASE SIGN(test_A+test_B+test_C)
WHEN 1 THEN 0 ELSE 1 END, *
FROM #test
--Using CASE with Bitwise OR
SELECT CASE (test_A | test_B | test_C)
WHEN 1 THEN 0 ELSE 1 END, *
FROM #test
--Using SUM with CASE
SELECT CASE SUM(test_A + test_B + test_C)
WHEN 0 THEN 1 ELSE 0 END, *
FROM #test
GROUP BY test_A , test_B , test_C
Here is some additional stuff, add this row and run the queries again
INSERT INTO #test VALUES(1,NULL,1)
What happens when you run this?
SELECT SIGN(test_A+test_B+test_C) ,*
FROM #test
A NULL is returned; If you have NULLS in your table then you might want to use COALESCE
SELECT SIGN(test_A+COALESCE(test_B,0)+test_C) ,*
FROM #test
there are basically three ways to do this
1 SUM
2 SIGN
3 | (Bitwise OR)
Here are the examples
--create table and insert rows
CREATE TABLE #test (test_A INT,test_B INT,test_C INT)
INSERT INTO #test VALUES(0,0,0)
INSERT INTO #test VALUES(1,0,0)
INSERT INTO #test VALUES(0,1,0)
INSERT INTO #test VALUES(0,0,1)
INSERT INTO #test VALUES(0,1,1)
INSERT INTO #test VALUES(1,1,1)
First we will display 0 if all columns are 0; if any of the columns is 1 then we will display a one
--Using SIGN
SELECT SIGN(test_A+test_B+test_C) ,*
FROM #test
--Using (Bitwise OR)
SELECT (test_A | test_B | test_C),*
FROM #test
--Using Sum
SELECT CASE SUM(test_A + test_B + test_C)
WHEN 0 THEN 0 ELSE 1 END, *
FROM #test
GROUP BY test_A , test_B , test_C
Now let's say we want to display 1 if all the values are 1.
we can do that by using &; this is the Bitwise AND operator
--Using (Bitwise AND)
SELECT (test_A & test_B & test_C),*
FROM #test
-- SUM can also be used
SELECT CASE SUM(test_A + test_B + test_C)
WHEN 3 THEN 1 ELSE 0 END, *
FROM #test
GROUP BY test_A , test_B , test_C
Now let's reverse the process and display 0 if any of the columns are 1 and 1 if all the columns are 0.
We have to use CASE to accomplish this
--Using CASE with SIGN
SELECT CASE SIGN(test_A+test_B+test_C)
WHEN 1 THEN 0 ELSE 1 END, *
FROM #test
--Using CASE with Bitwise OR
SELECT CASE (test_A | test_B | test_C)
WHEN 1 THEN 0 ELSE 1 END, *
FROM #test
--Using SUM with CASE
SELECT CASE SUM(test_A + test_B + test_C)
WHEN 0 THEN 1 ELSE 0 END, *
FROM #test
GROUP BY test_A , test_B , test_C
Here is some additional stuff, add this row and run the queries again
INSERT INTO #test VALUES(1,NULL,1)
What happens when you run this?
SELECT SIGN(test_A+test_B+test_C) ,*
FROM #test
A NULL is returned; If you have NULLS in your table then you might want to use COALESCE
SELECT SIGN(test_A+COALESCE(test_B,0)+test_C) ,*
FROM #test
Wednesday, December 20, 2006
SQL Server 2005 Will Be Supported On Vista And Windows Server Longhorn
Don't get scared of those people at Badvista.org
Microsoft announced on September 27, 2006 that SQL Server 2005 would be supported on Vista This is just more FUD from Stallman & Co. However you must have service Pack 2 installed for it to run. From the site:
You can read the whole story here
You can test SQL Server 2005 Service Pack 2 - Community Technology Preview (CTP) December 2006 right now
Microsoft announced on September 27, 2006 that SQL Server 2005 would be supported on Vista This is just more FUD from Stallman & Co. However you must have service Pack 2 installed for it to run. From the site:
"In an effort to provide customers with more secure products, Microsoft Windows Server "Longhorn" and Microsoft Windows Vista will only support SQL Server 2005 Service Pack 2 (SP2) or later when it becomes available. Earlier versions of SQL Server, including SQL Server 2000 (all editions including Desktop Engine edition, a.k.a MSDE), SQL Server 7.0, and SQL Server 6.5, will not be supported on Windows Server "Longhorn" or Windows Vista. Customers running applications with these earlier versions of SQL Server should consider evaluating and upgrading to SQL Server 2005, which was designed to take advantage of the upcoming security and performance enhancements in the operating environment."
You can read the whole story here
You can test SQL Server 2005 Service Pack 2 - Community Technology Preview (CTP) December 2006 right now
SQL Server 2005 Service Pack 2 - Community Technology Preview (CTP) December 2006 Available For Download
That's right it's here. Download it now and play around with vardecimal and custom reports for Management Studio
http://www.microsoft.com/downloads/details.aspx?FamilyID=d2da6579-d49c-4b25-8f8a-79d14145500d&DisplayLang=en
http://www.microsoft.com/downloads/details.aspx?FamilyID=d2da6579-d49c-4b25-8f8a-79d14145500d&DisplayLang=en
Tuesday, December 19, 2006
How Well Do You Interview And Do You Use Wizard Driven Programming?
We were interviewing people for developer and architect positions. The developer position required about 5 years C# and SQL Server. We got a couple of good candidates but some of them were really terrible. They claim SQL server experience since 1998 but don't know what a clustered index is or they can not name even one isolation level. There were people who had 10 years experience in web development but could not explain what MVC (Model-view-controller) is or name a design pattern. People created webservices but did not know what a WSDL is?????
You see this is the problem I have with WDP (Wizard Driven Programming); It's okay to use wizards but you HAVE to know what is going on behind the scenes. It's okay to use Enterprise Manager but you have to know how to do the same thing in Query Analyzer. If you never write your own scripts you will sooner or later come to a shop where they don't use these tools and changes have to be submitted via scripts. Not only do you have to script everything you also have to provide back-out scripts in case something goes wrong.
So here are some of the questions I asked; with 5 years experience I think you should not miss more than two (probably traceflag 1204 and parameter sniffing questions)
Let me know how many you couldn't get without looking them up
What is normalization
What is the fastest way to empty a table
what is a deadlock
Can you give an example of creating a deadlock
How do you detect deadlocks
What is an audit trail
what is an identity column
How do you return an identity value from a table
How do you return an identity value from a table with a trigger
How many bytes can you fit in a row, do you know why
What is a clustered index
How many clustered indexes per table
How many nonclustered indexes per table
what is an execution plan
Between index scan, index seek and table scan; which one is fastest and which one is slowest
How do you return a value from a proc
How do you return a varchar value from a proc
If I have a column that will only have values between 1 and 250 what data type should I use
How do you enforce that only values between 1 and 10 are allowed in a column
How to check for a valid date
Which date format is the only safe one to use when passing dates as strings
How do you suppress rows affected messages when executing an insert statement
Can you name the 4 isolation levels in SQL Server 2000
How would you select all last names that start with S
How would you select all rows where the date is 20061127
What is horizontal partitioning
What does schemabinding do
How do you test for nulls
Name some differences between isnull and coalesce
What is a temp table
what is the difference between a local and global temporary table
If you create a local temp table and then call a proc is the temp table available inside the proc
What is referential integrity
what is the fastest way to populate a table (performance wise)
using the method above what can you do to make it even faster
What data type should you use to store monetary values
What is a cascade delete
Name a couple of types of joins
What is a SQL injection
What is parameter sniffing
Name 2 differences between a primary key and UNIQUE Constraints
How do you ensure that SQL server will use an index
What does option fast (10) do
What is the difference between union and union all
What does trace flag 1204 do
You see this is the problem I have with WDP (Wizard Driven Programming); It's okay to use wizards but you HAVE to know what is going on behind the scenes. It's okay to use Enterprise Manager but you have to know how to do the same thing in Query Analyzer. If you never write your own scripts you will sooner or later come to a shop where they don't use these tools and changes have to be submitted via scripts. Not only do you have to script everything you also have to provide back-out scripts in case something goes wrong.
So here are some of the questions I asked; with 5 years experience I think you should not miss more than two (probably traceflag 1204 and parameter sniffing questions)
Let me know how many you couldn't get without looking them up
What is normalization
What is the fastest way to empty a table
what is a deadlock
Can you give an example of creating a deadlock
How do you detect deadlocks
What is an audit trail
what is an identity column
How do you return an identity value from a table
How do you return an identity value from a table with a trigger
How many bytes can you fit in a row, do you know why
What is a clustered index
How many clustered indexes per table
How many nonclustered indexes per table
what is an execution plan
Between index scan, index seek and table scan; which one is fastest and which one is slowest
How do you return a value from a proc
How do you return a varchar value from a proc
If I have a column that will only have values between 1 and 250 what data type should I use
How do you enforce that only values between 1 and 10 are allowed in a column
How to check for a valid date
Which date format is the only safe one to use when passing dates as strings
How do you suppress rows affected messages when executing an insert statement
Can you name the 4 isolation levels in SQL Server 2000
How would you select all last names that start with S
How would you select all rows where the date is 20061127
What is horizontal partitioning
What does schemabinding do
How do you test for nulls
Name some differences between isnull and coalesce
What is a temp table
what is the difference between a local and global temporary table
If you create a local temp table and then call a proc is the temp table available inside the proc
What is referential integrity
what is the fastest way to populate a table (performance wise)
using the method above what can you do to make it even faster
What data type should you use to store monetary values
What is a cascade delete
Name a couple of types of joins
What is a SQL injection
What is parameter sniffing
Name 2 differences between a primary key and UNIQUE Constraints
How do you ensure that SQL server will use an index
What does option fast (10) do
What is the difference between union and union all
What does trace flag 1204 do
Sunday, December 17, 2006
BadVista: The Worst FUD You Will Ever Read.... St. Ignucious Has Gone Wild
Richard Stallman (RMS, St. Ignucious ) is at it again. let's see what the site says:
More interesting is the piece of garbage that they link to: 25 Shortcomings Of Vista
Here are some of the 'gems'
1. SMB2: Vista introduces a new variant of the SMB protocol called SMB2, which may pose problems for those connecting to non-Microsoft networks, such as Samba on Linux.
--Speculation and pure FUD
7. Five Versions: The array of Vista editions could prove to be three too many, and upgrades between versions remain an unknown.
8. Activation: The need to activate the product via the Web could prove to be a time-waster during mass deployments.
--Could prove? Could prove? Who writes this stuff? For mass deployments there is something called Key Management Service
Key Management Service
Your organization can host the Key Management Service (KMS) internally to automatically activate computers running Windows Vista. To use the KMS, you must have a minimum of 25 computers running Windows Vista that are connected together. Computers that have been activated through KMS will be required to reactivate by connecting to your organization's network at least every six months.
Currently the KMS software runs on a local computer running Windows Vista or the Microsoft Windows Server Code Name "Longhorn" operating system. In the future, it will run on the Microsoft Windows Server 2003 operating system.
9. Storage Space: With Vista taking as much as 10 Gbytes of hard drive space, big and fast hard drives will be a must.
--Last time I checked you can't really buy anything less than 100GB
10. Backup: See No. 9. Backing up desktops will take a great deal of space.
--So backup only your personal data.
18
Buried Controls
Many options and controls are further buried, requiring a half-dozen mouse clicks or more to get to. Network settings and display settings are offenders here.
--Some people said the same thing in WinXP, until they realized there is also a classic view. Vista has this classic view also.
19. Installation Can take hours on some systems. Upgrades are even slower.
--This is BS; it should not take more than 30 minutes unless you got a pirated copy on 1500 floppy discs
20. HHD Hybrid Hard Drives. These are potentially a huge performance booster, but there's little information and support is available (even though should be available).
--Since when is having an emerging technology that will boost performance a shortcoming?
#25:
WordPadAbility to open .doc files has been removed.
--Hahahahahaha who wrote this piece of crap. You either use Word or OpenOffice
The writer of this article is Frank J. Ohlhorst send him an email if you want to tell him what you think
The Free Software Foundation (FSF) today launched BadVista.org, a campaign
with a twofold mission of exposing the harms inflicted on computer users by the
new Microsoft Windows Vista and promoting free software alternatives that
respect users' security and privacy rights.
“Vista is an upsell masquerading
as an upgrade. It is an overall regression when you look at the most important
aspect of owning and using a computer: your control over what it does. Obviously
MS Windows is already proprietary and very restrictive, and well worth
rejecting. But the new 'features' in Vista are a Trojan Horse to smuggle in even
more restrictions. We'll be focusing attention on detailing how they work, how
to resist them, and why people should care”, said FSF program administrator John
Sullivan.
More interesting is the piece of garbage that they link to: 25 Shortcomings Of Vista
Here are some of the 'gems'
1. SMB2: Vista introduces a new variant of the SMB protocol called SMB2, which may pose problems for those connecting to non-Microsoft networks, such as Samba on Linux.
--Speculation and pure FUD
7. Five Versions: The array of Vista editions could prove to be three too many, and upgrades between versions remain an unknown.
8. Activation: The need to activate the product via the Web could prove to be a time-waster during mass deployments.
--Could prove? Could prove? Who writes this stuff? For mass deployments there is something called Key Management Service
Key Management Service
Your organization can host the Key Management Service (KMS) internally to automatically activate computers running Windows Vista. To use the KMS, you must have a minimum of 25 computers running Windows Vista that are connected together. Computers that have been activated through KMS will be required to reactivate by connecting to your organization's network at least every six months.
Currently the KMS software runs on a local computer running Windows Vista or the Microsoft Windows Server Code Name "Longhorn" operating system. In the future, it will run on the Microsoft Windows Server 2003 operating system.
9. Storage Space: With Vista taking as much as 10 Gbytes of hard drive space, big and fast hard drives will be a must.
--Last time I checked you can't really buy anything less than 100GB
10. Backup: See No. 9. Backing up desktops will take a great deal of space.
--So backup only your personal data.
18
Buried Controls
Many options and controls are further buried, requiring a half-dozen mouse clicks or more to get to. Network settings and display settings are offenders here.
--Some people said the same thing in WinXP, until they realized there is also a classic view. Vista has this classic view also.
19. Installation Can take hours on some systems. Upgrades are even slower.
--This is BS; it should not take more than 30 minutes unless you got a pirated copy on 1500 floppy discs
20. HHD Hybrid Hard Drives. These are potentially a huge performance booster, but there's little information and support is available (even though should be available).
--Since when is having an emerging technology that will boost performance a shortcoming?
#25:
WordPadAbility to open .doc files has been removed.
--Hahahahahaha who wrote this piece of crap. You either use Word or OpenOffice
The writer of this article is Frank J. Ohlhorst send him an email if you want to tell him what you think
Friday, December 15, 2006
Three Ways To Display Two Counts From a Table Side By Side
This question came up on tek-tips.
I want to count the CA and the UT states from the authors table and output this in different columns named CaCount and UtCount
CaCount UtCount
----------- -----------
15 2
How do you do such a thing? To display the following is very easy
state StateCount
----- -----------
CA 15
UT 2
You just do a count and group by
SELECT state,COUNT(*) AS StateCount
FROM authors
WHERE state in ('CA', 'UT')
GROUP BY state
How do we display them side by side? We can do this 3 different ways
1 stick the count in variables
2 run the count twice a s a separate query
3 sum with case
USE PUBS
GO
--stick the count in variables
DECLARE @UtCount INT
DECLARE @CaCount INT
SELECT @UtCount = COUNT(*) FROM authors WHERE state ='UT'
SELECT @CaCount = COUNT(*) FROM authors WHERE state ='CA'
SELECT @CaCount AS CaCount,@UtCount AS UtCount
--run the count twice as a separate query
SELECT (SELECT COUNT(*) FROM authors
WHERE state ='CA') AS CaCount,
(SELECT COUNT(*) FROM authors WHERE state ='UT') AS UtCount
--sum with case
SELECT SUM(CASE state WHEN 'CA' THEN 1 ELSE 0 END) AS CaCount,
SUM(CASE state WHEN 'UT' THEN 1 ELSE 0 END) AS UtCount
FROM authors
If you look at the execution plan then you will see that the best option is the third one (sum with case). The third option only queries the table once instead of twice like the other two solutions and will thus be twice as fast
So keep that in mind when you have to do such a query next time
[edit]
Oracle Version
jbjornson left the Oracle version in a comment; I decided to add it to the post. I don't know about the syntax coloring on Oracle/TOAD so you are getting a black and white only ;-(
--run the count twice as a separate query
SELECT CA.caCount, UT.UtCount
FROM (SELECT COUNT(*) as CaCount FROM authors) WHERE state ='CA') CA,
(SELECT COUNT(*) FROM authors WHERE state ='UT') UT
--sum with case
SELECT SUM(decode(state, 'CA', 1, 0)) AS CaCount,
SUM(decode(state, 'UT', 1, 0)) AS UtCount
FROM authors
[/edit]
I want to count the CA and the UT states from the authors table and output this in different columns named CaCount and UtCount
CaCount UtCount
----------- -----------
15 2
How do you do such a thing? To display the following is very easy
state StateCount
----- -----------
CA 15
UT 2
You just do a count and group by
SELECT state,COUNT(*) AS StateCount
FROM authors
WHERE state in ('CA', 'UT')
GROUP BY state
How do we display them side by side? We can do this 3 different ways
1 stick the count in variables
2 run the count twice a s a separate query
3 sum with case
USE PUBS
GO
--stick the count in variables
DECLARE @UtCount INT
DECLARE @CaCount INT
SELECT @UtCount = COUNT(*) FROM authors WHERE state ='UT'
SELECT @CaCount = COUNT(*) FROM authors WHERE state ='CA'
SELECT @CaCount AS CaCount,@UtCount AS UtCount
--run the count twice as a separate query
SELECT (SELECT COUNT(*) FROM authors
WHERE state ='CA') AS CaCount,
(SELECT COUNT(*) FROM authors WHERE state ='UT') AS UtCount
--sum with case
SELECT SUM(CASE state WHEN 'CA' THEN 1 ELSE 0 END) AS CaCount,
SUM(CASE state WHEN 'UT' THEN 1 ELSE 0 END) AS UtCount
FROM authors
If you look at the execution plan then you will see that the best option is the third one (sum with case). The third option only queries the table once instead of twice like the other two solutions and will thus be twice as fast
So keep that in mind when you have to do such a query next time
[edit]
Oracle Version
jbjornson left the Oracle version in a comment; I decided to add it to the post. I don't know about the syntax coloring on Oracle/TOAD so you are getting a black and white only ;-(
--run the count twice as a separate query
SELECT CA.caCount, UT.UtCount
FROM (SELECT COUNT(*) as CaCount FROM authors) WHERE state ='CA') CA,
(SELECT COUNT(*) FROM authors WHERE state ='UT') UT
--sum with case
SELECT SUM(decode(state, 'CA', 1, 0)) AS CaCount,
SUM(decode(state, 'UT', 1, 0)) AS UtCount
FROM authors
[/edit]
Visual Studio 2005 Service Pack 1 (SP1) Released
Visual Studio 2005 Service Pack 1 (SP1) has been released. From the site:
Get it here
Service Pack 1 addresses issues that were found through a combination of customers and partner feedback, as well as internal testing. The issues addressed range in severity from places where the syntax coloring was incorrect to customer reported crashes in various scenarios. In some areas, more than 50% of the bugs addressed were reported by customers through the MSDN Product Feedback Center and Microsoft Connect. Overall, Service Pack 1 offers customers improvements in responsiveness, stability and performance for Visual Studio 2005.
Through further advancement and feedback, Service Pack 1 also provides over 70 improvements for common development scenarios including:
- New processor support (e.g., Core Duo) for code generation and profiling
- Performance and scale improvements in Team Foundation Server
- Team Foundation Server integration with Excel 2007 and Project 2007
- Tool support for occasionally connected devices and SQL Server Compact Edition
- Additional support for project file based Web applications
- Windows Embedded 6.0 platform and tools support
Get it here
Wednesday, December 13, 2006
Index Build Strategy In SQL Server
The Tips, Tricks, and Advice from the SQL Server Query Processing Team blog has 3 really good posts about Index Build strategy in SQL Server. You should definitely read these to really understand how to implement that. Here are the links:
Index Build strategy in SQL Server - Part 1: offline, serial, no partitioning
Index Build strategy in SQL Server - Part 2: Offline, Parallel, No Partitioning
Index Build strategy in SQL Server - Part 2: Offline, Parallel, No Partitioning (Non stats plan (no histogram))
Index Build strategy in SQL Server - Part 1: offline, serial, no partitioning
Index Build strategy in SQL Server - Part 2: Offline, Parallel, No Partitioning
Index Build strategy in SQL Server - Part 2: Offline, Parallel, No Partitioning (Non stats plan (no histogram))
Problems With ROW_NUMBER() In A WHERE Clause
This is a question that came up today. A person had a query like this
USE AdventureWorks
GO
SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address
WHERE rowNum > 3;
That gave the following error
Server: Msg 207, Level 16, State 1, Line 6
Invalid column name 'rowNum'.
What you have to do is use Common Table Expressions or use a subquery. Below are both methods.
--Subquery
SELECT * FROM ( SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address) AS x
WHERE rowNum > 3;
--CTE
WITH x (rowNum,addressline1,
city) AS
(SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address)
SELECT * FROM X
WHERE rowNum > 3;
USE AdventureWorks
GO
SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address
WHERE rowNum > 3;
That gave the following error
Server: Msg 207, Level 16, State 1, Line 6
Invalid column name 'rowNum'.
What you have to do is use Common Table Expressions or use a subquery. Below are both methods.
--Subquery
SELECT * FROM ( SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address) AS x
WHERE rowNum > 3;
--CTE
WITH x (rowNum,addressline1,
city) AS
(SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address)
SELECT * FROM X
WHERE rowNum > 3;
Tuesday, December 12, 2006
SQL Server 2005 Practical Troubleshooting: The Database Engine Is Shipping!
Amazon is shipping SQL Server 2005 Practical Troubleshooting: The Database Engine. This is a must have for all SQL developers who run into any kind of problems with SQL Server 2005For all of you who did not read my interview with the editor of this book Ken Henderson; you can find that here; Ken describes the target audience for the book the most important chapters for customers who have performance problems and much more
A sample chapter is available: Chapter 4 Procedure Cache Issues
About the book:
Paperback: 456 pages
Publisher: Addison-Wesley; 1ST edition
Language: English
ISBN: 0321447743
Contents
Preface
Chapter 1 Waiting and Blocking Issues
Chapter 2 Data Corruption and Recovery Issues
Chapter 3 Memory Issues
Chapter 4 Procedure Cache Issues
Chapter 5 Query Processor Issues
Chapter 6 Server Crashes and Other Critical Failures
Chapter 7 Service Broker Issues
Chapter 8 SQLOS and Scheduling Issues
Chapter 9 Tempdb Issues
Chapter 10 Clustering Issues
Index
Amazon Links:
Visual Studio Team Edition for Database Professionals links
By now you know that Visual Studio Team Edition for Database Professionals has been released. But do you know all the links to the newsgroup, documentation and download page? No? No problem here are the links:
Download Link
The product is available on the MSDN subscribers site, or you can download the trial version from he following URL :http://www.microsoft.com/downloads/details.aspx?FamilyId=7DE00386-893D-4142-A778-992B69D482AD&displaylang=en
For the trial version, you still need an installed copy of Visual Studio 2005 Team Suite which can be either a trial or a licensed version
Documentation
Team Edition for Database Professionals
Forum
Visual Studio Team System - Database Professionals
Visual Studio 2005 Team Edition for Database Professionals Site
Data Dude
Download Link
The product is available on the MSDN subscribers site, or you can download the trial version from he following URL :http://www.microsoft.com/downloads/details.aspx?FamilyId=7DE00386-893D-4142-A778-992B69D482AD&displaylang=en
For the trial version, you still need an installed copy of Visual Studio 2005 Team Suite which can be either a trial or a licensed version
Documentation
Team Edition for Database Professionals
Forum
Visual Studio Team System - Database Professionals
Visual Studio 2005 Team Edition for Database Professionals Site
Data Dude
Sunday, December 10, 2006
Wedding Anniversary
So here we are 9 years later. We are still married have 3 kids and bought our second home this year. So let me give you a little background of how I met my wife etc etc. I met my wife in a bar on the upper eastside in New York City. I don’t remember the name of the bar but I do remember that we went to Elaine’s for a drink and also to a place named the Cowboy Bar. Fast forward to the wedding date; we got married twice first at City Hall on December 11th and then we did a church wedding on August 15th (the pictures are from that day) We did the December wedding separately so that we could start my Green Card process; my wife is a third generation Italian-American but I had Dutch and Croatian citizenships.
The wedding was 1 block from were we lived. My wife (to be) spent the night in a hotel with her sisters. The wedding started at 3; I was still rollerblading till 2:30 in Central Park. I took a shower got dressed and walked to the church (which was 1 block from my apartment)
5% of the wedding guests did jail time.
The reception was held at the Kitano Hotel which is located at 38th and Park Avenue. I did not have a huge wedding only 65 or so family members and some of our closest friends were invited. Out of those people there were 3 who did some jail time. One of them is Brendan who was the head of the NYC Hells Angels chapter at that time.
Another was brought by a friend and the last one was a friend of mine who did jail time because he beat up a cop. The Hells Angel did not dress up of course, if you look at the picture you can see me next to my wife. The other picture is me and my wife on the roof. The reception had 2 huge balconies; one was facing the Empire State Building and the other one was facing the Chrysler Building. The good thing when having your wedding at a hotel is that you can just walk to your room.
Honeymoon in Hawaii
So after the reception we had two days to get ready for the honeymoon. We did our shopping and on Monday night we went to China Club with out friend from Europe. China Club is the place to go to on Mondays; you can always spot some model or one of the Yankees or Knicks there. The next day we went to Maui and Kuai. I have said many times to my wife that I want to go back to Hawaii once before I die…. Maui and Kuai were beautiful places to spend your honeymoon; if you are into playing golf, surfing, hiking, snorkeling then there are lots of things to do here. Jurassic Park was shot in Kuai and once you arrive in Kuai you will recognize the green scenery right away
After the honeymoon
Once you are married don’t have kids right away; your live will change and you won’t be able to do anything for a while. So that is what we did; we waited 4 years to have kids. We visited Paris and Amsterdam and bought a house near Princeton before having our first son Christian. Then my wife became pregnant with twins and we had to sell our house and buy a bigger house. If owing you house is the American Dream then selling and buying your next house has to be the American Nightmare; what a stressful situation. After that ordeal I said that the next time I move will be in one of those 55+ community houses (I still have about 20 years to go). So today we live in Princeton and we have Catherine and Nicholas to keep us awake at night.
Traditions
My wife and I usually buy our Christmas tree on our anniversary. In the city we would pick up a Charlie Brown on 2nd avenue and 79th street; out here we go to the farm and chop down our own. Somehow my wife thinks it is also a tradition to watch Serendipity; if I have to watch a movie with Kate Beckingsale I prefer Underworld but hey you have to be nice on your anniversary….
BTW I still fit in my wedding suit, the tailor was wrong ;-(
Friday, December 08, 2006
Plan For The Day…Gone Wrong In a Big Way
It is my 9th year wedding anniversary this weekend so I took the day off today and below is a list of things I planned to do today
[ ] Get a Christmas tree at the farm
[ ] Go for breakfast with the whole family
[ ] Drop of a package at the post office
[X] Interview a person for a position at work
[ ] Enhance a Stored Procedure and close the item in BugTrax
[ ] Watch Serendipity (my wife insists)
[ ] Get haircuts
[X] Cover the furniture in the garden
As you can see I managed to accomplish two things only so far. What happened? My son got sick and had a high fever and threw up this morning; that eliminated the first 3 items right away. I managed to do the phone interview because it was a reversed phone interview (the candidate was at the office and I interviewed him from home over the phone)
Forget about the haircuts (I rescheduled for tomorrow); where do I leave our 3 kids?
So that leaves me with watching Serendipity tonight ;-(
Anyway I will post something about my wedding this weekend. Believe it or not 5% of the people that came to my wedding did jail time; one of them was the head of the NYC Hells Angels chapter( I will also post some pictures this weekend)
Oh you wanted some SQL material? No problem this will keep you busy for a while ;-)
Log Buffer #21: A Carnival of the Vanities for DBAs
Log Buffer #22: A Carnival of the Vanities for DBAs
[ ] Get a Christmas tree at the farm
[ ] Go for breakfast with the whole family
[ ] Drop of a package at the post office
[X] Interview a person for a position at work
[ ] Enhance a Stored Procedure and close the item in BugTrax
[ ] Watch Serendipity (my wife insists)
[ ] Get haircuts
[X] Cover the furniture in the garden
As you can see I managed to accomplish two things only so far. What happened? My son got sick and had a high fever and threw up this morning; that eliminated the first 3 items right away. I managed to do the phone interview because it was a reversed phone interview (the candidate was at the office and I interviewed him from home over the phone)
Forget about the haircuts (I rescheduled for tomorrow); where do I leave our 3 kids?
So that leaves me with watching Serendipity tonight ;-(
Anyway I will post something about my wedding this weekend. Believe it or not 5% of the people that came to my wedding did jail time; one of them was the head of the NYC Hells Angels chapter( I will also post some pictures this weekend)
Oh you wanted some SQL material? No problem this will keep you busy for a while ;-)
Log Buffer #21: A Carnival of the Vanities for DBAs
Log Buffer #22: A Carnival of the Vanities for DBAs
Thursday, December 07, 2006
Get The Domain Name Of Your SQL Server Machine
How do you get the domain name that your SQL Server machine belongs to with T-SQL code?
You can use the undocumented procedure xp_regread; I have tested this on SQL Server 2005 and on SQL Server 2000.
Be advised that since this is an undocumented procedure that it might not be available in the future or it might be changed either because of a service pack or a hot fix. Don't use it as production code!
Here is how to do this
DECLARE @chvDomainName NVARCHAR(100)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon',
N'CachePrimaryDomain',@chvDomainName OUTPUT
SELECT @chvDomainName AS DomainName
You can use the undocumented procedure xp_regread; I have tested this on SQL Server 2005 and on SQL Server 2000.
Be advised that since this is an undocumented procedure that it might not be available in the future or it might be changed either because of a service pack or a hot fix. Don't use it as production code!
Here is how to do this
DECLARE @chvDomainName NVARCHAR(100)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon',
N'CachePrimaryDomain',@chvDomainName OUTPUT
SELECT @chvDomainName AS DomainName
Monday, December 04, 2006
Visual Studio 2005 Team Edition for Database Professionals Released to Manufacturing
On Friday 1st December 2006 the development team officially announced that Data Dude was done and was being released to manufacturing. It should be available for download to MSDN subscribers on December 7th 2006 and generally available for purchase on January 1st 2007.
This release marks the end of the first leg in a very exciting journey to revolutionize the ALM space and finally bring the database into the application lifecycle.
You can find more info about Data Dude here: Visual Studio Team Edition for Database Professionals
This release marks the end of the first leg in a very exciting journey to revolutionize the ALM space and finally bring the database into the application lifecycle.
You can find more info about Data Dude here: Visual Studio Team Edition for Database Professionals
Sunday, December 03, 2006
Top 5 Posts For November 2006
Below are the top 5 posts according to Google Analytics for the month of November 2006 in order by pageviews descending
How Are Dates Stored In SQL server?
SQL Query Optimizations
Check If Temporary Table Exists
SQL Server 2005 Script Repository
Five Ways To Return Values From Stored Procedures
How Are Dates Stored In SQL server?
SQL Query Optimizations
Check If Temporary Table Exists
SQL Server 2005 Script Repository
Five Ways To Return Values From Stored Procedures
Top SQL Server Google Searches For November 2006
These are the top SQL Searches on this site for the month of November. this time I left some of the searches that have nothing to do with SQL Server or programming (Like breeds of cats that produce less FEL D1s)
Here is the list:
apprentice sample narrative report in bank
generate table script
breeds of cats that produce less FEL D1
CSV import MS SQL ASP
gravelines
case in sql server 2005
if condition in sql server 2005
search string in a string sql server
sql 2000 olap range
TOP DOWNLOAD SOFTWARE
169.254.x.x
DATE CONVERSIONS
sp_send_dbmail
As you can see if and case are frequent searches. Just yesterday I answered a case question; the poster had this code
set t7 =
case
when (t4 is null and t5 is null) then cast(0 as varchar)
when t4 is null then cast(t5 as varchar) else cast(t4 as varchar)
end
Which of course can be replaced with
set t7 =coalesce(t4,t5,0)
This will return the first non non value, since t7 is a varchar column there is no reason to convert to varchar either
Here is the list:
apprentice sample narrative report in bank
generate table script
breeds of cats that produce less FEL D1
CSV import MS SQL ASP
gravelines
case in sql server 2005
if condition in sql server 2005
search string in a string sql server
sql 2000 olap range
TOP DOWNLOAD SOFTWARE
169.254.x.x
DATE CONVERSIONS
sp_send_dbmail
As you can see if and case are frequent searches. Just yesterday I answered a case question; the poster had this code
set t7 =
case
when (t4 is null and t5 is null) then cast(0 as varchar)
when t4 is null then cast(t5 as varchar) else cast(t4 as varchar)
end
Which of course can be replaced with
set t7 =coalesce(t4,t5,0)
This will return the first non non value, since t7 is a varchar column there is no reason to convert to varchar either
Subscribe to:
Posts (Atom)