Showing posts with label Sql. Show all posts
Showing posts with label Sql. Show all posts

June 24, 2015

I have this Sql table setup in Sql Server that contains a XML field and I need to count the number of times a xml node appears in the xml. In practice, it could be the table contains user data and the xml contains a list of cities the user has visited and we need to find out how many cities the user has visited.

July 25, 2014

Select Top 1 or Top n basically returns the first n rows of data based on the sql query. Select Top 1 1 or Top n s will return the first n rows with data s depending on the sql query.

April 29, 2014

Few days back I ran into a issue where my website failed with the message that

  1. {System.Data.SqlClient.SqlException (0x80131904): Could not allocate space for object 'dbo.Table1'.'PK_TableID' in database 'TestDB' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
  2. Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.


Initially I thought the database was out space but then it sounded strange as I was not expecting the database to run out of space. After checking the database space I noticed that it only had a few MBs of data remaining for the primary while the log file had a huge data capacity. To fix the issue, update the max size of the database so it can save the data. Also, make sure the server has sufficient space to cater for the increased space.

However, sooner or later you are going to run out of space and therefore it's a good idea to increase the max size to unlimited but you need to make sure the server can cater for it.

April 14, 2014

Sql - Count by XML Attribute

I have a Sql table with a XML column and I want to do a count of rows of data grouped by an attribute value of the XML column. We might need these kind of queries when the XML attribute refer to a type of data. For example, I might have country code as AU and US that refers to two different countries where the data came from. Ok, so, how can I do that?

First, we have a sql table with an XML column to hold the data. I will use the same table as in my previous post on how to query csv from XML column. Here is a copy of the example data that I have.


To query this table, I will perform the usual sql select operation on the column and get the attribute values and then perform sql count operation from an outer sql query. Here's the script on how I did it.

Sql Script
  1. SELECT src.typeid as [TypeId],
  2.     count(src.typeid) as [Count]
  3. FROM
  4.   (
  5.     SELECT [Tag].value('(/a/@data)[1]', 'nvarchar(50)') as name
  6.         ,[Tag].value('(/a/@type)[1]', 'int') as typeid
  7.     FROM [Employee].[dbo].[Table_2]
  8.   ) src
  10.   group by src.typeid

April 11, 2014

Query Comma Separated Values in Sql

Here is a situation where I will need to select and compare values from a table in sql server which stores data in a comma separated list. Well selecting data is not a problem but we want to run a query and compare the values within the list. In my scenario, the list contains numbers only but the solution will work for both numbers and strings. One solution will be to compare using LIKE and then check if the number exist but this won't necessarily be correct as ,2, and ,22, and 12, might all return the same result and the list does not contain commas at the start or at the end.

April 7, 2014

I have this scenario where I need to insert xml data to a xml column in Sql Server that already has an empty string in it. The xml column I have allows null and I need to update both null and and empty xml to a default value. When I started writing the script finding the null value was really easy - I could write something like this.

  1. SELECT xmldata
  2. from Table_1
  3. where XmlData IS NULL

But finding the xml column with empty string was difficult. First of all, I could not write the following

  1. SELECT xmldata
  2. from Table_1
  3. where XmlData = ''


as it returned error

Msg 402, Level 16, State 1, Line 2
The data types xml and varchar are incompatible in the equal to operator.

So, to solve the issue, I used the inbuilt function DataLength() on the xml column and compared if the value is 5 or not. Data length for empty string is 5. Based on this comparison, I updated the xml column.

  1. SELECT xmldata
  2. from Table_1
  3. where DATALENGTH(XmlData) = 5

March 18, 2014

Size of Individual Tables in Sql Server

I recently created a sql server database which was huge and so I wanted to find out individual sizes of the tables. So, with the help of few suggestions on the internet I ended up writing a script that displays the size of individual tables in KB, MB and the number of rows those tables have.

Here’s the script that I wrote.

  2.     t.NAME AS TableName,
  3.     s.Name AS SchemaName,
  4.     p.rows AS RowCounts,
  5.     SUM(a.total_pages) * 8 AS TotalSpaceKB,
  6.     SUM(a.used_pages) * 8 AS UsedSpaceKB,
  7.     SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
  8.     SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB
  9. FROM
  10.     sys.tables t
  11.       INNER JOIN
  12.     sys.indexes i ON t.OBJECT_ID = i.object_id
  14.     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  16.     sys.allocation_units a ON p.partition_id = a.container_id
  18.     sys.schemas s ON t.schema_id = s.schema_id
  19. WHERE
  20.     t.NAME NOT LIKE 'dt%'
  21.     AND t.is_ms_shipped = 0
  22.     AND i.OBJECT_ID > 255
  23. GROUP BY
  24.     t.Name, s.Name, p.Rows
  25. ORDER BY
  26.     t.Name


And here is a screen shot of the results produced when run on Adventure Works database.


March 3, 2014

Drop Database–Currently in use

I am trying to drop a database but is getting the following error – “Cannot drop database as it is currently in use”. This really means that there are open connections to the database that needs to be closed first. To do so, we can use the SP_WHO and KILL commands.

SP_WHO will provide a list of connections to the database servers. Check for the spid and the dbname and note the corresponding spid for the database you are trying to drop.

Once we got the spids which will be numbers, run the command KILL spid. For example, if you receive 51 and 52 as the spids. Then run

KILL 51; KILL 52;

Then, run the command to drop the database and it should work.

January 10, 2014

Query xml from sql

sSql Server has the option to store XML data. This is great as it means we can store xml data and also run sql scripts on it. Yes, we can run sql scripts to retrieve certain fields within the xml.  Ti start with, let's use the Customer table that I created in previous post and add a new field Person that is of type xml.

December 18, 2013

add primary key using sql

In the previous example, I showed how to add primary key from the designer view of the table in Sql Server Management Studio. In this example, I will show how to add primary key using sql. Since the column is already added to the table, we will use Alter Table and add the primary key constant to the table. Here's the sql to add the primary key.
  1. ALTER TABLE Customer
  2. ADD CONSTRAINT pk_Email PRIMARY KEY (EmailAddress)
Reference: Shahed Kazi at