SQL TIps

Database Diagram support object cannot be install error-SSMS TIP# 61

  Problem:-  It is general problem that you are interested in generating the database diagram of a database and when you right click on database diagrams folder of database and try to create a new database diagram you get following error as shown in below snap. Solution:- The above snap says that you are not …

Grouping Sets–Good to know feature TIP# 60

  Grouping sets is one of the cool feature came in SQL SERVER 2008. Lets understand here with problem and solution. Problem:-  Suppose , We want  an aggregation result in a query with different groups. Firstly we want aggregated result on first column then combination of First & second column then other column combination. So, …

Find last statistics updated date detail ?–Maintenance TIP #59

  Problem:- One of the pain point in any SQL engineer  is “Performance”. There are various reasons due to which your SQL Server database is slow. One of the possible reason is your maintenance.   You don’t know when statistics last updated and take further step if those are not updated Solution:- Here we have simple …

Merge Statement one of the way to synch destination from source table TIP# 58

  Problem:-  Suppose you have a source table and one destination table and you want to synch destination table from source table. means if record is already exists then you need to update and the record with latest value and insert the record if record is not exists. Solution:-  Although there are various ways exist …

What is TVP (Table Value Parameter) & How to use it ? TIP #57

  This is one of the interesting feature which I like most. Instead of passing values from collection one by one pass entire collection to stored procedure as a table value parameter. I know above statement is not digestive enough so lets understand this by an example. Suppose , I have a table tblStudent with …

Maintenance of fragmented Heap table–TIP #55

  Friends, In last post  post #54  we understood what is a Heap table. Now the challenge is what if this table is highly fragmented so. Now our task is to run maintenance of this highly fragmented table. So you have to run following command “ALTER TABLE  TABLENAME REBUILD; For example  I run the Rebuild …

What is Heap table in SQL Server and How to get all the heap table from a database ? TIP # 54

  Heap table:- A table without cluster index is called Heap table. Now you are thinking why we are talking about this. So as  SQL developer we should aware and In next post I will explain which is related to maintenance how to Rebuild a heap table. Now our next question is how to determine …

How Enable /Disable all CONSTRAINT on a table ? tip #53

  Dear Friends, Although , When we have designed our database he/she knows what type of constraints is require to make data perfect and the Database designer applies those constraints but it might be possible that sometime we have to disable those constraints make some tweak with the data in the table and then again …

A hidden feature sp_MSforeachdb TIP #52

  As we discussed yesterday a hidden feature tip #51 which is sp_MSForeachtable . sp_MsForeachtable is useful to run command on each table of selected database. Now what if we want to run command on each database also ? then in that case we need to take help of sp_MsForeachDB. Sp_MsForEachDB  by the name it …