SQL Server: Object Validation Error by Estimated Plan While Actual Plan Working Fine

It happened dozen of times when I tried to get an Estimated Execution Plan, it return object verification error, while the same Stored Procedure was working perfectly. Then what’s wrong with Estimated Execution Plan, lets create a simple procedure.

CREATE PROCEDURE Proc_TestProcedure

AS
BEGIN
      IF 1 =2
            SELECT * FROM NoTable — NoTable doesn’t exists
      ELSE
      SELECT ‘ESLE’
END
GO

On compilation and execution, above stored procedure will not return any error, because condition is never true, so it never need to exec SELECT * FROM NoTable, and to check that table exists or not. Now just try to get execution plan of

EXECProc_TestProcedure

It will return error.

Msg 208, Level 16, State 1, Procedure Proc_TestProcedure, Line 8

Invalid object name ‘NoTable’.

Why So.

Because on estimation, query optimizer check each and every statement separately and once it try to estimate cost for “NoTable”, which never exists, it returns above mentioned error.

SQL Server Denali : TRY_CONVERT(), a Good Addition

While working with different typeof data, I have not found a single day, without data type conversion error.Sometime error could be just because of a single row, out of million of correctrows. SQL Server never mentions which row is the culprit, it just throw anerror. Sometime you want to ignore those problematic rows but can’t because youhave no other option to found those wrong data.You can correct them manually or write anintelligent query. Consider following simple example

CREATE TABLE #TempTable (Val1 VARCHAR(10))

INSERT INTO #TempTable

SELECT ‘2.01’ UNION ALL

SELECT ‘A2.4’ UNION ALL

SELECT ‘6.51’ UNION ALL

SELECT ‘$37’ UNION ALL

SELECT ’56’

GO

—Simple convert

SELECT CONVERT(float,Val1)  AS Val1

FROM #TempTable

Result of above selectquery is an error, because row:2 data can’t be converted into float.

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to float.

So whatwill I do, so I can execute my query without any error.

SELECT CASE ISNUMERIC(Val1)

       WHEN 1 THEN 

              CASELEFT(Val1,1)

              WHEN‘$’ THEN NULL

              ELSECONVERT(float,Val1) END 

       ELSE NULL END AS Val1

To avoid such error, with out writing long quires, SQL Server Denali has introduced a new function TRY_CONVERT(). Thisfunction try to convert values according to your given format and if failed itwill return NULL (instead of error). Let’s try this amazing function.

SELECT TRY_CONVERT(float,Val1)  AS Val1

FROM #TempTable

 

SQL Server: Where You Can Find Saved Import/Export SSIS Packages

Using SQL Server Import Export Utility, have you ever saved this SSIS package to SQL Server ?

Recently, a friend of mine asked that after saving Import/Export process as SSIS package to SQL Server, Where he can find this saved package to re-execute it.

Once you have saved your SSIS package,open SQL Server Management studio and then on object explorer panel click on connect and select Integration Services. Use valid user name and password to connect.

 

Once connect, expend Stored Packages node and then expend MSDB node. Here you can find your saved package. Right click on it and select “Run Package” to re-execute your saved import/export steps.

SQL Server: Quick Way to Create Single Table Backup

There are several ways to create backup for a single table in SQL Server database. In SQL Server 2008, now you can create insert statements for selected tables. Beside this Export wizard is commonly used to create a flat file backup for a single table. Both methods are reliable but still time consuming. And when I searched for quickest method to take backup of a single table I found following code by SQL Expert Fred.

USE AdventureWorks

GO

DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)

— If i need to create CSV file Product table then   
SET @table = ‘Production.Product’
SET @file = ‘D:\BCP_OUTPUT\’ + @table + ‘_’ + CONVERT(CHAR(8), GETDATE(), 112)
+ ‘.csv’
SET @cmd = ‘bcp “AdventureWorks.’ + @table + ‘” out “‘ + @file + ‘” -S. -T -c -t,’

EXEC master..xp_cmdshell @cmd

Code basically uses BCP to create a CSV file for a given table. I can create a template of above code, and then just load, change values and execute. So simple but still it has a drawback. It creates a CSV file for all rows but WITHOUT column header row. Now how can I import this table later on, without column header row?

Then I found quickest and simplest way to achieve this goal. Simply type select * query but before execution
• Click on Tools — > options and change values for Query Result Output Format and Custom Delimiter (I preferred pipe sign “|”)

• Press Ctrl+Shift+F buttons, so it can save result to file.
    SELECT * FROM Production.Product
• On execution, provide file name and your desired path and it’s done

Don’t worry about newly created file extension. When need to import just select it from All Files (*,*).

SQL Server: Does Unwanted Tables in a Query or View Affect Performance

Recently a friend of mine asked, that is it true that presence of extra tables in joins section of a query, will affect query performance. Extra tables means, tables which can be skipped from query without affecting query result. For example following query has extra tables (other than vendor and contact tables) in join section
USE AdventureWorks
GO

SELECT  Vendor.Name,
        Contact.Title,
        Contact.FirstName,
        Contact.MiddleName
FROM    Person.Address AS a
        INNER JOIN Purchasing.VendorAddress AS VendorAddress 
        ON a.AddressID = VendorAddress.AddressID
        INNER JOIN Person.StateProvince AS StateProvince 
        ON StateProvince.StateProvinceID = a.StateProvinceID
        INNER JOIN Person.CountryRegion AS CountryRegion 
        ON CountryRegion.CountryRegionCode = StateProvince.CountryRegionCode
        INNER JOIN Purchasing.Vendor AS Vendor
        INNER JOIN Purchasing.VendorContact AS VendorContact 
        ON VendorContact.VendorID = Vendor.VendorID
        INNER JOIN Person.Contact AS Contact 
        ON Contact.ContactID = VendorContact.ContactID
        INNER JOIN Person.ContactType AS ContactType 
        ON VendorContact.ContactTypeID = ContactType.ContactTypeID 
        ON VendorAddress.VendorID = Vendor.VendorID
Though this is NOT common to have extra tables in our usual queries but it could be possible in views. A view can be created with multiple tables and selecting columns from each joined table. And later on when we will query this view we can use only few columns in our select statement. So when we will execute above query SQL Server Query Analyzer will skip all those tables which are not part of game. Here is execution plan of above query.

Same query with more columns, pushing all tables in action.

SELECT  Vendor.Name,
        ContactType.Name AS ContactType,
        Contact.Title,
        Contact.FirstName,
        Contact.MiddleName,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        StateProvince.Name AS StateProvinceName,
        a.PostalCode,
        CountryRegion.Name AS CountryRegionName,
        Vendor.VendorID
FROM    Person.Address AS a
        INNER JOIN Purchasing.VendorAddress AS VendorAddress 
        ON a.AddressID = VendorAddress.AddressID
        INNER JOIN Person.StateProvince AS StateProvince 
        ON StateProvince.StateProvinceID = a.StateProvinceID
        INNER JOIN Person.CountryRegion AS CountryRegion 
        ON CountryRegion.CountryRegionCode = StateProvince.CountryRegionCode
        INNER JOIN Purchasing.Vendor AS Vendor
        INNER JOIN Purchasing.VendorContact AS VendorContact 
        ON VendorContact.VendorID = Vendor.VendorID
        INNER JOIN Person.Contact AS Contact 
        ON Contact.ContactID = VendorContact.ContactID
        INNER JOIN Person.ContactType AS ContactType 
        ON VendorContact.ContactTypeID = ContactType.ContactTypeID 
        ON VendorAddress.VendorID = Vendor.VendorID

If we create a view using our second query and use our view in following style then execution plan will be same to our first query.

SELECT  Name,
        Title,
        FirstName,
        MiddleName
FROM    vw_MyView

Hence, SQL Server Query Analyser is quite smart and work on only those tables which are part of actual game and it doesn’t matter that extra tables are part of your query or a view.

SQL Server: Query Template, a Time Saving Feature

While working with SQL Server, its common to have a saved script which you need to execute for different server or databases and usually you open this saved script, make changes according to requirements and execute. SQL Server 2008 has one more time saving facility. You can save your script as template, like following is a simple query script, which I like to execute for different databases and with different order by clause.

Read Full Store …

SQL Server: sp_WhoIsActive, a Must Tool for a DBA

A mail with subject “Low Database Response” or a call with harsh words “What happened to the database?” , push a DBA to query sp_who or sp_who2 to get first hand information about problem but these sp doesn’t help most of the time BUT sp_WhoIsActive really does.

Read full story…

SQL Server: Short Code Doesn’t Mean Smart Code

TSQL is a powerful query language.A single query can be written by several ways. With new functions and keywords, query with multiple lines now can be written very shortly and effectively BUT every time and SHORT code doesn’t mean SMART (well performing)code. How? Read full story…

SQL Server: Conditional WHERE clause (Filter for a Filter)

No one is unaware of WHERE clause. Everyone knows that we can filter output records by using WHERE clause but recently I found my team member stuck when he need a Conditional WHERE clause (filter for a filter).  Let’s try to figure out the problem and its solution with a simple scenario.

read full story

SQL Server: How SQL Diag Became Eesy for Every DBA

SQL Diag is a useful diagnostic tool, through which we can collect lot of useful information, like

• Windows performance logs
• Windows event logs
• SQL Server Profiler traces
• SQL Server blocking information
• SQL Server configuration information

Only problem with this tool is that you have to configure an XML file to provide information to SQL Diag that basically decides that what type of information you want. Though a default XML file is provided with tool as an example but it is not according to everyone’s requirement and somehow you have to change this XML file to get proper information.

Like me, most of DBAs and Developers are not good with XML, and that is why unable to use SQL Diag according to their needs. But recently this problem is solved by providing a simple SQL Diag Configuration utility, through which you can configure input XML file according to your requirements BUT just using your mouse and you need not to know a single line of XML.

Read full story…