In this tutorial, we will share to you how to check available colum in a table. If you want to check if the column is already available in the table, you can use system views like sys.columns or INFORMATION_SCHEMA.COLUMNS.

Step 1

First, let us create the dataset:

USE TEMPDB;
CREATE TABLE TESTING(ID INT, NAME VARCHAR(100))

Step 2

Suppose you want to find out the existence of the column named NAME and print a message. You can do it by using any of the following methods:

IF EXISTS
(
SELECT * FROM SYS.COLUMNS
WHERE NAME='NAME' AND OBJECT_ID=OBJECT_ID('TESTING')
)
PRINT 'COLUMN EXISTS'
--
IF EXISTS
(
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='NAME' AND TABLE_NAME='TESTING'
)
PRINT 'COLUMN EXISTS'

There is also a shorter way to do this. You can use COL_LENGTH system function:

IF (SELECT COL_LENGTH('TESTING','NAME')) IS NOT NULL
PRINT 'COLUMN EXISTS'

What it does is that it finds the length of the column. If it is null, the column does not exist in the table otherwise it exists.

Simple and fast, right?

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Maybe you were recently doing a clean up of your website database, then you create some tables on your database but never adding any new rows to it. In this tutorial, I will tell you how to detect empty row in a table on SQL Server database.

Here's a simple query to list all empty rows in tables in your SQL Server database using a Dynamic Management View called dm_db_partition_stats. This will return page and row-count information for every partition in the current database:

;WITH EmptyRows AS
(
   SELECT SUM(row_count) AS [TotalRows],
          OBJECT_NAME(OBJECT_ID) AS TableName
   FROM sys.dm_db_partition_stats
   WHERE index_id = 0 OR index_id = 1
   GROUP BY OBJECT_ID
)
SELECT * FROM EmptyRows
WHERE [TotalRows] = 0

And here's the output:

Easy right?

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

SQL Server database files are organized in 8KB (8192 bytes) chunks, called pages. When we create the first row in a table, SQL Server allocates an 8KB page to store that row. Similarly every row in every table ends up being stored in a page.

Say one of the pages in your table is corrupt and while repairing the corrupt pages, you may eventually end up loosing some data. You may want to find out which records are on the page. To do so, use the following undocumented T-SQL %%physloc%% virtual column:

USE AdventureWorks2014
GO
SELECT *, %%physloc%% AS physloc
FROM Person.AddressType
ORDER BY physloc;

As you can see, the last column represents the record location. However the hexadecimal value is not in a human readable format. To read the physical record of each row in a human readable format, use the following query:

SELECT *
FROM Person.AddressType
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)

The sys.fun_PhysLocCracker function takes the %%physloc%% and represents a human readable format fileid, pageid i.e. 880 and record number on the page 880.

If you are interested in knowing what’s inside the sys.fn_PhysLocCracker function, use sp_helptext as follows:

EXEC sp_helptext 'sys.fn_PhysLocCracker'
which display the definition of sys.fn_PhysLocCracker
-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocCracker
--
-- Description:
--    Cracks the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
create function sys.fn_PhysLocCracker (@physical_locator binary (8))
returns @dumploc_table table
(
    [file_id]    int not null,
    [page_id]    int not null,
    [slot_id]    int not null
)
as
begin
    declare @page_id    binary (4)
    declare @file_id    binary (2)
    declare @slot_id    binary (2)
    -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot
    --
    select @page_id = convert (binary (4), reverse (substring (@physical_locator, 1, 4)))
    select @file_id = convert (binary (2), reverse (substring (@physical_locator, 5, 2)))
    select @slot_id = convert (binary (2), reverse (substring (@physical_locator, 7, 2)))
   
    insert into @dumploc_table values (@file_id, @page_id, @slot_id)
    return
end
The undocumented sys.fn_PhysLocCracker works on SQL Server 2008 and above.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this blog post “Report Viewer wrap text”, we are going to learn an important trick of text wrapping in SSRS reports for long texts (without space and any separator) exceeding the width of a report column’s defined width and being rendered in a Report Viewer control of .NET Application.

Impact of text wrapping in report project and on report server

Lets start this too with some demo and sample codes. Follow these steps to create a sample application and produce this on your machine:

1. Create a report server project in SQL Server Data Tools (SSDT)

2. Add a Datasource and a Dataset in the report.

3. In query type of Dataset, select “Text”.

4. Add below as query in “Query” box of the Dataset. You can also write your own query.

SELECT 'ThisTextDoesNotContainAnySpace' AS Comment
UNION ALL
SELECT 'ThisTextDoesNotContainAnySpace' AS Comment
UNION ALL
SELECT 'ThisTextDoesNotContainAnySpace' AS Comment

5. In above query, we have a text string without any space as a column named “Comment”. Now add a “Table” control from Report Item’s tollbox on the report body and select this column named “Comment” in the table to display. Remember you have fixed the column width less than the above string’s length to produce the mentioned issue.

6. Click on “preview” button to check the output locally. The text should be wrapped to the next row if it exceeds the width of the column. Below is the screen shot:

7. In above image you can see that this text could not be accommodated in one row in this column and has been wrapped in multiple rows. But the width of the column is constant as desired.Below i will show you the output of this report after deploying it on report server. I have deployed the same report on my local report server and executed from report server. Below is the screen shot:

Again you can see that we have the desired output i.e. text wrapping is taken care as per the column width.

Impact of text wrapping in ReportViewer Control

Now we have to check the same report in a report viewer control. To do so, follow these steps:

1. Create a ASP.NET Web Application in Visual Studio.

2. Make appropriate changes in web.config file. What i did on my machine is below:

<system.webServer>
<handlers>
<add name="ReportViewerWebControlHandler" preCondition="integratedMode" verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
</handlers>
</system.webServer>

3. Add an .aspx web form in the project and on this page register the report viewer control as below. Before that also add the reference of reportviewer assembly in project references.

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

4. Add a report viewer control on this page and a script manager too like below:

<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<div>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Width="1000">
</rsweb:ReportViewer>
</div>
</form>

5. Write a code to display your report from this report viewer control in .cs file of this web page like below. You can modify the below code as per your need. I did not have any parameter in my report to keep this demo simple and to focus in main issue.

if (!IsPostBack)
{
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
 
Microsoft.Reporting.WebForms.ServerReport serverReport = ReportViewer1.ServerReport;
 
// Set the report server URL and report path
serverReport.ReportServerUrl = new Uri("http://mymachine/reportserver");
serverReport.ReportPath = "/TestReports/TextWrapping";
}

6. Build your application and open the web page in your browser. You will have an output as below:

In above figure, you can see that the output of the same report has been changed. The text wrapping is not working as it worked in above cases which was desired too. In this case width of the column is expanding to accommodate the text instead of wrapping the text in more than one row.

Enable Text Wrapping in SQL Server Reporting Server rendered in report viewer control on web page

As of now and what i know, there is no any staright forward method to achieve this task and to do this we have to folk it as below:

1. To render this report with text wrapping, follow these steps;
2. To avoid rework, go to your report project and click on the textbox which holds this data inside the table and press ctrl + X to cut it from the table.
3. Drag and drop a rectangle control in the table’s cell from the Report Item’s toolbox.
4. Press ctrl + V to put the textbox inside the rectangle.
5. Set the border of the rectangle as your textbox has.
6. Deploy the report and see the output in your browser:

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this post, we will convert a given date of range into list of dates as per the business requirement. Just have a look on as these three mentioned demo table below which has three columns CustomerID, StartDate and EndDate. So, we want to generate the date list for the given date range for each customer. For example if we have a entry as CustomerID – “1”, StartDate – “10-Dec-2012” and EndDate – “19-Mar-2013”, then this should return a date list for CustomerID 1 with 4 rows. First row for 10-Dec-12 to 31-Dec-12, Second row for 01-Jan-13 to 31-Jan-13, Third row for 01-Feb-13 to 28-Feb-13 and fourth row for 01-Mar-13 to 19-Mar-13.

Mind that in first row, we have started with the actual start date of the given date range and in the last row, we have put the end date as actual end date. But, if there is any more row between first row and last row, it has a date range as whole month.

Look at this table of date range below:

And we want the required output from this date range is as below:

How to Generate The Output

Step 1

Create a table as below:

CREATE TABLE DateRange
(
CustomerID INT,
StartDate DATE,
EndDate DATE
)

Step 2

Insert demo values for customer and date range

INSERT INTO DateRange
VALUES (1, '10-Dec-12', '19-Mar-13'),
(2, '20-Mar-14', '10-Jul-14')

Step 3

Create another table to hold Serial number so that we can apply a join with this table to generate the date list for given date range. You can also use a demo DateList table to get the desired output. In this demo, we are using serial numbers to generate the desired output:

CREATE TABLE TableSerialNumber
(
RowNumber INT
)

Step 4

Insert serial numbers up to 100 for this demo using sys.columns table. You can also use another way to insert this.

INSERT INTO TableSerialNumber
SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM SYS.COLUMNS

Step 5

Now, write a SQL query to extract the output as required. You can also try with some different way using DateList table too. Here, we're gonna use a serial number to generate the rows and date list dynamically for the given date range.

SELECT A.CustomerID
,CONVERT(VARCHAR(20), (CASE WHEN B.RowNumber = 1 THEN A.StartDate
ELSE DATEADD(MONTH, B.RowNumber - 1, DATEADD(DD, -(DATEPART(DD, A.StartDate)) + 1, A.StartDate)) END), 106) AS FromDate
,CONVERT(VARCHAR(20), (CASE WHEN (DATEADD(DD, -(DATEPART(DD, A.StartDate)), DATEADD(MONTH, B.RowNumber, A.StartDate))) < A.EndDate THEN
DATEADD(DD, -(DATEPART(DD, A.StartDate)), DATEADD(MONTH, B.RowNumber, A.StartDate)) ELSE A.EndDate END), 106) AS ToDate
FROM DateRange A
INNER JOIN TableSerialNumber B ON B.RowNumber <= (DATEDIFF(MONTH, A.StartDate, A.EndDate) + 1)

And you're done! You can also generate it in some other way too. For example, you can achieve this with a demo DateList table too. It's your creativity to find your own way.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

There is a long list of new features getting introduced in SQL Server 2016. In this post, we would talk about one of the security feature called Dynamic Data Masking. How to Use it?

Whenever you access your account on your bank site, would you be comfortable in seeing your credit card or bank account number in clear text on the web page? There are multiple ways to do this at the application level, but as a human nature, it leaves room for error. One small mistake from the developer can leak sensitive data and can cost a huge loss. Wouldn’t it be great if a credit card number would be returned with only its last 4 digits visible – XXXX-XXXX-XXXX-1234 with no additional coding? Sounds interesting, read on!

Before experimenting this feature please remember that if you are using CTP2.0 then you need to turn on trace flags using below command.

DBCC TRACEON(209,219,-1)

If you don’t enable, then here is the error which you would receive while trying this sample script given later.

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ‘masked’.

Don't forget that this is SQL Server 2016 feature. Running the script on earlier version of SQL would cause below:

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ‘MASKED’.
Msg 319, Level 15, State 1, Line 14
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

And here is the script to create objects (database, table, masked column):

SET NOCOUNT ON
GO

Drop database MaskingDemo, if already exists

USE [master]
GO
IF DB_ID('MaskingDemo') IS NOT NULL
BEGIN
ALTER DATABASE [MaskingDemo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [MaskingDemo]
END

Create new database called MaskingDemo

CREATE DATABASE MaskingDemo
GO
USE MaskingDemo
GO

Create table with different data type columns

CREATE TABLE MyContacts (
ID INT IDENTITY(1, 1) PRIMARY KEY
,fName NVARCHAR(30) NOT NULL
,lName NVARCHAR(30) NOT NULL
,CreditCard VARCHAR(20) NULL
,SalaryINR INT NULL
,OfficeEmail NVARCHAR(60) NULL
,PersonalEmail NVARCHAR(60) NULL
,SomeDate DATETIME NULL
)

Insert a Row

INSERT INTO [dbo].[MyContacts]
([fName],[lName] ,[CreditCard],[SalaryINR],[OfficeEmail],[PersonalEmail], SomeDate)
VALUES('Rebecca','C','1234-5678-1234-5678',999999,'[email protected]','[email protected]', '31-March-2013')
GO

Apply Masking

ALTER TABLE MyContacts
ALTER COLUMN CreditCard ADD MASKED
WITH (FUNCTION = 'partial(2,"XX-XXXX-XXXX-XX",2)')
ALTER TABLE MyContacts
ALTER COLUMN SalaryINR ADD MASKED
WITH (FUNCTION = 'default()')      -- default on int
ALTER TABLE MyContacts
ALTER COLUMN SomeDate ADD MASKED
WITH (FUNCTION = 'default()')      -- default on date
ALTER TABLE MyContacts
ALTER COLUMN fname ADD MASKED
WITH (FUNCTION = 'default()')      -- default on varchar
ALTER TABLE MyContacts
ALTER COLUMN OfficeEmail ADD MASKED
WITH (FUNCTION = 'email()')
GO

Create a new user and grant select permissions

USE MaskingDemo
GO
CREATE USER WhoAmI WITHOUT LOGIN;
GRANT SELECT ON MyContacts TO WhoAmI;

As we can see above, those fields which are masked are showing obfuscated data based on masking rule.
For your information, versions after CTP2 release, the trace flag will not be needed. If you add trace flag, you would start getting “Incorrect syntax” error.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article, I'm introducing you new feature in SQL Server 2016 CTP 2.0 named Query Store. This is a very useful feature for the DBA and developers from the performance point of view.

Query store feature allows to captures multiple query plan for a query and run time statistics. Query store can store multiple execution plans per query, it can force query processor to use a particular execution plan which is referred as plan forcing using USE PLAN query hint.

By default, Query Store is not active so you can enable it in two ways:

Step 1

First Using SSMS, Right Click on DatabaseName -> Go to properties -> Query Store options -> Enable -> True

Step 2

Second way to enable it by using ALTER Database script in this manner:

ALTER DATABSE  Database_name SET QUERY_STORE = ON;

Query store option is not enabled for master or tempdb database. If you try to enable it then you get below error:

Msg 12420, Level 16, State 1, Line 1

Cannot perform action because Query Store is not started up for this database.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

Step 3

To determine the current options available for query store we can query the system view sys.database_query_store_options. Query stores contains two stores:

1. Plan store – Stores execution plan information
2. Running Stats store – Stores execution statistics information

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article, I will explain you how to parse XML or import XML to SQL Server Database Table.

Here are the XML File:

<?xml version="1.0"?>
<Orders>
<Order OrderNumber="99503" OrderDate="2013-10-20">
  <Address Type="Shipping">
    <Name>Ellen Adams</Name>
    <Street>123 Maple Street</Street>
    <City>Mill Valley</City>
    <State>CA</State>
    <Zip>10999</Zip>
    <Country>USA</Country>
  </Address>
  <Address Type="Billing">
    <Name>Tai Yee</Name>
    <Street>8 Oak Avenue</Street>
    <City>Old Town</City>
    <State>PA</State>
    <Zip>95819</Zip>
    <Country>USA</Country>
  </Address>
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
  <Items>
    <Item PartNumber="872-AA">
      <ProductName>Lawnmower</ProductName>
      <Quantity>1</Quantity>
      <USPrice>148.95</USPrice>
      <Comment>Confirm this is electric</Comment>
    </Item>
    <Item PartNumber="926-AA">
      <ProductName>Baby Monitor</ProductName>
      <Quantity>2</Quantity>
      <USPrice>39.98</USPrice>
      <ShipDate>2013-05-21</ShipDate>
    </Item>
  </Items>
</Order>
</Orders>

To Query XML File, you need to store in to xml variable @MyXML:

DECLARE @MyXML XML
SET @MyXML = '<?xml version="1.0"?>
<Orders>
<Order OrderNumber="99503" OrderDate="2013-10-20">
  <Address Type="Shipping">
    <Name>....</Name>
    <Street>123 Maple Street</Street>
    <City>Mill Valley</City>
    <State>CA</State>
    <Zip>10999</Zip>
    <Country>...</Country>
  </Address>
  <Address Type="Billing">
    <Name>Tai Yee</Name>
    <Street>8 Oak Avenue</Street>
    <City>Old Town</City>
    <State>PA</State>
    <Zip>95819</Zip>
    <Country>...</Country>
  </Address>
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
  <Items>
    <Item PartNumber="872-AA">
      <ProductName>Lawnmower</ProductName>
      <Quantity>1</Quantity>
      <USPrice>148.95</USPrice>
      <Comment>Confirm this is electric</Comment>
    </Item>
    <Item PartNumber="926-AA">
      <ProductName>Baby Monitor</ProductName>
      <Quantity>2</Quantity>
      <USPrice>39.98</USPrice>
      <ShipDate>2013-05-21</ShipDate>
    </Item>
  </Items>
</Order>
</Orders>

Now, you use SQL query to get Order header from @MyXML:

SELECT Col.value('@OrderNumber', 'int') AS 'Order',
Col.value('@OrderDate', 'date') AS 'OrderDate',
Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',
Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',
Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',
Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',
Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',
Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',
Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',
Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',
Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',
Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',
Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',
Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',
Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'
FROM @MyXML.nodes('/Orders/Order') AS T(Col)

Then, you can use SQL query to get Order Items from @MyXML:

SELECT
Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',
Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber' ,
Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,
Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',
Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',
Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',
Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'
FROM @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)

To make it easier, here's the full code:

DECLARE @MyXML XML
SET @MyXML = '<?xml version="1.0"?>
<Orders>
<Order OrderNumber="99503" OrderDate="2013-10-20">
  <Address Type="Shipping">
    <Name>...</Name>
    <Street>123 Maple Street</Street>
    <City>Mill Valley</City>
    <State>CA</State>
    <Zip>10999</Zip>
    <Country>...</Country>
  </Address>
  <Address Type="Billing">
    <Name>Tai Yee</Name>
    <Street>8 Oak Avenue</Street>
    <City>Old Town</City>
    <State>PA</State>
    <Zip>95819</Zip>
    <Country>...</Country>
  </Address>
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
  <Items>
    <Item PartNumber="872-AA">
      <ProductName>Lawnmower</ProductName>
      <Quantity>1</Quantity>
      <USPrice>148.95</USPrice>
      <Comment>Confirm this is electric</Comment>
    </Item>
    <Item PartNumber="926-AA">
      <ProductName>Baby Monitor</ProductName>
      <Quantity>2</Quantity>
      <USPrice>39.98</USPrice>
      <ShipDate>2013-05-21</ShipDate>
    </Item>
  </Items>
</Order>
</Orders>'
   SELECT
    Col.value('@OrderNumber', 'int') AS 'Order',
    Col.value('@OrderDate', 'date') AS 'OrderDate',
    Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',
    Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',
    Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',
    Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',
    Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',
    Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',
    Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',
    Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',
    Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',
    Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',
    Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',
    Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country', 
    Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'
  FROM  @MyXML.nodes('/Orders/Order') AS T(Col)
  
   SELECT
    Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',
    Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber'  ,
    Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,
    Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',
    Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',
    Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',
    Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate' 
  FROM  @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)

Here is the Output:

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article, you will play around with some SQL Script to identify blocking behaviour. The most basic script that I have been using and used by many DBA will include Activity Monitor, sp_who2, sysprocesses etc.

Recently, I saw a script written by one person:

SELECT * FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL
GO

This will shows the rows like:

This is so cool because I know who is waiting for whom. In the above image 53 is waiting for 68. While 79 is waiting for 53. This was a cool way to look at things. Sure, you wanted to show the same data in slightly different way using T-SQL. So, I wrote a Blocking Tree TSQL script:

SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N'    ' + REPLICATE (N'|         ', LEN (LEVEL)/4 - 1) +
CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD -  '
ELSE '|------  ' END
+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO

The output would look like:

That's a simple way to look at the same Blocking data inside SSMS.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

SQL Server database files are organized in 8KB (8192 bytes) chunks, called pages. When we create the first row in a table, SQL Server allocates an 8KB page to store that row. Similarly every row in every table ends up being stored in a page.

Say one of the pages in your table is corrupt and while repairing the corrupt pages, you may eventually end up loosing some data. You may want to find out which records are on the page. To do so, use the following undocumented T-SQL %%physloc%% virtual column:

USE AdventureWorks2014
GO
SELECT *, %%physloc%% AS physloc
FROM Person.AddressType
ORDER BY physloc;

As you can see, the last column represents the record location. However the hexadecimal value is not in a human readable format. To read the physical record of each row in a human readable format, use the following query:

SELECT *
FROM Person.AddressType
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)

The sys.fun_PhysLocCracker function takes the %%physloc%% and represents a human readable format fileid, pageid i.e. 880 and record number on the page 880.

If you are interested in knowing what’s inside the sys.fn_PhysLocCracker function, use sp_helptext as follows:

EXEC sp_helptext 'sys.fn_PhysLocCracker'
which display the definition of sys.fn_PhysLocCracker
-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocCracker
--
-- Description:
--    Cracks the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
create function sys.fn_PhysLocCracker (@physical_locator binary (8))
returns @dumploc_table table
(
    [file_id]    int not null,
    [page_id]    int not null,
    [slot_id]    int not null
)
as
begin
    declare @page_id    binary (4)
    declare @file_id    binary (2)
    declare @slot_id    binary (2)
    -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot
    --
    select @page_id = convert (binary (4), reverse (substring (@physical_locator, 1, 4)))
    select @file_id = convert (binary (2), reverse (substring (@physical_locator, 5, 2)))
    select @slot_id = convert (binary (2), reverse (substring (@physical_locator, 7, 2)))
  
    insert into @dumploc_table values (@file_id, @page_id, @slot_id)
    return
end

The undocumented sys.fn_PhysLocCracker works on SQL Server 2008 and above.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.