Journey to SQL Authority with Pinal Dave

I recently received question through email that how to determine if any user defined function is deterministic or non-deterministic?

First go through two articles I have written about deterministic and non-deterministic function.

SQL SERVER - Deterministic Functions and Nondeterministic Functions

SQL SERVER - 2005 - Use of Non-deterministic Function in UDF - Find Day Difference Between Any Date and Today

You can run following code to determine if function is deterministic or not.

SELECT OBJECTPROPERTY(OBJECT_ID(‘dbo.ufnGetAccountingStartDate’), 
‘IsDeterministic’) IsFunctionDeterministic

Reference : Pinal Dave (http://www.SQLAuthority.com)

Following email is received from SQL Server Expert Roy Cheung. He faced issue of creating and running distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server. He has found solution and would like to share with SQLAuthority Blog Readers.

What I love most about this blog is active readers participation. If readers are becoming contributor is the true success for any blog or online community. Recently many readers have contributed their suggestions and script to this blog.

Joffery has provided nice script which is modification to previous article of SQL SERVER - 2005 - Find Tables With Foreign Key Constraint in Database.

Following note is from Joffery:

Hi Pinal

Very interesting article and of great help.
I made a little addition to your code. As I wanted also to know what the FKs are doing in the Table (referential integrity on update and on delete) I added two columns to your superb query. And a little bit of ordering :)
Maybe it helps other readers.

USE AdventureWorks
GO
SELECT f.name AS ForeignKey,
            OBJECT_NAME(f.parent_object_id) AS TableName,
            COL_NAME(fc.parent_object_id,
                fc.parent_column_id) AS ColumnName,
            OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
            COL_NAME(fc.referenced_object_id,
                fc.referenced_column_id) AS ReferenceColumnName,
            f.update_referential_action_desc AS UpdateAction,
            f.delete_referential_action_desc AS DeleteAction
    FROM sys.foreign_keys AS f
            INNER JOIN sys.foreign_key_columns AS fc
            ON f.OBJECT_ID = fc.constraint_object_id
    ORDER BY TableName ASC, ColumnName ASC

Reference : Pinal Dave (http://www.SQLAuthority.com), Joffery

I recently receive question from one of the blog reader that he is having problem creating database in Windows Vista. Read original comment here.

I have installed vista ultimate and sql server 2005 devloper edition in my computer.I also connect sql 2005 in window authentication but when I CREATE any database in following query
CREATE DATABASE MANEESH
USE MANEESH
Its give me everytime following error:-
Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database ‘master’.
&
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database ‘maneesh’. No entry found with that name. Make sure that the name is entered correctly.
But i entered ev name correctly.and i am not making anytype tabels in sql2005.Solve these problem please.

Solution to above problem is very simple and I have already posted solution for this in my earlier article. SQL SERVER 2005 - FIX Error: 18456 : VISTA Windows Authentication

Reference : Pinal Dave (http://www.SQLAuthority.com)

In previous post I have mentioned about SQL SERVER 2005 - Vista Ultimate and SQL Server 2005 DEV Edition. There was one simple issue with the installation. I was not able to login using windows authentication method. I was able to successful login using sa username and password.

I kept on receiving following error.

TITLE: Connect to Server
——————————
Cannot connect to SQLAUTHORITY.
——————————
ADDITIONAL INFORMATION:

Login failed for user ‘SQLAUTHORITY\Pinal’. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

——————————
BUTTONS:
OK
——————————

After a while I realize that this may be due to one needs Administrator rights to do any task in SQL Server. I added the my current login as administrator in SQL Server and next time I was able to login successfully using Windows Authentication.

Following diagrams explains the fix.

1. Error while using Windows Authentication

2. Login using SA username and password and go to Security >> New Login

3. Type In Login Name as ComputerName\UserName

4. Click Server Roles and check sysadmin server role, after that click OK.

5. Now login using Windows Authentication and it should work fine.

Reference : Pinal Dave (http://www.SQLAuthority.com)

Long time blog reader and SQL Server Expert Simon Worth has suggested two additional method to achieve same results as described in article SQL SERVER - Find Table in Every Database of SQL Server.

Method 1

sp_msforeachdb "SELECT ’?' DatabaseName, Name FROM ?.sys.Tables WHERE Name LIKE ’%address%’"
            

Method 2

CREATE TABLE #TableNameResults (DatabaseName VARCHAR(100) NOT NULL, TableName VARCHAR(100) NOT NULL)
INSERT INTO #TableNameResults 
    EXEC sp_msforeachdb "SELECT ’?' DatabaseName, Name FROM ?.sys.Tables WHERE Name LIKE ’%address%’"
SELECT * 
    FROM #TableNameResults
DROP TABLE #TableNameResults
            

Reference : Pinal Dave (http://www.SQLAuthority.com), Simon Worth

I am proud on readers of this blog. One of the reader asked asked question on article SQL SERVER - Delete Duplicate Records - Rows and another reader followed up with nice quick answer. Let us read them both together.

Question from Mayank Mishra

Is it possible to delete a single column from a table in SQL 2000.

Thanks

Answer from Ray McCoy

This works in 2000. WARNING: According to MS, SET ROWCOUNT will not work with INSERT, DELETE, and UPDATE in later versions.

– Create table with a number of values between zero and nine
select a+b+c as val
into dbo.rmtemp
from (select 0 a union all select 1 union all select 2 union all select 3) a
, (select 0 b union all select 1 union all select 2 union all select 3) b
, (select 0 c union all select 1 union all select 2 union all select 3) c

– Show what you’ve got
select val,count(*) row_count from dbo.rmtemp group by val

– Limit processing to a single row
set rowcount 1
– While you’ve got duplicates, delete a row
while (select top 1 val from dbo.rmtemp group by val having count(*) > 1) is not null
delete from dbo.rmtemp where val in (select top 1 val from dbo.rmtemp group by val having count(*) > 1);
– Remove single row processing limit
set rowcount 0

– Confirm that only uniques remain
select val,count(*) row_count from dbo.rmtemp group by val

– Clean up
drop table dbo.rmtemp

– Comment
I knew there had to be a better way.

Reference : Pinal Dave (http://www.SQLAuthority.com)

I have been asked many times before “Does SQL Server Dev edition can be installed on Vista operating system?”

I decided to find out the answer of this myself. I have just got new system which has Vista Ultimate Installed on it. I installed SQL Server 2005 dev edition on it. While installing it suggested that there are few component will not work with Vista and to make them work make sure to install SQL Server 2005 SP2. I was any way planning to install that. Once installation of SQL Server 2005 over, I installed SQL Server 2005 SP2. After restart I ran SQL Server 2005 and it just worked fine.

I am loving the combination of SQL Server 2005 and Vista Ultimate.

Reference : Pinal Dave (http://www.SQLAuthority.com)

Christopher Miller read article of SQL SERVER Database Coding Standards and Guidelines Complete List Download and came up with wonderful SQL Server Script to rename all their database constraint with more organized constraint names, which helps to easily identify the constraint database exist on.

Christopher Miller - “When we submit our schema updates internally, we usually catch any deviation from our naming conventions.  It’s not a perfect process and every now and then, something slips through the cracks.  We then correct the schema update to use the appropriate naming convention.  if we have been using the schema changes internally, we may have some databases that don’t match the published schema for the object names.  When that happens, we run a simple T-SQL script that cleans house.  The following T-SQL will scan the database for default constraints that do not match our naming conventions and rename the ones that it finds.”

Read more about this subject and find the script to rename database objects How to rename database objects to comply with naming conventions.

Reference : Pinal Dave (http://www.SQLAuthority.com)

I always enjoy writing for my readers. Many times, I receive very good note, comments or article from my great experts of SQL Server. I really enjoy learning from my reader.

If you are reader of SQLAuthority and you think you have knowledge, script or concept which benefit other readers of this blog, please feel free to send that to me. I love sharing good article and knowledge with my readers.

You do not have to be well known to write article, just something which can interest other fellow readers like you, will be good article for this blog. It will be my entirely my decision about which article to publish or edit it for clarity. If it is readers contribution, I will mention the name of the article author along with article.

I will also send personal thank you note to author of article published on this blog.

Yesterday I wrote about SQL SERVER - Find Table in Every Database of SQL Server. Today we will see another method how we can achieve the same result using Information_Schema view. Refer my previous article here for additional information.

CREATE PROCEDURE usp_FindTableNameInAllDatabase
    @TableName VARCHAR(256)
            AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR
    SET @getDBName = CURSOR FOR
SELECT name
    FROM sys.databases
CREATE TABLE #TmpTable (TABLE_CATALOG VARCHAR(128), TABLE_SCHEMA VARCHAR(128), TABLE_NAME VARCHAR(256), TABLE_TYPE VARCHAR(10))
OPEN @getDBName
            FETCH NEXT
    FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @varSQL = ‘USE ’ + @DBName + ‘;
			INSERT INTO #TmpTable
			SELECT *
			FROM INFORMATION_SCHEMA.TABLES
			WHERE TABLE_NAME LIKE ”%’ + @TableName + ‘%”’
    EXEC (@varSQL)
                FETCH NEXT
        FROM @getDBName INTO @DBName
    END
            CLOSE @getDBName
            DEALLOCATE @getDBName
SELECT *
    FROM #TmpTable
DROP TABLE #TmpTable
GO
EXEC usp_FindTableNameInAllDatabase ‘Address’
GO
            

Reference : Pinal Dave (http://www.SQLAuthority.com)

Just a day ago, one of the Jr. Developer requested that if I can help her with finding one particular table in every database on SQL Server. We have many Database Server and on some of the Database Server we have nearly 200 databases on it. The requirement was to find out one particular table from all the database. This was not possible by visual inspection as it might take lots of time and human error was possible. She was aware of the system view sys.tables.

SELECT *
    FROM sys.Tables
    WHERE name LIKE ‘%Address%’

The limitation of query mentioned above is that it only searches in one database and user has to keep on changing database manually and run the query again. I wrote down following quick script which looks into all the database on the server and provides the database name, schema name and table containing searched word in its name.

CREATE PROCEDURE usp_FindTableNameInAllDatabase
    @TableName VARCHAR(256)
            AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR
    SET @getDBName = CURSOR FOR
SELECT name
    FROM sys.databases
CREATE TABLE #TmpTable (DBName VARCHAR(256),
                SchemaName VARCHAR(256),
                TableName VARCHAR(256))
OPEN @getDBName
            FETCH NEXT
    FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @varSQL = ‘USE ‘ + @DBName + ‘;
			INSERT INTO #TmpTable
			SELECT ”’+ @DBName + ”’ AS DBName,
 SCHEMA_NAME(schema_id) AS SchemaName,
name AS TableName
			FROM sys.tables
			WHERE name LIKE ”%’ + @TableName + ‘%”’
    EXEC (@varSQL)
                FETCH NEXT
        FROM @getDBName INTO @DBName
    END
            CLOSE @getDBName
            DEALLOCATE @getDBName
SELECT *
    FROM #TmpTable
DROP TABLE #TmpTable
GO
EXEC usp_FindTableNameInAllDatabase ‘Address’
GO           

If you liked this script and have similar useful script let me know and I will post it here.

Reference : Pinal Dave (http://www.SQLAuthority.com)

One of the most popular request I have received on this blog is to create one page which list all the SQL Server FAQs. SQL Server technology is very broad as well very deep. This is my humble attempt to list few of the daily used details in one page. Let me know your opinion and suggestion.

Download SQL Server FAQ Sheet in PDF format

I enjoy reader’s articles to read as much as I enjoy expert’s articles. One of blog reader Praveen Barath always have good ideas to share. Here is Praveen Barath’s comment on my previous article Query Analyzer Shortcuts.

MSSQL server 2005 is a database platform , Platform because from one window you can connect to any of MSSQL services like SSMS, SSRS,SSIS,SSAS..etc.

I am coming to your doubt why they shifted to SSMS as it s far slow.

As the matter of fact MSSQL 2005 is more graphical more user friendly and handy tool, I hope once you will aware of all SSMS functionality or new feature you might change your views, I agree it require more hardware recourses like RAM, 500 MB Min, and 1000 Mb Recommended rest depends on your work load. Also I feel you would be more contented with 2000 but still there are new upcoming DBA’s which require these sort of user friendly interface.(If still you face slowness check your settings and resources.)

Shortcuts:

2000 – isqlw –To open a query analyzer in 2000

2005 – sqlwb—To open SSMS in 2005 (Not Query analyzer For New Query use Alt+N)

2005 –ssmsee—TO open SSMS in 2005 (Express Edition)

The most commonly used keyboard shortcuts in the SQL Server Management Studio (SSMS).

Run and then SqlWb.exe to launch SQL Server Management Studio

Results in Grid
Ctrl + D

Change database
Ctrl + U

Results in Text
Ctrl + T

Execute Query
Ctrl + E or F5

Show/Hide Object browser
F8 (Auto Hide has to be enabled)

New Query Window
Ctrl + Q

New Query with New Connection
Ctrl + N (Then selecting SQL Server Query template)

Open a .SQL Script file
Ctrl + O

Full Screen
Shift + Alt + Enter

Parse the query
Ctrl + F5

Show/Hide Results Pane
Ctrl + R

Switch between query and results panes
F6

Information about all the objects in the current database
Alt + F1

Reference : Pinal Dave (http://www.SQLAuthority.com)

This article has been written by blog reader and SQL Server Expert Praveen Barath in response to my previous article SQL SERVER - Optimization Rules of Thumb - Best Practices.

Well Query Optimizations rules are not limited.
It depends on business needs as well,

For example we always suggest to have a relationship between tables but if they are heavily used for Update insert delete, I personally don’t recommended coz it will effect performance as I mentioned it all depends on Business needs;

Here are few more tips I hope will help you to understand.

One: only “tune” SQL after code is confirmed as working correctly.
(use top (sqlServer) and LIMIT to limit the number of results where appropriate,
SELECT top 10 jim,sue,avril FROM dbo.names )

Two: ensure repeated SQL statements are written absolutely identically to facilate efficient reuse: re-parsing can often be avoided for each subsequent use.

Three: code the query as simply as possible i.e. no unnecessary columns are selected, no unnecessary GROUP BY or ORDER BY.

Four: it is the same or faster to SELECT by actual column name(s). The larger the table the more likely the savings.

Five: do not perform operations on DB objects referenced in the WHERE clause:

Six: avoid a HAVING clause in SELECT statements - it only filters selected rows after all the rows have been returned. Use HAVING only when summary operations applied to columns will be restricted by the clause. A WHERE clause may be more efficient.

Seven: when writing a sub-query (a SELECT statement within the WHERE or HAVING clause of another SQL statement):
– use a correlated (refers to at least one value from the outer query) sub-query when the return is relatively small and/or other criteria are efficient i.e. if the tables within the sub-query have efficient indexes.
– use a noncorrelated (does not refer to the outer query) sub-query when dealing with large tables from which you expect a large return (many rows) and/or if the tables within the sub-query do not have efficient indexes.
– ensure that multiple sub-queries are in the most efficient order.
– remember that rewriting a sub-query as a join can sometimes increase efficiency.

Eight: minimize the number of table lookups especially if there are sub-query SELECTs or multicolumn UPDATEs.

Nine: when doing multiple table joins consider the benefits/costs for each of EXISTS, IN, and table joins. Depending on your data one or another may be faster.
‘IN is usually the slowest’.
Note: when most of the filter criteria are in the sub-query IN may be more efficient; when most of the filter criteria are in the parent-query EXISTS may be more efficient.

Ten: where possible use EXISTS rather than DISTINCT.

Praveen Barath

Reference : Pinal Dave (http://www.SQLAuthority.com)

There are few rules for optimizing slow running query. Let us look at them one by one see how it can help.

Rule # 1 : Always look at query plan first.
I always start looking at query plan. There is always something which catches eyes. I pay special attention to part which has taken the most expensive part of whole execution plan.

Rule # 2 : Table scan or clustered index scan needs to be optimized to table seek (if your table is small it does not matter and table scan gives you better result). Table scan happens when index are ignored while retrieving the data or index does not exist at all.

Rule # 3 : Bookmark lookup are not good as it means correct index is not being used or index is not sufficient for all the necessary data to retrieve. The solution of bookmark lookup is to create covering index. Covering index will cover all the necessary columns which needs to improve performance of the query. It may be possible that covering index is slower. Try and Error is good method to find the right match for your need.

Rule # 4 : Experiment with Index hints. In most cases database engines picks up the best index for the query. While determining which index is best for the query, database engine has to make certain assumption for few of the database parameters (like IO cost etc). It may be possible database engine is recommending incorrect index for query to execute. I usually try with few of my own indexes and test if database engine is picking up most efficient index for queries to run. I use Index Hint for this purpose.

Rule # 5 : Avoid functions on columns. If you use any functions on column which is retrieved or used in join or used in where condition, it does not take benefit of index. If I have situation like this, I usually create separate column and populate it ahead of time using simple update query with the value which my function will return. I put index on this new column and this way performance  is increased. Creating indexed view is good option too.

Rule # 6 : Do not rely on execution plan only. I understand that this contradicts the Rule # 1, however execution plan is not the only benchmark for indexes. There may be cases that something looks less expensive on execution plan but in real world it takes more time to get data. Test your indexes on different benchmarks.

Rule # 7 : ___________________________________________

Let me know what should be the Rule # 7.

Reference : Pinal Dave (http://www.SQLAuthority.com)

Just like everybody else I also keep my personal bookmarks of websites. Recently I have reorganized my bookmarks in two categories. Please visit them and let me know your opinion.

SQLAuthority BEST Articles

SQLAuthority FAVORITE Articles

Visio Infrastructure for SQL Servers is a tool which is meant for IT administrators who require constant interactions with the users for the installations of the SQL server in any IT infrastructure. Visio Infrastructure for SQL Servers is a tool which is meant for IT administrators who require constant interactions with the users for the installations of the SQL server in any IT infrastructure. This tool eases the constant communication between the end user and the administrator where administrators will have a ready to install visual representation of data which can be executed later to build the infrastructures efficiently. This diagrammatic representation of SQL Servers with the configuration data of each server will help installation implementation with less communication with the end user. The objective of this tool is to prove the capabilities of Visio to draw the effective diagrams using Visio SDK.

Download Microsoft Office Visio 2007 Professional SQL Server Add-In

There are always more than one way to do one thing in any programming languages. In SQL Server there are always more than one way to achieve same result set. It is quite often I see that developers write subqueries in place of joins or joins in place subqueries.

I recommend to read my previous article SQL SERVER - Better Performance - LEFT JOIN or NOT IN?, which describes how to convert subqueries to Joins and Joins to subqueries.

Reference : Pinal Dave (http://www.SQLAuthority.com)

First of all answer this question : Which method of T-SQL is better for performance LEFT JOIN or NOT IN when writing query? Answer is : It depends! It all depends on what kind of data is and what kind query it is etc. In that case just for fun guess one option LEFT JOIN or NOT IN. If you need to refer the query which demonstrates the mentioned clauses, review following two queries.

USE AdventureWorks;
GO
SELECT ProductID
    FROM Production.Product
    WHERE ProductID
            NOT IN (
    SELECT ProductID
        FROM Production.WorkOrder);
GO
SELECT p.ProductID
    FROM Production.Product p
            LEFT JOIN Production.WorkOrder w ON p.ProductID = w.ProductID
    WHERE w.ProductID IS NULL;
GO

Now let us examine the actual execution plan of both the queries. Click on image to see larger image. You can clearly observe that first query with NOT IN takes 20% resources of execution plan and LEFT JOIN takes 80% resources of execution plan. It is better to use NOT IN clause over LEFT JOIN in this particular example. Please note that this is not generic conclusion and applies to this example only. Your results may vary on many factors. Let me know your comments if you have guessed this correct or not.

Reference : Pinal Dave (http://www.SQLAuthority.com)