Category Archives: Blog

Articles related to this Blog

Object Explorer Details (Part I) – Underappreciated features of Microsoft SQL Server

As mentioned in my previous post, I am currently developing a series of brief articles on the many underappreciated features of Microsoft SQL Server as received from the community as a response to Andy Warren’s SQLServerCentral.com’s editorial (http://www.sqlservercentral.com/articles/Editorial/71788/), which was published on December 17, 2010.

We will start this journey by looking a few of the features that came out with the SQL Server Management Studio 2008. After login, the first feature that almost everyone sees and uses is the Object Explorer, but very few use the Object Explorer Details window.

The Object Explorer Details Window

Writing this article is nostalgia for me. The first Microsoft SQL Server version that I worked upon (more like studied upon) was Microsoft SQL Server 7.0. In fact, I still have one of those installed on a virtual machine at home. When you login to the Microsoft SQL Server 7.0 Enterprise Manager, you can graphically see:

Database information:	Table & Index usage:

Space Utilization:

This continued through Microsoft SQL Server 2000, but with the redesign in Microsoft SQL Server 2005, the Object Explorer lost it’s charm a bit. The Object Explorer and the Object Explorer details window were almost the same and there was no real point to using the Object Explorer Details window. All this changed in Microsoft SQL Server 2008. The Object Explorer Details window is useful once again and here’s how.

First of all, how does one launch Object Explorer in SQL Server 2008? It’s very simple. One can simply navigate to View –> Object Explorer Details, or hit the (F7) key. Follow the series of screenshots as I drill down to the database level information (images have been cropped to emphasize on the details):

Step 01: Object Explorer Base State	Step 02: Database Information

As you can see, the Database level information is already much more detailed than what SQL Server 7.0 could provide. Once you have drilled down onto the database level, you can get a host of other column options by simply right-clicking the column header. Once you have chosen the columns that you want to look at, you can rearrange them just as you would do to any other grid layout:

Column Chooser	An alternate

You can now drill down all the way to the tables and index details. For more fun, try using the Object Explorer to navigate through the database schema, and notice how the Object Explorer Details window stays synchronized with your movements!

Table details	Index details

As you can see, the Object Explorer in Microsoft SQL Server 2008 is a fully loaded, action-packed feature, very useful for developers, quality assurance engineers and administrators alike. I hope that you will immediately launch your SQL Server Management Studio instance and start playing around with the Object Explorer details window.

In the next part, I will be demonstrating how to search for objects using the Object Explorer details – a feature that was never available before Microsoft SQL Server 2008.

Have a good day!

Be courteous. Drive responsibly.

Executing Queries against linked servers

Step 01: Add a Microsoft SQL Server 2005 linked server

USE master;
GO
EXEC sp_addlinkedserver 
   'VPCW2K3',
   N'SQL Server'
GO

Step 02: Write a stored procedure that builds and executes a dynamic SQL statement

Here is the stored procedure that I wrote as a POC to help them out:

CREATE PROCEDURE dbo.proc_CheckTableExistance @tInstanceName NVARCHAR(200),
                                              @tDatabaseName NVARCHAR(100),
                                              @tSchemaName   NVARCHAR(100),
                                              @tTableName    NVARCHAR(100)
AS
BEGIN
    DECLARE @stmnt NVARCHAR(MAX)
    --Check if the provided server is a linked server or not
    IF EXISTS (SELECT data_source FROM sys.servers ss WHERE ss.data_source = @tInstanceName AND ss.is_linked = 0)
    BEGIN
        SET @stmnt = 'SELECT IST.TABLE_NAME FROM [' + LTRIM(RTRIM(@tDatabaseName)) + '].' + 
                     'INFORMATION_SCHEMA.TABLES IST WHERE IST.TABLE_CATALOG = ' 
                                                    + '''' + LTRIM(RTRIM(@tDatabaseName)) + '''' +
                     ' AND IST.TABLE_SCHEMA = '     + '''' + LTRIM(RTRIM(@tSchemaName))   + '''' +
                     ' AND IST.TABLE_NAME = '       + '''' + LTRIM(RTRIM(@tTableName))    + ''''
    END
    ELSE IF EXISTS (SELECT data_source FROM sys.servers ss WHERE ss.data_source = @tInstanceName AND ss.is_linked = 1)
    BEGIN
        SET @stmnt = 'EXECUTE (''SELECT IST.TABLE_NAME FROM ' + @tDatabaseName +
                     '.INFORMATION_SCHEMA.TABLES IST WHERE IST.TABLE_CATALOG = ? AND IST.TABLE_SCHEMA = ? AND IST.TABLE_NAME = ?'', ''' 
                                + @tDatabaseName + ''', ''' 
                                + @tSchemaName   + ''',''' 
                                + @tTableName + ''') AT [' + @tInstanceName + ']'
    END
    ELSE
    BEGIN
        SET @stmnt = 'SELECT ''Required SQL instance not found in sys.servers catalog view.'''
    END
    
    EXEC sp_executesql @stmnt
END
GO

The key point here is the usage of the EXECUTE statement. The EXECUTE statement has the provision to execute a query against a linked server when combined with the “AT” clause. More details are available from the MSDN page here.

In Conclusion

Please note that there is one major point that you need to take care before using EXECUTE in this way. EXECUTE is prone to SQL Injections and hence please make sure that you production code has each and every input validated beforehand.

You can, in this way, use the EXECUTE…AT combination to execute a query against a SQL Server of a different version or even against a different data source (the MSDN example has a way to fire off a query against an ORACLE data source).

Do let me know what you, the reader, uses to query a different database on a different SQL Server instance or a different data source.

Be courteous. Drive responsibly.

Underappreciated features of Microsoft SQL Server

2 Replies

SQLServerCentral (or “SSC” as we know it), ran a very good editorial last year on Friday, December 17, 2010. The editorial was a guest editorial by Andy Warren on “under-appreciated features of SQL Server” (http://www.sqlservercentral.com/articles/Editorial/71788/). Andy looks back at the evolution of Microsoft SQL Server over the past decade (that we just bid goodbye to) and the tons of features that have been introduced across the various releases. Some of these features are still with us, some have been deprecated; however the editorial is more targeted to identifying those features that held a lot of promise, but for one reason or the other failed to take off within the general SQL Server community.

If you follow the discussion that took place afterwards, a lot of interesting features were listed down. I have here a summary of the same:

SQL Server Management Studio/Profiler

T-SQL Enhancements

Administration Enhancements

Major Technological Enhancements

SQL CLR
Resource Governor
Event notifications
Tuning Advisor
Data Collector
Central Management Server
Missing indexes in graphical plans
XML tools
Upgrade Advisor
Connection Strings
Service Broker

As I was following the discussion and compiling the list, it dawned upon me that this is an excellent opportunity to write a little bit on each of these features that we as a community might end up appreciating a bit more.

The major technological enhancements are such that they require a detailed study of their own. However, the rest are really very quick demos in order to get started and I am sure that they will definitely change your life at the work place.

I will thus spin off a series of small articles and ultimately link them all back to this parent article. Finally, as a closing post to the series, I will be also be pointing you towards some learning resources for rest of the items on the list.

Do send in your feedback on how you would use (or are using) each of these items.

Have a good day!

Be courteous. Drive responsibly.

CAST v/s CONVERT

1 Reply

Data type conversion has been one of the most used features of any programming language. Microsoft SQL Server users are familiar with the two functions: CAST() and CONVERT() and use them regularly, and more specifically, interchangeably.

However, using CAST() and CONVERT() interchangeably is one of the most capital mistakes that a developer can make. Madhivanan, a SQL Server blogger on BeyondRelational.com (http://beyondrelational.com/blogs/madhivanan/default.aspx) does a very good job at highlighting the differences between CAST() and CONVERT() in his blog post here.

To summarize, the differences that he highlights are:

CAST is an ANSI standard, while CONVERT is specific to Microsoft SQL Server
CAST cannot be used for formatting purposes (i.e. used to type cast something to a character string), whereas CONVERT can do so, especially for datetime and money datatypes
CAST cannot convert a string to a DATETIME value of a required format (i.e. formatting as mm/dd/yyyy or dd/mm/yyyy as required etc), while the CONVERT can

Finally, he warns us to use a properly calculated size value when using the CAST and CONVERT functions to convert integer values to character data types.

All very great points, and very, very useful. Thank-you, Madhivanan for the wonderful research and enlightenment.

A Question

We can draw a general conclusion that for computation purposes, CAST should be used and for formatting of values for display on a UI or a report, CONVERT should be used. However, the interesting question is one which came up when I was following the discussion on the blog the other day –

Tuesday, July 27, 2010 10:38 AM by cute_boboi

For data extraction purpose, with 1M+ records, which method is faster/recommended ? CAST or CONVERT from:
(i) Date to varchar
(ii) Int to varchar

Today, I try to answer that question.

The Demonstration

Preparing the Environment

Let’s start by creating a table, and filling it up with some test data (Running against the AdventureWorks2008 database gives you about 356409 rows in the table):

-- Step 01. Create a test table
CREATE TABLE CASTCONVERTTest (Id INT,
                              CrDate DATETIME)

-- Step 02. Generate Test Data
INSERT INTO CASTCONVERTTest
SELECT sso1.Id, sso1.crdate
FROM sys.sysobjects sso1
CROSS JOIN sys.sysobjects sso2

Now, let’s begin by pressing Ctrl+M when in the SQL Server Management Studio or go to Query->Include Actual Execution Plan to enable showing the Actual Execution Plan.

Conversion of INT to VARCHAR

In order to see the performance of using CAST and CONVERT on INT to VARCHAR conversion, we will use the following script. At each stage, we will be using DBCC freeproccache to remove all elements from the plan cache.

/***********************************************************
Press Ctrl+M (or go to Query->Include Actual Execution Plan)
************************************************************/
-- Step 03. Test conversion of INT to VARCHAR
-- A. Remove all elements from the plan cache
DBCC freeproccache
GO

-- B. Check the performance of CAST
SELECT CAST(Id AS VARCHAR(11))
FROM CASTCONVERTTest
GO

-- C. Remove all elements from the plan cache
DBCC freeproccache
GO

-- D. Check the performance of CONVERT
SELECT CONVERT(VARCHAR(11),Id)
FROM CASTCONVERTTest
GO

Let’s look at the Actual Execution Plan used by SQL Server. As you can see, we get a 50-50 split, which means that both CAST and CONVERT perform equally well for the INT to VARCHAR conversion.

Conversion of DATETIME to VARCHAR

In order to see the performance of using CAST and CONVERT on DATETIME to VARCHAR conversion, we will use the following script. At each stage, we will be using DBCC freeproccache to remove all elements from the plan cache.

-- Step 04. Test conversion of DATETIME to VARCHAR
-- A. Remove all elements from the plan cache
DBCC freeproccache
GO

-- B. Check the performance of CAST
SELECT CAST(CrDate AS VARCHAR(25))
FROM CASTCONVERTTest
GO

-- C. Remove all elements from the plan cache
DBCC freeproccache
GO

-- D. Check the performance of CONVERT
SELECT CONVERT(VARCHAR(25),CrDate,106)
FROM CASTCONVERTTest
GO

Let’s look at the Actual Execution Plan used by SQL Server. As you can see, again we get a 50-50 split, which means that both CAST and CONVERT perform equally well for the DATETIME to VARCHAR conversion.

The Cleanup

Finally, as always, let’s cleanup the environment.

-- Step 05. Cleanup!
DROP TABLE CASTCONVERTTest

In Conclusion

In conclusion, we can safely conclude that both CAST and CONVERT perform equally well for the following conversions:

INT to VARCHAR
DATETIME to VARCHAR

I hope the above is a satisfactory reply to the question we started with.

Powered with the research conducted by Madhivanan and with the afore described performance test, the choice is now up to the reader. Do share your practices with CAST & CONVERT on this blog. Also, do mention the reasons why you practice a particular rule of thumb, if possible.

Be courteous. Drive responsibly.

Thank-you, SQL Server Community and I wish you a Happy New Year 2011!

3 Replies

It’s New Year’s Eve, and I am spending some nice family time by taking a holiday from work. However, it is not possible for me to forget the nice memories that the SQL Server community has given me.

As we wind up the year 2010, I would like to express my sincere thanks to the entire SQL Server Community for their kind support and encouragement in 2010. It was fun attending the various Community Tech Days events and also view the recorded Virtual Tech Days (being work days, I was not able to participate as a live attendee).

This year, I made an attempt to start giving back to the SQL Server Community in order to help strengthen it even further. I am thankful that all my contributions have been welcomed by the community and continue to pray that I will receive the same support in the years to come.

One of the the most important professional developments that happened for me in 2010 was that I started my blog with the kind help of Jacob Sebastian and the encouragement of both local SQL Server heroes – Pinal Dave and Jacob Sebastian. Here is a summary of my other contributions to the community:

Contributed quite a few “Question Of the Day” on SQLServerCentral.com
Pinal Dave published one of my articles on December 20, 2010 (http://blog.sqlauthority.com/2010/12/20/sql-server-securing-truncate-permissions-in-sql-server/)
My article on Connection Strings 101 was published on December 24, 2010 on SQLServerCentral.com (http://www.sqlservercentral.com/articles/Connection+Strings/71669/)

I pray to the Almighty that I continue contributing to the community with even increased zeal and fervor in 2011! I am proud to be associated with a community as vibrant and as wonderful as the SQL Server community.

With this, I will close and allow you all to get back with your families to ring in the New Year. I wish you all a very Happy and Prosperous New Year!

Oh…and in the New Year,

Be courteous. Drive responsibly.

SQLTwins by Nakul Vachhrajani

SQL Server tips and experiences dedicated to my twin daughters.

Category Archives: Blog

Object Explorer Details (Part I) – Underappreciated features of Microsoft SQL Server

The Object Explorer Details Window

Executing Queries against linked servers

Step 01: Add a Microsoft SQL Server 2005 linked server

Step 02: Write a stored procedure that builds and executes a dynamic SQL statement

In Conclusion

Underappreciated features of Microsoft SQL Server

SQL Server Management Studio/Profiler

T-SQL Enhancements

Administration Enhancements

Major Technological Enhancements

CAST v/s CONVERT

A Question

The Demonstration

Preparing the Environment

Conversion of INT to VARCHAR

Conversion of DATETIME to VARCHAR

The Cleanup

In Conclusion

Thank-you, SQL Server Community and I wish you a Happy New Year 2011!