SQL Server – Multiple ways to find identity column

SQL Server – Multiple ways to find identity column.


Swapping Of Records by column in Sqlserver

At times, we may accidently insert data into a wrong column if we have two identical columns (both having the same data type). To fix this later on we can delete the data from the wrong column and insert it in the proper one, but that becomes a lot of work if you know how to swap data between the two columns through SQL.
Swapping can be done :
             1.with the help of a temporary variable .
Suppose we have a table named Employee with 3 columns: ID, FirstName, LastName.
              ID: int 
              FirstName: varchar(10)
              LastName: varchar(10)
and we have inserted some rows to the table.
              Insert into Emplyee values(1,’Kumar’,’Mahesh’)
              Insert into Emplyee values(2,’Kumari’,’Abhilasha’)
              Insert into Emplyee values(3,’Kumar’,’Anand’)
              Insert into Emplyee values(4,’Kumar’,’Binit’)
Here we have wrongly inserted LastName in place of FirstName and we need to swap them.
1. Swap with the help of temporary variable:
DECLARE @temp AS varchar(10)
UPDATE Employee1 SET @temp=LastName, LastName=FirstName, FirstName=@temp
  The resulting  table can be verified.

UPDATE Employee1 SET LastName=FirstName, FirstName=LastName
This query can also swap the column data.
Now we can see the difference by :
      Select ID,FirstName, LastName from Employee
This swapping be done for more than two columns also. Suppose we need to swap three columns named value1,value2,value3
UPDATE Employee SET value1=value3, value2=value1, value3=value2
So like this we can swap column data. Hope freshers  will find it helpful . : )

SQL Server: Controlling the growth of msdb database caused by Database Mail

This should be the common issue faced by many of us where the database mail is used to send bulk mails on a regular basis. We have a database server which is specifically used for sending weekly newsletters to all the subscribed users. The number of subscriptions increased over a period of time and so as the number of newsletters. One day the disk drive, holding the system databases, started alerting for the low disk space. On investigation, I found that the msdb database has grown to 7 GB in size!

         As we know that msdb maintains various types of history information like backup history, jobs history, sent mails and attachments history etc. Now the question is to find out the table(s) holding the largest data. I have found the following query from the Net which helped me to find out the name of the tables in descending order of space consumed on the disk.

(As the above issue had already been fixed, I can not show you the 7 GB grown msdb. For this demo I am running this query on a server having 1.5 GB grown msdb)

use msdb
sp_helpdb msdb

msdb database report

01 --Query 1 - Checking the size of tables in a database
02 select
03     object_name(i.object_id) as ObjectName,
04     i.[name] as IndexName,
05     sum(a.total_pages) as TotalPages,
06     sum(a.used_pages) as UsedPages,
07     sum(a.data_pages) as DataPages,
08     (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
09     (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
10     (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
11 from
12     sys.indexes i
13     inner join sys.partitions p
14         on i.object_id = p.object_id and i.index_id = p.index_id
15     inner join sys.allocation_units a
16         on p.partition_id = a.container_id
17 group by
18     i.object_id,
19     i.index_id,
20     i.[name]
21 order by
22     sum(a.total_pages) desc,
23     object_name(i.object_id)
24 go

Checking the size of tables in a database

          We can see that sysmail_mailitems table is occupying most of the space on the disk, around 1.3 GB spanning across 171812 pages. This table stores the database mail messages which are sent, unsent, retrying, and failed. Microsoft recommends maintaining this table by periodically deleting the messages based on the organizations document retention program. The sysmail_delete_mailitems_sp procedurelocatedin the msdb database permanently deletes e-mail messages from this table. The syntax of the procedure is shown below

1 sysmail_delete_mailitems_sp 
2     [ [ @sent_before = ] 'sent_before' ]
3     [ , [ @sent_status = ] 'sent_status' ]

          The date option allows deleting e-mails before the specified date and the status option allows deleting e-mails of a particular type i.e. sent, unsent, retrying, and failed.

          As deleting a huge amount of records in one shot takes a good amount of time we can purge the data in small batches. The below query will identify since when the data is lying in the table. This is especially useful when the data is piled up over a long period of time.

01 --Query 2 - Checking the records by year, month
02 select
03     datepart(yy,sent_date) [Year],
04     datepart(mm,sent_date) [Month],
05     count(*) 'NoOfRows'
06 from
07     sysmail_mailitems
08 group by
09     datepart(yy,sent_date),
10     datepart(mm,sent_date)
11 order by
12     1,2

Checking the records by year and month  

        The table has data for two months. Let’s keep the data for March and deleted the February data. The following query will do the job,

1 --Query 3 – Purging the data before 1st March 2011
2 DECLARE @DelDate nvarchar(20) ;
3 SET @DelDate = '03/01/2011'
4 EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DelDate;

Purging the data

           Now run the Query 1 and check the output. The total table space which was 1372 MB has come down to 623 MB and also the Total Pages from 171812 to 79836.

 Checking the size of tables in a database

          Let’s check the database size using,

 sp_helpdb msdb

msdb database reporting

          No luck???!!! In fact the log size has increased. Don’t worry, now we need to truncate the log and shrink the database so that the free space will be release to the operating system. Run the following code and recheck the database size.

1 --Query 4 - Truncate the log and shrink the database
2 checkpoint
4 backup log msdb with truncate_only
6 dbcc shrinkdatabase ('msdb')
8 sp_helpdb msdb

 msdb database report

 It worked…Smile

How To Send Mail From Database In Tabular Form

Here is the sample code.

  [Rank]  [int],
  [Player Name]  [varchar](128),
  [Ranking Points] [int],
  [Country]  [varchar](128)

INSERT INTO #Temp SELECT 1,'Rafael Nadal',12390,'Spain' UNION ALL SELECT 2,'Roger Federer',7965,'Switzerland' UNION ALL SELECT 3,'Novak Djokovic',7880,'Serbia'
SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','', [Ranking Points] AS 'td','', Country AS 'td' FROM #Temp ORDER BY Rank FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Tennis Rankings Info</H3> <table border = 1> <tr> <th> Rank </th> <th> Player Name </th> <th> Ranking Points </th> <th> Country </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile @body = @body, @body_format ='HTML', @recipients = 'bruhaspathy@hotmail.com', -- replace with your email address @subject = 'E-mail in Tabular Format' ;

The HTML output from the above example looks like this:

<h3>Tennis Rankings Info</h3>
<table border="1">
  <th>Rank </th>
  <th>Player Name </th>
  <th>Ranking Points </th>
  <th>Country </th>
  <td>Rafael Nadal</td>
  <td>Roger Federer</td>
  <td>Novak Djokovic</td>

And this is how the email would look:

how to send an email from sql server with data in a tabular format

As another example, if you want to change this and select FirstName, LastName and EmailAddress from Person.Contact in the AdventureWorks database and order it by LastName, FirstName you would make these changes:


SET @xml = CAST(( SELECT [FirstName] AS 'td','',[LastName] AS 'td','', [EmailAddress] AS 'td' FROM Person.Contact ORDER BY LastName, FirstName FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Contact Info</H3> <table border = 1> <tr> <th> First Name </th> <th> Last Name </th> <th> Email </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile @body = @body, @body_format ='HTML', @recipients = 'bruhaspathy@hotmail.com', -- replace with your email address @subject = 'E-mail in Tabular Format' ;

SQL SERVER – Server Side Paging in SQL Server 2005 or 2008

–creating the table.
create table dbo.EmployeeTable
EmpId int identity primary key,
EmpName varchar(40),
Sal int

–inserting data into table
insert into EmployeeTable values(‘ranga’,2000),(‘rajesh’,3000),(‘kumar’,4000),(‘swapna’,5000),(‘gopi’,6000)

–creating view.
create view pagingView
select empid,empname,sal from EmployeeTable

–creating procedure
CREATE PROCEDURE [dbo].[spPaging]
@PageNumber int,
@PageRecordsSize int

SELECT TOP(@PageRecordsSize) * FROM
empid, empname, sal, TotalRows=Count(*) OVER()
FROM pagingView
A WHERE A.Row_ID > ((@PageNumber-1)*@PageRecordsSize)

exec spPaging 1,5


NTILE(): This is one of the most coolest function provided by T-SQL. This is very useful while grouping a dataset into multiple sets i.e. in case if you want to distribute the result set into more than one tables or destination, this function is useful in accomplishing this task.

Let’s consider we have a table with 1 million records, and we want to divide this 1 million records across five tables, then we can achieve this by using NTILE () function.

The functionality of NTILE () function is same as other ranking functions i.e. ROW_NUMBER(), RANK() and DENSE_RANK(), except it takes one parameter named “integer_expression” i.e. NTILE(integer_expression). The integer_expression signifies the number of groups the NTILE function will create to distribute the data. integer_expression can be of type int, or bigint but must be positive integer. If you specify any other value like 2.5 or -3, it will throw the below error:

The function ‘ntile’ takes only a positive int or bigint expression as its input.

Let’s consider the example of Employee table, where we have 7 records, and our purpose is to distribute these records into two separate tables, one containing highest salaries and the other with lower salaries. Then the query goes like this:

For the table with Highest Salaries:

[ Copy to Clipboard ] | [ View Source ]
SELECT NTILE(2) OVER( ORDER BY salary DESC) AS GroupID,* FROM dbo.employee
) InnQ
WHERE InnQ.GroupID = 1

For the table with Lowest Salaries:

SELECT NTILE(2) OVER( ORDER BY salary DESC) AS GroupID,* FROM dbo.employee
) InnQ
WHERE InnQ.GroupID = 2

You might have noticed here that in the first set we have four records while in the second one we have three records. This is because; we have odd number of records in the employee table from where these sets are derived. When the integer_expression parameter is divisible with the total number of records in the source table, then we can get even number of records in all the sets derived from it or else it will start filling the remaining records starting from first set.

Let’s dissect the query a little:

SELECT NTILE(2) OVER( ORDER BY salary DESC) AS GroupID,* FROM dbo.employee;

GroupID column has two distinct numbers on the basis of which the records are divided into two separate sets as shown below.

As the total number of records in the employee table is not divisible by the integer_expression parameter in NTILE (), we have two groups with different sizes differ by one member. The group with higher number of members comes before the group with small number of members.

Using in NTILE():

clause in NTILE () function increases it functionality a little more. Here, we will see how partition by affects NTILE () in the above example. Let’s consider the below query to group records by department wise.


Let’s dissect the above query and see the process behind it.

The OVER clause in the query will partition the employee table as per DeptNo i.e. into three sub-sets. Then the NTILE(2) will divide each partition into groups of two as show in the below figure.