Identify Numbers of Non Clustered Index on Tables for Entire Database

Here is the script which will give you numbers of non clustered indexes on any table in entire database.

SELECT COUNT(i.TYPE) NoOfIndex,[schema_name] = s.name, table_name = o.nameFROM sys.indexes iINNER JOIN sys.objects o ON i.[object_id] = o.[object_id]INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]WHERE o.TYPE IN ('U')
AND i.TYPE = 2GROUP BY s.name, o.nameORDER BY schema_name, table_name

Here is the small story behind why this script was needed.

I recently went to meet my friend in his office and he introduced me to his colleague in office as someone who is an expert in SQL Server Indexing. I politely said I am yet learning about Indexing and have a long way to go. My friend’s colleague right away said – he had a suggestion for me with related to Index. According to him he was looking for a script which will count all the non clustered on all the tables in the database and he was not able to find that on SQLAuthority.com.

I was a bit surprised as I really do not remember all the details about what I have written so far. I quickly pull up my phone and tried to look for the script on my custom search engine and he was correct. I never wrote a script which will count all the non clustered indexes on tables in the whole database. Excessive indexing is not recommended in general. If you have too many indexes it will definitely negatively affect your performance. The above query will quickly give you details of numbers of indexes on tables on your entire database. You can quickly glance and use the numbers as reference.

Please note that the number of the index is not a indication of bad indexes. There is a lot of wisdom I can write here but that is not the scope of this blog post. There are many different rules with Indexes and many different scenarios. For example – a table which is heap (no clustered index) is often not recommended on OLTP workload (here is the blog post to identify them), drop unused indexes with careful observation (here is the script for it), identify missing indexes and after careful testing add them (here is the script for it). Even though I have given few links here it is just the tip of the iceberg. If you follow only above four advices your ship may still sink. Those who wants to learn the subject in depth can watch the videos here after logging in.

Note: You change where condition type to 6 for nonclustered column store index.

Reference: Pinal Dave (http://blog.sqlauthority.com)

INFORMATION_SCHEMA.COLUMNS and Value Character Maximum Length -1

INFORMATION_SCHEMA.COLUMNS and Value Character Maximum Length -1

July 20, 2012 by pinaldave

I personally use the sys schema and DMV to retrieve most of the information. However, I am not surprised see usage of Information_Schema. It has been very popular and works in most of the time. Though, I do not use any feature it does not mean everybody else should stop using the same feature. The matter of the fact, when I receive questions about features which I have not used frequently I feel refreshed to come across new concepts.

Just a few days ago, I received a simple question about INFORMATION_SCHEMA.COLUMNS table. The question was as follows:

Question: I often see the value -1 in the CHARACTER_MAXIMUM_LENGTH column of INFORMATION_SCHEMA.COLUMNS table. I understand that the length of any column can be between 0 to large number but I do not get it when I see value in negative (i.e. -1). Any insight on this subject?

Answer: Of course, I love this kind of simple question which often know the answer or assume that we know the answer. Whenever we use data type VARCHAR(MAX) for any column it is represented by -1 in INFORMATION_SCHEMA.COLUMNS table. Let us see a quick demonstration of the same.

Let us create a table which has column which is of VARCHAR(MAX) and see the result returned by the same.

-- Create Sample TableCREATE TABLE t(id INT,name VARCHAR(200),address VARCHAR(MAX))GO-- select from columnsSELECT COLUMN_NAME,CHARACTER_MAXIMUM_LENGTHFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME=OBJECT_NAME(OBJECT_ID('t'))GO-- drop tableDROP TABLE t
GO  

Let us check the resultset.

You will see that the column address which is of datatype VARCHAR(MAX) have Character Maximum Length value as -1. You will see the same behavior from nvarchar(max) and varbinary(max).

I personally believe in simple learning – if we learn a thing a day we will learn 365 new things every year!

Reference: Pinal Dave (http://blog.sqlauthority.com)

Why Do We Need Master Data Management – Importance and Significance of Master Data Management (MDM)

Why Do We Need Master Data Management – Importance and Significance of Master Data Management (MDM)

September 11, 2012 by pinaldave

Let me paint a picture of everyday life for you.  Let’s say you and your wife both have address books for your groups of friends.  There is definitely overlap between them, so that you both have the addresses for your mutual friends, and there are addresses that only you know, and some only she knows.  They also might be organized differently.  You might list your friend under “J” for “Joe” or even under “W” for “Work,” while she might list him under “S” for “Joe Smith” or under your name because he is your friend.  If you happened to trade, neither of you would be able to find anything!

This is where data management would be very important.  If you were to consolidate into one address book, you would have to set rules about how to organize the book, and both of you would have to follow them.  You would also make sure that poor Joe doesn’t get entered twice under “J” and under “S.”

This might be a familiar situation to you, whether you are thinking about address books, record collections, books, or even shopping lists.  Wherever there is a lot of data to consolidate, you are going to run into problems unless everyone is following the same rules.

I’m sure that my readers can figure out where I am going with this.  What is SQL Server but a computerized way to organize data?  And Microsoft is making it easier and easier to get all your “addresses” into one place.  In the  2008 version of SQL they introduced a new tool called Master Data Services (MDS) for Master Data Management, and they have improved it for the new 2012 version.

MDM was hailed as a major improvement for business intelligence.  You might not think that an organizational system is terribly exciting, but think about the kind of “address books” a company might have.  Many companies have lots of important information, like addresses, credit card numbers, purchase history, and so much more.  To organize all this efficiently so that customers are well cared for and properly billed (only once, not never or multiple times!) is a major part of business intelligence.

MDM comes into play because it will comb through these mountains of data and make sure that all the information is consistent, accurate, and all placed in one database so that employees don’t have to search high and low and waste their time. MDM also has operational MDM functions.  This is not a redundancy.  Operational MDM means that when one employee updates one bit of information in the database, for example – updating a new address for a customer, operational MDM ensures that this address is updated throughout the system so that all departments will have the correct information.

Another cool thing about MDM is that it features Master Data Services Configuration Manager, which is exactly what it sounds like.  It has a built-in “helper” that lets you set up your database quickly, easily, and with the correct configurations.  While talking about cool features, I can’t skip over the add-in for Excel.  This allows you to link certain data to Excel files for easier sharing and uploading.

In summary, I want to emphasize that the scariest part of the database is slowly disappearing.  Everyone knows that a database – one consolidated area for all your data – is a good idea, but the idea of setting one up is daunting.  But SQL Server is making data management easier and easier with features like Master Data Services (MDS).

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

SQL in Sixty Secondsby Pinal Dave

SQL in Sixty Seconds

by Pinal Dave

http://www.youtube.com/playlist?list=PL4BD5C24EDB584162&feature=mh_lolz

Simple Cursor to Select Tables in Database with Static Prefix and Date Created

Following cursor query runs through database and find all the table with certain prefixed (‘b_’,'delete_’). It also checks if the Table is more than certain days old or created before certain days, it will delete it. We can have any other opertation on that table like delete, print or reindex.

SET NOCOUNT ON
DECLARE @lcl_name VARCHAR(100)DECLARE cur_name CURSOR FOR
SELECT nameFROM sysobjectsWHERE type = 'U'AND crdate <= DATEADD(m,-1,GETDATE())
AND name LIKE 'b_%'OPEN cur_nameFETCH NEXT FROM cur_name INTO @lcl_nameWHILE @@Fetch_status = 0BEGIN
SELECT @lcl_name = 'sp_depends ' +@lcl_namePRINT @lcl_name--  EXEC (@lcl_name )FETCH NEXT FROM cur_name INTO @lcl_nameEND
CLOSE cur_nameDEALLOCATE cur_nameSET NOCOUNT OFF
Reference: Pinal Dave (http://www.SQLAuthority.com)

Manage Help Settings – CTRL + ALT + F1

October 6, 2012 by pinaldave

It is a miracle that curiosity survives formal education. ~ Albert Einstein

I have 3 years old daughter and she never misses any chance to play with the system. I have multiple computers and I always make sure that if I am working with production server, I never leave it open but when I am doing some experiment I often leave my computer open. My daughter loves the part when I have left the computer open and I am not observing her. Recently I had the same scenario, I got urgent call and I moved away from my computer and when I returned she was playing with SSMS left open my computer. Here is the screen which was visible on the screen.

For a moment, I could not figure out what was this screen and what was about to get updated. I tried to ask her what keys she pressed the reaction was “I wanted – eya eya o”. Well, what more I expect from 3 years old. She is no computer genius – she just learned to use notepad and paint on my machine.

Finally, when I saw the above screen in detail, I realize that this screen was from the help screen and something got updated. I have been using SQL Server for a long time but I never updated help on the screen. When I need to search something if I remember that I have written it earlier I will go to http://search.sqlauthority.com and will search there or will search on Google.

As this computer was already updated I fired up Virtual Machine and tried to look recreate how my daughter was reached to above screen. Here are the steps which I have to do to reach to above screen.

Go to SSMS >> Toolbar >> Help >> Manage Help Settings (or type CTRL+ALT+F1) and click it.

Above click brought up following screen.

I clicked on Check for update online brought following screen up.

When I clicked on Update it brought me back to original screen which my daughter was able to bring up earlier.

I found it so interesting that what took me 2-3 minutes to figure out and the screen which I have never come across in my career I learned from my curiosity like my daughter.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Getting Columns Headers without Result Data – SET FMTONLY ON

May 26, 2011 by pinaldave

I was recently watching a videos online of TechEd 2011 USA (link) and I learned that SET FMTONLY ON is going to be replaced with enhanced DMVs in future versions of SQL Server. I really liked the new direction of the product. However, SET FMTONLY ON is really have done its job so far. I have used it many times so far and always find it useful.

SET FMTONLY ON returns only metadata to the client. It can be used to test the format of the response without actually running the query. When this setting is ON the resultset only have headers of the results but no data. If resultset has Spatial Results, it will have the spatial results tab as well, however, no spatial data.

USE AdventureWorks2008R2
GOSET FMTONLY ON;SELECT *FROM HumanResources.Department;SELECT *FROM Person.Address;SET FMTONLY OFF;GO

If you have turned on the execution plan (CTRL+M) while executing this settings, it will not return any execution plan as well.

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