EMP, DEPT Sample script

/****** Object:  Table [dbo].[DEPT]    Script Date: 19-05-2016 06:58:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DEPT](
[DEPTNO] [numeric](2, 0) NULL,
[DNAME] [varchar](14) NULL,
[LOC] [varchar](13) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[EMP]    Script Date: 19-05-2016 06:58:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EMP](
[EMPNO] [numeric](4, 0) NOT NULL,
[ENAME] [varchar](10) NULL,
[JOB] [varchar](9) NULL,
[MGR] [numeric](4, 0) NULL,
[HIREDATE] [datetime] NULL,
[SAL] [numeric](7, 2) NULL,
[COMM] [numeric](7, 2) NULL,
[DEPTNO] [numeric](2, 0) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (CAST(10 AS Numeric(2, 0)), N'ACCOUNTING', N'NEW YORK')
GO
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (CAST(20 AS Numeric(2, 0)), N'RESEARCH', N'DALLAS')
GO
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (CAST(30 AS Numeric(2, 0)), N'SALES', N'CHICAGO')
GO
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (CAST(40 AS Numeric(2, 0)), N'OPERATIONS', N'BOSTON')
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7369 AS Numeric(4, 0)), N'SMITH', N'CLERK', CAST(7902 AS Numeric(4, 0)), CAST(0x0000738200000000 AS DateTime), CAST(800.00 AS Numeric(7, 2)), NULL, CAST(20 AS Numeric(2, 0)))
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7499 AS Numeric(4, 0)), N'ALLEN', N'SALESMAN', CAST(7698 AS Numeric(4, 0)), CAST(0x000073C300000000 AS DateTime), CAST(1600.00 AS Numeric(7, 2)), CAST(300.00 AS Numeric(7, 2)), CAST(30 AS Numeric(2, 0)))
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7521 AS Numeric(4, 0)), N'WARD', N'SALESMAN', CAST(7698 AS Numeric(4, 0)), CAST(0x000073C500000000 AS DateTime), CAST(1250.00 AS Numeric(7, 2)), CAST(500.00 AS Numeric(7, 2)), CAST(30 AS Numeric(2, 0)))
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7566 AS Numeric(4, 0)), N'JONES', N'MANAGER', CAST(7839 AS Numeric(4, 0)), CAST(0x000073EC00000000 AS DateTime), CAST(2975.00 AS Numeric(7, 2)), NULL, CAST(20 AS Numeric(2, 0)))
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7654 AS Numeric(4, 0)), N'MARTIN', N'SALESMAN', CAST(7698 AS Numeric(4, 0)), CAST(0x0000749F00000000 AS DateTime), CAST(1250.00 AS Numeric(7, 2)), CAST(1400.00 AS Numeric(7, 2)), CAST(30 AS Numeric(2, 0)))
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7698 AS Numeric(4, 0)), N'BLAKE', N'MANAGER', CAST(7839 AS Numeric(4, 0)), CAST(0x0000740900000000 AS DateTime), CAST(2850.00 AS Numeric(7, 2)), NULL, CAST(30 AS Numeric(2, 0)))
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7782 AS Numeric(4, 0)), N'CLARK', N'MANAGER', CAST(7839 AS Numeric(4, 0)), CAST(0x0000743000000000 AS DateTime), CAST(2450.00 AS Numeric(7, 2)), NULL, CAST(10 AS Numeric(2, 0)))
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7788 AS Numeric(4, 0)), N'SCOTT', N'ANALYST', CAST(7566 AS Numeric(4, 0)), CAST(0x0000765400000000 AS DateTime), CAST(3000.00 AS Numeric(7, 2)), NULL, CAST(20 AS Numeric(2, 0)))
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7839 AS Numeric(4, 0)), N'KING', N'PRESIDENT', NULL, CAST(0x000074D100000000 AS DateTime), CAST(5000.00 AS Numeric(7, 2)), NULL, CAST(10 AS Numeric(2, 0)))
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7844 AS Numeric(4, 0)), N'TURNER', N'SALESMAN', CAST(7698 AS Numeric(4, 0)), CAST(0x0000748B00000000 AS DateTime), CAST(1500.00 AS Numeric(7, 2)), CAST(0.00 AS Numeric(7, 2)), CAST(30 AS Numeric(2, 0)))
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7876 AS Numeric(4, 0)), N'ADAMS', N'CLERK', CAST(7788 AS Numeric(4, 0)), CAST(0x0000767600000000 AS DateTime), CAST(1100.00 AS Numeric(7, 2)), NULL, CAST(20 AS Numeric(2, 0)))
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7900 AS Numeric(4, 0)), N'JAMES', N'CLERK', CAST(7698 AS Numeric(4, 0)), CAST(0x000074E100000000 AS DateTime), CAST(950.00 AS Numeric(7, 2)), NULL, CAST(30 AS Numeric(2, 0)))
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7902 AS Numeric(4, 0)), N'FORD', N'ANALYST', CAST(7566 AS Numeric(4, 0)), CAST(0x000074E100000000 AS DateTime), CAST(3000.00 AS Numeric(7, 2)), NULL, CAST(20 AS Numeric(2, 0)))
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (CAST(7934 AS Numeric(4, 0)), N'MILLER', N'CLERK', CAST(7782 AS Numeric(4, 0)), CAST(0x0000751400000000 AS DateTime), CAST(1300.00 AS Numeric(7, 2)), NULL, CAST(10 AS Numeric(2, 0)))
GO

Download SQL 2012 Evaluation

Download the SQL SERVER 2012 Evaluation Edition free for 180 days including SSDT.

click here to download

More information about version and Editions click here

To Learn SQL SERVER 2012 click here

For more information about SSRS and SSIS

Different Characters and Their Numbers in SQL SERVER

There are Different symbols in SQL Server useful while writing the Dynamic SQL Query.

The total list in SQL 2012 are 255.
You get to know the all Symbols while running below Query.

;WITH X AS(
SELECT 0 CharNumber, CAST ('' AS VARCHAR) Symbol
UNION ALL
SELECT X.CharNumber+1, CAST(CHAR(X.CharNumber+1) AS VARCHAR) FROM X WHERE X.CharNumber<=255
)SELECT * FROM X OPTION (MAXRECURSION 1000)

Few Symbols showing below.

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.