SQL Server: Search Similar String in a Table

There are may ways to look for similar strings in a SQL Server column. The most common  method is to make use of LIKE operator. Let us see the different ways to look for similar string in a table.

Consider the following data:

declare @test table(data varchar(100))
insert into @test
select 'this is for testing' union all
select 'test entry' union all
select 'no way for this' union all
select 'nothing to be tested' union all
select 'welcome'

Suppose you want to find out data with the word ‘test’

Method 1 : Use LIKE operator

select data from @test
where data like '%test%'

Method 2 : Use CHARINDEX function

select data from @test
where charindex('test',data)>0

Method 3 : Use PATINDEX function

select data from @test
where patindex('%test%',data)>0

Method 4 : Use Regular expression

select data from @test
where data like '%[t][e][s][t]%'

Both charindex and patindex look for the match and return its position in the string. All the above four methods would return the same result as shown below:

Feedback:

SQL Server: String operations in XML document‏

SQL Server versions starting from 2005 and onwards supports storing and retrieving data as XML format. Using the XML datatype, you can do lot of things such as splitting string, string concatenation etc. In this post, we will see how we can use XML datatype to split strings

Consider the following example:



Declare @string Varchar(100),@delimiter CHAR(1)
Set @string = 'See if you can you split this'
set @delimiter = ' '
Declare @Xml Xml
Select @Xml = Cast('<d>' +
                    Replace(@string, @Delimiter,'</d><d>') + '</d>' As Xml )
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @XML.nodes('/d') T (split)

In the above example the variable @Xml is of type xml. It concatenates the
original string by replacing a space with </d><d> so that each word will be surrounded
by <d> and </d>. The string is split by identifying the elements within <d> and </d>.

OUTPUT

Feedback:

SQL Server Management Objects 2008 (SMO) New Features

MSDN defines SMO as - SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. In this article we will practically explore some features of SQL Server Management Objects.

To start with, let’s create a ‘Windows Application’ using Visual Studio and name the application as ‘SMOExamples’ as shown below –

Now let’s add the following references to our project.

1. Microsoft.SqlServer.ConnectionInfo.dll
2. Microsoft.SqlServer.Smo.dll
3. Microsoft.SqlServer.SmoExtended.dll
4. Microsoft.SqlServer.SqlEnum.dll
5. Microsoft.SqlServer.Management.Sdk.Sfc.dll

All these references can be found in the path – ‘C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies’.

Now let’s design our windows form as shown below –

Declare an object which will make a connection to our SQL Server instance. For this, we will use a class called ‘Server’ as shown below –

Server srv = new Server(Environment.MachineName);

Now to test the connection to the SQL Server, let’s write the following code on the Form Load event as shown below –

Now let’s create a Database and Table with the name ‘SMOExample’ and ‘SMOTable’ respectively by writing the following code on the click event of ‘Create Database And Table’ button –

For creating a database, we have used a class called ‘Database’ which takes two parameters in the constructor. First one is the SQL Server instance in which we have to create a database. Second is the database name.

Now let’s create a script for all the tables available in our database. Write bee following code on the click event of ‘Generate Script’ button –

For generating the script files we are using a ‘Scripter’ class.

Now let’s write the code to take a backup of the complete database. Write the following code on the click event of ‘Backup Database’ button –

To take a backup of the database, we are using a ‘Backup’ class and to decide what will be the device of backup, we are using ‘BackupDeviceItem’ class.

Now the last thing we will explore is how to verify the backup, which is taken before we restore it to another server/same server. Write the following code on the ‘Verify Backup’ button –

Now run your project. This will show you the date and time created for ‘AdventureWorks’ database as shown below –

Now let’s click the button ‘Create Database and Table’ and observe the results in SQL Server Management
 Studio Object Explorer –



Now click on the button ‘Generate Script’. Go to ‘C:\’ and verify the script file –

Finally click the button ‘Backup Database’ and check your ‘C:\’ drive. Your backup file should have been created. Similarly click on ‘Verify Backup’ button and it will show you a success message.

Summary – In this article we saw few capabilities of SQL Server Management Objects (SMO).

Download the source code

Feedback:

XML Basics in SQL Server 2005 and 2008

In this article, we will see how to write basic queries to insert, query and generate XML data in SQL Server 2005/2008. For this demonstration, I am using the ‘Northwind’ database.

The first step is to start ‘SQL Server Management Studio’ and start a new query window. Then use the following clauses

1. FOR XML AUTO – This clause returns a simple, nested XML tree result.

SELECT * FROM Customers FOR XML AUTO

2. FOR XML RAW – This clause returns a simple, nested XML tree result by transforming each row in an <ROW/> Element.

SELECT * FROM Customers FOR XML RAW

3. FOR XML AUTO, ELEMENTS – This clause returns a XML result by specifying columns as sub elements.

SELECT * FROM Customers FOR XML AUTO,ELEMENTS

4. FOR XML AUTO, ELEMENTS, TYPE – This clause returns a XML result by specifying columns as sub elements and ‘TYPE’ specifies that it returns result as XML type which we can store in XML data type in SQL Server.

SELECT * FROM Customers FOR XML AUTO, ELEMENTS, TYPE

In SQL Server 2005, Microsoft has introduced a new data type ‘XML’. We can use this data type to store well formed as well as valid xml in our tables. Let’s see a few examples of the same –

Let’s first create a table which will use XML data type as shown in the following script –

Now let’s insert few rows in our table. For inserting the data in the above table, we will define a variable with ‘XML Data Type’ as shown below –



Now let’s write a select statement which will fetch the inserted data –

SELECT * FROM CustomerProducts

Now if you check the result, it should look like the following –

Now let’s write some queries which will test the XML data type. Let’s insert a record into our ‘CustomerProducts’ table which is not well formed, as shown below –

If you check the above XML, it does not having closing tag for <ProductID>. So the result will be as shown below –



Now let’s see how to validate the XML data using XML Schemas. Let’s drop the existing table we created above.

DROP TABLE CustomerProducts

Now create a schema for XML data validation as shown below –

CREATE XML SCHEMA COLLECTION ProductSchema AS'
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.microsoft.com/schemas/northwind/products"
xmlns:prod="http://www.microsoft.com/schemas/northwind/products">
<xs:element name="Product">
<xs:complexType>
<xs:sequence>
<xs:element ref="prod:ProductID" />
<xs:element ref="prod:ProductName" />
<xs:element ref="prod:SupplierID" />
<xs:element ref="prod:CategoryID" />
<xs:element ref="prod:QuantityPerUnit" />
<xs:element ref="prod:UnitPrice" />
<xs:element ref="prod:UnitsInStock" />
<xs:element ref="prod:UnitsOnOrder" />
<xs:element ref="prod:ReorderLevel" />
<xs:element ref="prod:Discontinued" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ProductID" type="xs:integer" />
<xs:element name="ProductName" type="xs:string" />
<xs:element name="SupplierID" type="xs:integer" />
<xs:element name="CategoryID" type="xs:integer" />
<xs:element name="QuantityPerUnit" type="xs:string" />
<xs:element name="UnitPrice" type="xs:double" />
<xs:element name="UnitsInStock" type="xs:integer" />
<xs:element name="UnitsOnOrder" type="xs:integer" />
<xs:element name="ReorderLevel" type="xs:integer" />
<xs:element name="Discontinued" type="xs:boolean" />
</xs:schema>'

To see all the available XML schemas execute below query –

SELECT * FROM sys.xml_schema_collections

The result is as shown below –



Now let’s create the CustomerProduct table once again with the XML data type which will take the address of the above schema, as shown below –



Now try to insert a record and you will get an exception –



The exception is thrown because we are not passing the ProductName. Now let’s insert the following record which matches our schema validations –

You can now query the data and you should see valid XML.

Summary – In this article, we have seen how to use XML for querying and inserting the data in the table. We saw some basic queries of XML while working with SQL Server.

Feedback:

SQL Server: Transfer Objects From One Schema to Another

This post describes how to transfer objects from one schema to another. A simple method is to make use of the ALTER Schema statement as shown below.

Consider that there are two schemas named test1 and test2 in the database and you want
to move tables from schema test1 to test2.

Let’s create a table in schema test1.



In the above query, we have also run a query to know which schema the table test belongs to.

create table test1.test(id int, names varchar(100))
select * from information_schema.tables where TABLE_NAME='test'



As you can see, the schema of the table is test1

Now run the following query to move table test from schema test1 to test2



Again run the following statement to know which schema the table test now belongs to

SELECT * from information_schema.tables where TABLE_NAME='test'



As you see from the result, the schema of table has now changed from test1 to test2

Feedback:

SQL Server: Designing Chart Report in SSRS 2008

In this article, we will see how to design a Chart report in SQL Server Reporting Services 2008. For this demonstration we will use AdventureWorks sample database provided by Microsoft.

You can also check my previous article on SQL Server: Designing Tablix Report in SSRS 2008

To design this report, let’s open Visual studio 2008 Business Intelligence Studio and create a new Report Server project as shown below –

Once your project is ready, let’s create a data source which will fetch the data from AdventureWorks database. To fetch the data let’s write the following query –

SELECT YEAR(SOH.OrderDate) OrderYear,PC.NAME AS CategoryName,PSC.NAME as SubCategoryName,SUM(UnitPrice) TotalPrice, SUM(OrderQty) TotalOrders
FROM Production.ProductCategory PC INNER JOIN Production.ProductSubcategory PSC ON PC.ProductCategoryID=PSC.ProductCategoryID
INNER JOIN Production.Product P ON PSC.ProductSubcategoryID=P.ProductSubcategoryID INNER JOIN Sales.SalesOrderDetail SOD ON P.ProductID=SOD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH ON SOH.SalesOrderID=SOD.SalesOrderID
GROUP BY YEAR(SOH.OrderDate), PC.Name,PSC.Name
ORDER BY OrderYear,CategoryName,SubCategoryName

If you execute this query, you should get a similar the result as shown below –



To add a new data source to our report, go to Solution Explorer. Right click ‘Shared Data Sources’ folder and add a ‘New Data Source’. This will show you a shared data source property window. Name the data source as ‘AdventureWorksDS’ and configure the connection to the AdventureWorks database by clicking the ‘Edit’ button as shown below –



Now let’s add a blank report using our solution explorer. To add a blank report, right click the Report and click on ‘Add New Item’ and choose ‘Report’. Name the report as ‘AdventureWorksYearlySalesChartReport’ as shown below –



Now go to ‘View’ menu and click on ‘Report Data’. In the ‘Report Data’ window, add a new dataset as shown below –



This will show you the ‘Dataset Properties’ window. Name the dataset as ‘YearlySalesDS’ and choose the existing data source by clicking a ‘New’ button from ‘Data Source’ option. This displays a ‘Data Source Properties’ window. Choose ‘Use Shared Data Source Reference’ radio button and choose our data source ‘AdventureWorksDS’ which we have created in the above steps. Then paste the query shown above in a ‘Query’ box as shown below –



Now we are ready to design the report. Drag and drop a ‘Chart’ control from tool box on our report. As soon as you drop the chart control on the report, it will ask you to ‘Select Chart Type’. Make a choice of Column chart and click ‘OK’ as shown below –



Now click on the Chart and you will see the view of the chart with different axis, as shown below –



Now from the ‘Report Data’ Window drag and drop ‘TotalPrice’ on ‘Drop data fields here’ and drag and drop OrderYear on ‘Drop category fields here’. Now your report will look like this –



Now click on the preview report tab and see your report. It will look similar to the following –



Now let’s modify the report. First of all change the title of the report to ‘Adventure Works Yearly Sales’. Change the ‘Y’ axis title to ‘Total Sales’ and change ‘X’ axis to ‘Order Year’. Now preview your report and it will look like the one shown below –



Now right click the ‘Total Price’ and click on ‘Add Calculated Series’ as shown below –



You will see the ‘Calculated Series Properties’ window. From here, choose ‘General’ section. Under formula, choose ‘Exponential Moving Average’ as shown below –



Now preview your report and it will look like the one shown below –



Now right click the Chart column and choose ‘3D Effects’. This will show you ‘Chart Area Properties’ window. Check the check box ‘Enable 3D’ and click ‘OK’ button by keeping the other properties default. Preview your chart and this is what you see –



Summary – In this article, we have seen how to design a Chart report using SQL Server Reporting Services 2008.

Feedback:

SQL Server: Designing Tablix Report in SSRS 2008

In this article we will see how to design a Tablix (Table + Matrix) report in SQL Server Reporting Services (SSRS) 2008. For this demonstration, we will use the AdventureWorks sample database provided by Microsoft.

In SQL Server Reporting Services 2008, Microsoft has introduced a new reporting model called Tablix. Tablix is a combination of ‘Table + Matrix’ report. To design this report, open Visual studio 2008 Business Intelligence Studio and create a new Report Server project as shown below –

Once your project is ready, let’s create a data source which will fetch the data from the AdventureWorks database. To fetch the data, write the following query –

SELECT YEAR(SOH.OrderDate) OrderYear,PC.NAME AS CategoryName,PSC.NAME as SubCategoryName,SUM(UnitPrice) TotalPrice, SUM(OrderQty) TotalOrders
FROM Production.ProductCategory PC INNER JOIN Production.ProductSubcategory PSC ON PC.ProductCategoryID=PSC.ProductCategoryID
INNER JOIN Production.Product P ON PSC.ProductSubcategoryID=P.ProductSubcategoryID INNER JOIN Sales.SalesOrderDetail SOD ON P.ProductID=SOD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH ON SOH.SalesOrderID=SOD.SalesOrderID
GROUP BY YEAR(SOH.OrderDate), PC.Name,PSC.Name
ORDER BY OrderYear,CategoryName,SubCategoryName

If you execute this query, you will get the following result –



To add a new data source to our report, go to Solution Explorer. Right click ‘Shared Data Sources’ folder and add a ‘New Data Source’. It will show you shared data source property window. Name the data source as ‘AdventureWorksDS’ and configure the connection to the AdventureWorks database by clicking the ‘Edit’ button as shown below –



Now let’s add a blank report using the solution explorer. To add a blank report, right click the ‘Report and click on add ‘New Item’ and make a choice of ‘Report’. Name the report as ‘AdventureWorksYearlySalesReport’ as shown below –



Now go to ‘View’ menu and click on ‘Report Data’. In the Report Data window, add a new dataset as shown below –



It will show you a Dataset Properties window. Name the dataset as ‘YearlySalesDS’ and choose the existing data source by clicking a ‘New’ button from ‘Data Source’ option. It will show you a Data Source Properties window. Make a choice of ‘Use Shared Data Source Reference’ radio button and choose our data source ‘AdventureWorksDS’ which we have created in the above steps. Then paste the query shown above in a ‘Query’ box -



Now we are ready to design the report. Drag and drop ‘Matrix’ control from tool box on our report as shown below –



Now if you check the properties window, you will see the name ‘Tablix1’. Align the report to the left side of the report designer.

Now from the ‘Report Data’ Window drag and drop ‘OrderYear’ on columns group section and drag and drop ‘CategoryName’ on Rows group section. Then drag and drop ‘TotalPrice’ on Data section. Now your report will look like this –



Now click on the preview report tab and see your report. It will look like below –

Now let’s modify the report little bit. Let’s first format the report. Make all columns bold. Now let’s add a ‘Total’ column before Columns group and add ‘Total’ column after Rows group.

To add a total column, right click to ‘[OrderYear]’ column header and click on ‘Add Total’ ‘Before’. Repeat the same step by right clicking the Category name column and ‘Add total’ ‘After’. Now preview your report and it should look like below –

Now let’s add a ‘SubCategory’ section in the Rows group. Drag and drop ‘SubCategoryName’ field below the ‘CategoryName’ field as shown below –



Now preview your report. It will look like this –



If you observe this report, it is similar to reporting services 2005. Let’s modify this report to display it in ‘Tablix’ format. Go to Subcategory column and ‘Add Total’ as shown below –

Now click on the total column and choose a field ‘Category Name’ and delete ‘Category Name’ column as shown below –

Preview your report and it will look similar to the following –



Summary – In this article we have seen how to design a basic ‘Tablix’ report using a ‘Tablix’ control in SQL Server Reporting Services (2008).

Download the source code

Feedback: