Difference between temp table and table variable

Different types of temp tables

Step 1:

We have different types of temporary data storage  objects, probably we call as temp tables.
1. TempTable
2. TableVariable
3.CTE (Common Table Expression)
.
     1. TempTable
         Again we have #table(Local Temp Table) and ##table(Global Temp Table)
     
        A local temp table, we will use in one session. It means that anywhere in the same SPID.

In above images, I have created one # temp table. I inserted the data into that and executed temp table separately. It gives the result. Because same(55) session.

The same # temp table, I am trying to access from SPID 57. It gives an error. It means that SCOPE (Validity) of # table is limited to that particular session only.

Now, it is ##(Global Temp Table).

I have created in SPID(55). I tried to access. It is giving fine results. 

Look into below image.

I can able to access it. Because the SPID(55) connection is opened. 

Now, I closed SPID 55 and trying to access ##table from SPID 57. It is giving error.

It means, if the connection is in open state (i.e., where we created the ## Table), we can access this table from anywhere. Once it closed, ## table also expires.

2. Table Variable.

Why we call this a Table Variable?. 

Declare @Table Table(BusinessEntityID int,

PhoneNumber VARCHAR(50),

PhoneNumberTypeID int,

ModifiedDate datetime)

We usually declares the variables with '@'. (EX: Declare @String VARCHAR(100) )

This table name also given like @Table.  That's why we call it as Table Variable.

I have created Table Variable, and Inserted the data into it.

INSERT INTO @TblVariable

SELECT TOP 10 * FROM Person.PersonPhone

SELECT * FROM @TblVariable

I selected all the script and executed once.

It is giving result fine.

Now, see below image.

I am executing SELECT * FROM @TblVariable separately.
 But it throws an error.

It means, the table variable must be in a batch. If we execute individually, it wont give any result.

# and ## tables will stored in tempdb system database. But table variable will store in RAM( Memory).
If more data in table variable, it consumes memory, so that server will get slow.

Same will be applicable for CTE which are meant for table variable.

Rename All files in Folder at Single time

Step 1:

Select the folder where your files exists.

In my existing files, I want to remove "dbo."

Step 2:

Create one batch file (.bat) in same folder(For my situation .bat file named as "Rename").

hear is the script for .bat file

@echo off
setlocal enabledelayedexpansion
set deletestring=dbo.
echo Ready to start
echo.
pause
echo.
for /f "delims==" %%F in ('dir /b ^| find "%deletestring%"') do (
   set oldfilename=%%F
   set newfilename=!oldfilename:%deletestring%=!
   Ren "!oldfilename!" "!newfilename!"
   )
echo.
echo All done
pause

Step 3:

Now run the .bat file.

Once done, you can able to see the result.

Deep Drive into Indexes

Indexes In depth Knowledge

--CREATE DATABASE HeapsDB

use HeapsDB
go


CREATE TABLE NumbersTable(NumberValue bigint not null, BiggerNumber bigint not null, CharacterColumn char(50))

DBCC IND('HeapsDB',NumbersTable ,-1)

We have around 14 types of pages
Page type 1 is a datpage
and Page type 10 is a IAM page

The Page is of 3 Parts.
1. Header
2. Data
3. Row Offset

Header for 96 bytes
Data depends on DataType + 7 Bytes(Identify the Nulls)
Row Offset is for Address of Every Record

One page = 8192 Bytes

DataPages --> Actual data Get Stored.
IAM page --> Locates the DataPages.

DBCC TRACEON(3604)
DBCC PAGE('DATABASENAME',1,PAGEID,1)
GO

insert into NumbersTable
select
NumberValue,
NumberValue + 5000000,
LEFT(replicate((cast(NumberValue as varchar(50))),50),50)
from
(
select NumberValue = ROW_NUMBER() over( order by newid() asc) from
master..spt_values a
cross apply master..spt_values b where a.type = 'P' and a.number <= 200 and a.number>0 and b.type = 'P' and b.number <= 200 and b.number>0
) a;

select page_count, index_depth,page_Level = index_level,page_count,record_count,
* from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID('NumbersTable'),NULL, NULL, 'DETAILED')

SELECT DB_NAME([ddips].[database_id]) AS [DatabaseName]
, OBJECT_NAME([ddips].[object_id]) AS [TableName]
, [i].[name] AS [IndexName]  FROM [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, NULL) AS ddips
INNER JOIN [sys].[indexes] AS i
ON [ddips].[index_id] = [i].[index_id]
AND [ddips].[object_id] = [i].[object_id]

--Page Allocation
SELECT allocated_page_iam_page_id ,allocated_page_page_id, next_page_page_id,previous_page_page_id
FROM sys.dm_db_database_page_allocations  (db_id(),OBJECT_ID('NumbersTable'),NULL, NULL, 'DETAILED')
WHERE page_type_desc is not null and page_type_desc = 'data_page'

--Create a Clustered Index
create clustered index CIX_NumbersTable on NumbersTable(NumberValue) with (maxdop=1)

-- Look into the data and Index pages

SELECT allocated_page_iam_page_id ,allocated_page_page_id, next_page_page_id,previous_page_page_id , page_level,page_type_desc

FROM sys.dm_db_database_page_allocations  (db_id(),OBJECT_ID('NumbersTable'),NULL, NULL, 'DETAILED')

WHERE page_type_desc is not null order by page_level desc

--and page_type_desc = 'index_page'

More than 8000 characters in Ms SQL Server handling with XML

Convert Delimited XML value in a column into Column (More than 8000 characters)

To handle more than 8000 characters, convert that data into XML format and insert into temp table.
Use this temp table wherever we want.

Step 1:
First convert your data into XML format.
If is comma separated varchar value, then go step 2.

Step 2:

For delimited value run below query.
select stuff((select distinct ','+cast(AddressID as varchar(max))
from Person.Address for xml path('') ),1,1,''))

Here my delimiter is ,(comma). I want to convert into XML.

Declare @Xml AS XML
SET @Xml =
( SELECT CAST (N'<r>' + REPLACE((select stuff((select distinct ','+cast(AddressID as varchar(max))
from Person.Address for xml path('') ),1,1,'')), ',', '</r><r>')  + '</r>' AS XML) AS [vals]  )
select @Xml

the selected XML value comes like below.

<r>29180</r>

<r>27611</r>

<r>22762</r>

<r>16843</r>

<r>17333</r>

<r>17665</r>

<r>24578</r>

<r>21802</r>

<r>26564</r>

<r>13205</r>

<r>499</r>

Convert that xml into table format.

SELECT A.value('.', 'varchar(max)') as AssessmentID FROM @Xml.nodes('r') AS FN(A) order by AssessmentID

The above query will give you table result. Insert insert this into temp table.

Below are the different ways to read the XML data in table format.

use [AdventureWorks2012]
go

DECLARE @XMLPath xml

set @XMLPath =(SELECT top 10 AddressID FROM Person.Address ORDER BY AddressID FOR XML PATH(''))

select @XMLPath
SELECT A.value('.', 'int') as AddressID FROM @XMLPath.nodes('AddressID') AS FN(A) order by AddressID


set @XMLPath =(SELECT top 10 AddressID FROM Person.Address ORDER BY AddressID FOR XML RAW('AddressIDs'))

SELECT A.value('@AddressID', 'int') as AddressID FROM @XMLPath.nodes('AddressIDs') AS FN(A) order by AddressID
select @XMLPath

Read one of the tag in XML format.

declare @DateXml xml =
'<Settings>
<NumOfRp>A</NumOfRp>
<InsID>ABCD</InsID>
<Terms>
<Term><Rpid>A</Rpid><MonthName>January</MonthName><Startdate>03/20/2015</Startdate><Enddate>03/24/2015</Enddate><Sortorder>1</Sorder></Term>
</Terms>
<YourComments>
 <CommentSettings>
     <AllPermitted>Y</AllPermitted><Predefined>Y</Predefined><Custom>Y</Custom><Personalized>N</Personalized><Century21st>Y</Century21st>
     <Unlimited>Y</Unlimited>
   </CommentSettings>
</YourComments>
</Settings>'

Now I want to take one of the tag CommentSettings as a XML data only.

SELECT
  Comments.query('.')
    AS Comments
FROM
  @DateXml.nodes('/Settings/Comments/RCCommentSettings')
    AS xmlData(Comments);

SELECT
  Comments.query('.')
    AS Comments
FROM
  @DateXml.nodes('/Settings/Comments')
    AS xmlData(Comments);

this XML data you may store in database.

Pass the variables to Another Procedure with OUTPUT Parameter

How to Pass the variables from one Procedure to Another Procedure with OUTPUT Parameter

Here is the Example:

Step 1: Create a Table 

CREATE TABLE  StudentsDetails

(

    [Studentid] [int] IDENTITY(1,1) NOT NULL,

    [Firstname] [nvarchar](200) NOT  NULL,

    [Lastname] [nvarchar](200)  NULL,

    [Email] [nvarchar](100)  NULL

)

Insert into StudentsDetails (Firstname, lastname, Email)

 Values('Vagalla', 'Suresh', 'vagalla.suresh@gmail.com')

Insert into StudentsDetails (Firstname, lastname, Email)

 Values('vagalla', 'Saran', 'vagalla.Saran@gmail.com')

Insert into StudentsDetails (Firstname, lastname, Email)

 Values('Rajesh', 'Kumar', 'Raj.Kumar@gmail.com')

Insert into StudentsDetails (Firstname, lastname, Email)

 Values('Prasad', 'Reddy', 'Prasad.reddy@gmail.com')

Insert into StudentsDetails (Firstname, lastname, Email)

 Values('Sukanya', 'reddy', 'Sukanya.reddy@gmail.com')

 select * from StudentsDetails

Step 2:

Declare the Parameters.

As of my concern, StudentID is the Input value and Expecting 3 output  values.

create  procedure GetStudentDetailsWithID

(

@studentid INT, --Input parameter ,  Studentid 

@studentFirstName VARCHAR (200) OUTPUT, -- Output parameter of student Firsst name

@studentLastName VARCHAR (200) OUTPUT, -- Output parameter of student Last name

@StudentEmail VARCHAR (200) OUTPUT -- Output Parameter of student email Address

)

AS

/*=====================================================================================================

With one input value, getting full details of the Student

=====================================================================================================*/

BEGIN

SELECT @studentFirstName = Firstname, 

@studentLastName = Lastname, 

@StudentEmail = email 

FROM StudentsDetails 

WHERE StudentID = @studentid

END

Step 3:

Now I am trying to get the Final result 

Create procedure ReturnFullData(@studentid int)

as

/*=====================================================================================================

With one input value, getting full details of the Student

=====================================================================================================*/

begin

Declare @studentFName as nvarchar(200)   -- Declare a variable to get studentFirstName

Declare @studentLName as nvarchar(50)     -- Declare a variable to get studentLastName

Declare @StudentEML as nvarchar(50)     -- Declare a variable to get Studentemail

Execute GetStudentDetailsWithID 1 , @studentFirstName = @studentFName output,  @studentLastName = @studentLName output, @StudentEmail = @StudentEML output

select @studentFName as FirstName,@studentLName as LastName ,@StudentEML   as Email   -- "Select" Statement is used to show the output from Procedure

end

-- here is your Fina output

exec ReturnFullData 1

SQL Database Mail With Table Format

SQL Database Mail With Table Format

DECLARE 

@Body NVARCHAR(MAX),

@TableHead VARCHAR(1000),

@TableTail VARCHAR(1000)

SET @TableTail = '</table></body></html>' ;

SET @TableHead = '<html><head>' + '<style>'

    + 'td {border: solid black;border-width: 1px;padding-left:6px;padding-right:6px;padding-top:4px;padding-bottom:4px;font: 11px arial} '

    + '</style>' + '</head>' + '<body>' + 'Report generated on : '

    + CONVERT(VARCHAR(50), GETDATE(), 106) 

    + ' <br> <table cellpadding=0 cellspacing=0 border=0>' 

    + '<tr> <td bgcolor=#E6E6FA><b>Special Offer ID</b></td>'

    + '<td bgcolor=#E6E6FA><b>Description</b></td>'

    + '<td bgcolor=#E6E6FA><b>Type</b></td>'

    + '<td bgcolor=#E6E6FA><b>Category</b></td>'

    + '<td bgcolor=#E6E6FA><b>Start Date</b></td>'

    + '<td bgcolor=#E6E6FA><b>Today Date</b></td>'

    + '<td bgcolor=#E6E6FA><b>EndDate</b></td></tr>' ;

SET @Body = ( 

SELECT td = CAST([SpecialOfferID] AS VARCHAR(100)),'',

td = [Description],'',

td = [Type],'',

td = [Category] ,'',

td = CONVERT(VARCHAR(30),[StartDate],120) ,'',

td = CONVERT(VARCHAR(30),GETDATE(),120) ,'',

td = CONVERT(VARCHAR(30),[EndDate],120) 

FROM [AdventureWorks2012].[Sales].[SpecialOffer]

ORDER BY [SpecialOfferID]

FOR XML PATH('tr'),ELEMENTS )

SELECT  @Body = @TableHead + ISNULL(@Body, '') + @TableTail

EXEC msdb.dbo.sp_send_dbmail 

@profile_name = 'Database2012Mail',

@recipients='vagalla.suresh@gmail.com',

@subject = 'Work Order List',

@body = @Body,

@body_format = 'HTML' ;

Result:

Create triggers Dynamically on Database

Create triggers Dynamically on Database

create Proc CreateTriggers
as
BEGIN
SET nocount ON

DECLARE @t TABLE
  (
     NAME VARCHAR(50)
  )

EXEC DropTrigger
/*

CREATE PROC DropTrigger
AS
  BEGIN
      SET nocount ON

      DECLARE @t TABLE
        (
           NAME VARCHAR(50)
        )

      INSERT INTO @t
      SELECT DISTINCT table_name
      FROM   information_schema.COLUMNS

      DECLARE @TableName VARCHAR(50)=''
      DECLARE cur CURSOR FOR
        SELECT NAME
        FROM   @t

      OPEN cur

      FETCH next FROM cur INTO @TableName

      WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @DROPTrigger VARCHAR(max)=''
            DECLARE @DROPTABLE VARCHAR(max)=''
            DECLARE @CREATELOGTBL VARCHAR(max)=''
            DECLARE @TRIGGERCREATE VARCHAR(max)=''
            DECLARE @STR VARCHAR(max)=''

            SELECT @STR = @STR + ', ' ++ '[' + column_name + ']'
            FROM   information_schema.COLUMNS
            WHERE  table_name = @TableName

            DECLARE @DropTRG VARCHAR(max)= 'if OBJECT_ID(''trg' + @tableName
              + ''') is not null' + Char(10) + 'begin' + Char(10)
              + 'drop trigger trg' + @tableName + ';' + Char(10)
              + 'end'

            SET @DROPTrigger= @DropTRG

            --EXEC(@DROPTrigger)
            DECLARE @CREATE VARCHAR(100)='CREATE TABLE trg' + @tableName +
                                         '_History('
            DECLARE @Drop VARCHAR(max)= 'if OBJECT_ID(''trg' + @tableName
              + '_History'') is not null' + Char(10) + 'begin'
              + Char(10) + 'drop table trg' + @tableName
              + '_History' + Char(10) + 'end'

            SET @DROPTABLE= @drop

            EXEC(@DROPTrigger)

            EXEC(@DROPTABLE)

            FETCH next FROM cur INTO @TableName
        END

      CLOSE cur

      DEALLOCATE cur

      SET nocount OFF
  END

*/
INSERT INTO @t
SELECT DISTINCT table_name
FROM   information_schema.COLUMNS

DECLARE @TableName VARCHAR(50)=''
DECLARE cur CURSOR FOR
  SELECT NAME
  FROM   @t

OPEN cur

FETCH next FROM cur INTO @TableName

WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @DROPTrigger VARCHAR(max)=''
      DECLARE @DROPTABLE VARCHAR(max)=''
      DECLARE @CREATELOGTBL VARCHAR(max)=''
      DECLARE @TRIGGERCREATE VARCHAR(max)=''
      DECLARE @STR VARCHAR(max)=''

      SELECT @STR = @STR + ', ' ++ '[' + column_name + ']'
      FROM   information_schema.COLUMNS
      WHERE  table_name = @TableName

      DECLARE @DropTRG VARCHAR(max)= 'if OBJECT_ID(''trg' + @tableName
        + ''') is not null' + Char(10) + 'begin' + Char(10)
        + 'drop trigger trg' + @tableName + ';' + Char(10)
        + 'end'

      SET @DROPTrigger= @DropTRG

      --EXEC(@DROPTrigger)
      DECLARE @CREATE VARCHAR(100)='CREATE TABLE trg' + @tableName + '_History('
      DECLARE @Drop VARCHAR(max)= 'if OBJECT_ID(''trg' + @tableName
        + '_History'') is not null' + Char(10) + 'begin'
        + Char(10) + 'drop table trg' + @tableName
        + '_History' + Char(10) + 'end'

      SET @DROPTABLE= @drop

      --EXEC(@DROPTABLE)
      DECLARE @STRCRE VARCHAR(max)=''

      SELECT @STRCRE = @STRCRE + ', ' + CASE WHEN character_maximum_length IS
                       NULL
                       THEN '['+
                              column_name
                              +']'+' '+data_type ELSE '['+column_name+']'+' '+
                       data_type+'('+
                              CONVERT(VARCHAR(10), Replace(
                       character_maximum_length,
                       '-1',
                              'MAX'))+')' END
      FROM   information_schema.COLUMNS
      WHERE  table_name = @TableName

      SET @CREATELOGTBL= @CREATE + ' '
                         + RIGHT(@STRCRE, Len(@STRCRE)-1)
                         + ',Actions varchar(20),CreatedDate1 dateTime)'
                         + Char(10)
      --EXEC (@CREATELOGTBL)
      SET @TRIGGERCREATE= 'create trigger trg' + @tableName + ' on ' + '['
                          + @tableName + ']' + Char(10)
                          + ' for insert,update , delete ' + Char(10)
                          + 'AS ' + Char(10) + 'BEGIN' + Char(10)
                          + 'IF EXISTS(SELECT * FROM inserted)'
                          + Char(10) + 'BEGIN' + Char(10) + 'INSERT INTO trg'
                          + @tableName + '_History' + '('
                          + RIGHT(@STR, Len(@str)-1)
                          + ',Actions, CreatedDate1' + ')'
                          + ' SELECT *,''INSERTED'',getdate() FROM inserted'
                          + Char(10) + 'END' + Char(10)
                          + 'IF EXISTS(SELECT * FROM deleted)'
                          + Char(10) + 'BEGIN' + Char(10) + 'INSERT INTO trg'
                          + @tableName + '_History' + '('
                          + RIGHT(@STR, Len(@str)-1)
                          + ',Actions, CreatedDate1' + ')'
                          + ' SELECT *,''DELETED'',getdate() FROM deleted'
                          + Char(10) + 'END' + Char(10) + 'END'

      EXEC(@DROPTrigger)

      EXEC(@DROPTABLE)

      EXEC (@CREATELOGTBL)

      EXEC (@TRIGGERCREATE)

      FETCH next FROM cur INTO @TableName
  END

CLOSE cur

DEALLOCATE cur

SET nocount OFF

end