RSS

Category Archives: SQL

Full Text Search Now Supported in SQL Azure PaaS Databases

It is really cool know that now that SQL Azure PaaS databases supports full text indexes which was earlier not. SQL azure PasS has serious limitations where some of the features that are supported in normal SQL server enterprise versions do not available.

One such feature was SQL server’s full text indexing. Alternative newly introduced features like “Azure Search” are arguably more preferred for newer implementations but however recently Microsoft has announced that full text search is available for SQL Azure PaaS databases. Detailed information on that is available in this link http://azure.microsoft.com/blog/2015/04/30/full-text-search-is-now-available-for-preview-in-azure-sql-database/.

Limitations on SQL azure PaaS databases are available on this link…

https://msdn.microsoft.com/en-us/library/azure/ee336281.aspx

Advertisements
 
1 Comment

Posted by on May 5, 2015 in Azure, MS SQL Server, SQL, SQL Azure

 

Tags: , , ,

Table spools operator used in SQL server query plans

Table spools in SQL server are temporary result sets used by SQL server query optimizer. You might see this operator in SQL server query execution plans. Let’s see this with an example. We can create a simple test table with following script.

IF (OBJECT_ID('test') IS NOT NULL)
BEGIN
DROP TABLE test
END
GO

--Create test table 
CREATE TABLE test    
( Id INT IDENTITY(1, 1) , 
[Name] CHAR(1000) DEFAULT 'test value')          
GO   
-- insert 1000 test records 

INSERT INTO dbo.test 
DEFAULT VALUES  
GO 100

CREATE CLUSTERED INDEX IX_test_Name ON dbo.test(Name ASC)
GO

If we do an update to the test table by including actual execution plan (graphical in this case) using following query we will get following sort of graphical execution plan

-- run the update query with include actual execution plan
UPDATE dbo.test set [Name] = NEWID()

table_spool
We can see that there is a table spool in the middle of the table update. Why SQL server creates table spool here is noteworthy to understand since it has 39% cost which seems like unnecessary additional cost but actually it is a sort of protection mechanism pushed by SQL server for common problem known as “Halloween Protection”. When an update query is being executed it has two operations. One is done by read cursor which does the reading of rows to be updated and the other half is write cursor which actually does the row changes. Let’s understand this with above example, since my test table has clustered index on “Name” column and clustered index enforces physical order of the table updating the clustered index column with new values might change physical order of the table so if the read cursor and write cursor in SQL server went simultaneously write cursor might do the update more than once while traversing the physical structure of the clustered index. So to prevent that SQL server creates a temporary result set in tempdb know as table spool with the result set of the read cursor(which is eager Spool since it will block parent operation until all rows are being read) . That result set will be updated so this is the “Halloween Protection” enforced by SQL server. This issue is identified by IBM researchers more than 30 years ago.
If you drop the clustered index and run the update query again you will NOT see the table spool since you are not updating physically ordered clustered index hence no threat of “Halloween Issue”. We can confirm this with Query plan as following.

--drop the clustered index
DROP INDEX IX_test_Name ON dbo.test
GO
-- run the update query again and see execution plan
UPDATE dbo.test set [Name] = NEWID()

with no clustered index no table spool

 
Leave a comment

Posted by on April 25, 2013 in MS SQL Server, SQL, TSQL

 

Using SQL Fiddle to debug test SQL scriptlets

Many web developers use online web parsers like JSFiddle(http://jsfiddle.net/) to test, debug, demonstrate etc JavaScript code. Recently I had to show some simple SQL code to a student who didn’t have any DBMS system installed in his machine. I had to search similar tool to quickly demonstrate the SQL code. There is similar SQL code runner called “SQL Fiddle” in http://sqlfiddle.com/. However it might be much easier to test your SQL code in your real DBMS than running in this web tool but there might be occasions where you find this tool helpful. 

 
Leave a comment

Posted by on April 16, 2013 in SQL, TSQL

 

Truncate table command in SQL server is a efficiently logged operation

There is common misconception between developers that truncate table command is a non logged operation or minimally logged operation. Actually it is not. It is a fully logged but also efficiently logged operation. Those who are not familiar with these terms here is a quick explanation.
In SQL server, minimally logged operation is a database operation that only generates log records for data page level allocations but not for all row level changes for the selected pages. i.e. Rebuilding a index would generate log records for page level allocations in the transactions log but not row level changes. whereas in fully logged operation it will record all row level changes in addition to page level changes(happens with delete command) in after images and before images in transaction log records. When you run Truncate table command it will deallocate all the pages associated with the selected table. Pages and the extents are deallocated which are associated with the table. But still operation is logged in transaction log only the data pages deallocation happen instantly,so it might be faster at some situations. Truncate table command is a efficiently logged operation. However if it is a large table there might be a need to get large amount of locks for the pages and hence it might be slower if you are using SQL server 2000 SP2 or earlier. This has been fixed with SQL Server 2000 SP3 and upper with deferred drop mechanism where truncate table command runs as separate steps. this solves the issue of running out of extent locks. You can experiment with the deallocations as following scripts.

--Create test table 
CREATE TABLE test
    (
      Id INT IDENTITY(1, 1) ,
      [Name] CHAR(1000) DEFAULT 'test value'
    )
    
    
GO 


-- insert 1000 test records

INSERT into dbo.test

DEFAULT VALUES 

GO 1000


-- Clear the Transaction log so wee can see later easily
CHECKPOINT
GO

-- Run truncate table 

TRUNCATE TABLE dbo.test
GO

-- See the transaction log records

SELECT * FROM  fn_dblog(NULL,NULL)

transactionlog

 
Leave a comment

Posted by on December 17, 2012 in ASP.NET, MS SQL Server, SQL, TSQL

 

Tags: ,

Are sql server table variables not being created in tempdb where as temp tables do?

Some TSQL developers and DBAs think that in SQL server table variables are created in SQL server memory where as only temporary tables created in tempdb. It is actually both these kind of temp tables are being created in tempdb. Actually you could do experiments to find out this.



SELECT  *
FROM    sys.dm_db_session_space_usage
WHERE   session_id = ( SELECT   @@SPID
                     )
GO
-- get the current date time before table creation
DECLARE @DTBeforeCreation DATETIME

SET @DTBeforeCreation = GETDATE()

--create tamp Table with 1000000 recoreds
CREATE TABLE #TestTempTable ( Id INT )

INSERT  #TestTempTable
        SELECT TOP 1000000
                ROW_NUMBER() OVER ( ORDER BY a.name ) AS Alias
        FROM    sys.objects a
                CROSS JOIN sys.objects b 
                
--see that temp table created in temp db                
SELECT  *
FROM    tempdb.sys.objects
WHERE   type = N'U'
        AND create_date >= @DTBeforeCreation               


GO
--see how much pages being in the database
SELECT  *
FROM    sys.dm_db_session_space_usage
WHERE   session_id = ( SELECT   @@SPID
                     ) 
GO 
-- get the current date time before table creation
DECLARE @DTBeforeCreation DATETIME

SET @DTBeforeCreation = GETDATE()

SELECT  @DTBeforeCreation
                     
                     
--create table variable with same amount of records                     
DECLARE @TestTableVar TABLE ( Id INT )

INSERT  @TestTableVar
        SELECT TOP 1000000
                ROW_NUMBER() OVER ( ORDER BY a.name ) AS Alias
        FROM    sys.objects a
                CROSS JOIN sys.objects b 
--see that table variable created in temp db database
SELECT  *
FROM    tempdb.sys.objects
WHERE   type = N'U'
        AND create_date >= @DTBeforeCreation  
GO                 
/*
see how much pages being in the database due to table variable creation
it proves that same amount of pages are being incremented used as in temp table creation
*/
SELECT  *
FROM    sys.dm_db_session_space_usage
WHERE   session_id = ( SELECT   @@SPID
                     )    
   
                                  
                
/*Cleanup*/
DROP TABLE #TestTempTable

Here I am checking page allocation before and after temp table and table variable creation. It proves that in both occasions same amount of pages being increased since we inserted same amount of records to the two tables. I am also recording current date time before temp table and table variable creation and checking sys.objects in tempdb database to find tables that have been created after the time record. In both occasions make sure you are the only one connected to the target test database.
We can see that even after the table variable creation it is being recorded in tempdb database.

 
Leave a comment

Posted by on September 23, 2012 in MS SQL Server, SQL, TSQL

 

Tags: ,

Using recursive query to generate incremental integer field in SQL server

There are number of ways to generate incremental integer id field in SQL server using TSQL for temp tables, DDL operations etc. Recently for research purpose I needed to generate incremental integer column for a table. I used a recursive query to do it for research purpose although there are many other trivial ways to do it too.(eg.ROW_NUMBER() function, IDENTITY column temp table method etc.)

;WITH    cte
AS ( SELECT   1 AS n
UNION ALL
SELECT   n + 1
FROM     cte /*this is the recursive call which starts the recursion*/
WHERE    n < 21 /*Terminating condition*/
)
SELECT  *
FROM    cte

Recursion in SQL server is frequently achieved by using common table expressions(there are other approaches too SQL CLR, Recursife UDF calls etc). How ever maximum recursion level in SQL server is by default 100 so if you try to get more than that you will be popped up with error saying

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

In order to overcome that use following kind of query hint which also specify an aditional option(which is actually the query hint in this case)

;WITH    cte
AS ( SELECT   1 AS n
UNION ALL
SELECT   n + 1
FROM     cte /*this is the recursive call which starts the recursion*/
WHERE    n < 12222 /*Terminating condition*/
)
SELECT  *
FROM    cte OPTION( MAXRECURSION 32767)

Actually if you specify 0 as MAXRECURSION you can virtually expand the limit to much higher level as in following query.

;WITH    cte
AS ( SELECT   1 AS n
UNION ALL
SELECT   n + 1
FROM     cte /*this is the recursive call which starts the recursion*/
WHERE    n < 12222 /*Terminating condition*/
)
SELECT  *
FROM    cte OPTION( MAXRECURSION 0) -- specifying maxrecursion to 0

 
Leave a comment

Posted by on June 24, 2012 in MS SQL Server, SQL, TSQL

 

Tags: , ,

Remove Time portion from SQL server Date time object

If you have done TSQL programming you might need declare a variable which will store a specific date. Lets say you want to store today date in a variable(which is apparently your server machine date assuming it’s correctly configured). This can be done with several ways here are some of the ways I used to do.
Method 1 – In this method what you does is you find number of Days between 1900-01-01 as reference date and which essentially truncates the time portion 🙂 and then you add that number of days to 1900-01-01 which truncates the date.

DECLARE @Today DateTime
DECLARE @mydate datetime = GETDATE();

SELECT DATEADD(day, DATEDIFF(day, '19000101', @mydate), '19000101'); -- this will select today's date without time portion

Method 2 – in this method we convert datetime value to float which will give you a floating point representation of a datetime where in decimal portion representing the time portion so when you use floor function that floating point number rounds to the ground level integer which do the trick of removing the time portion and then you can convert that integer representation or day portion of the original datetime variable to datetime data type. Kind of clever method right? 🙂 Actually I prefer this method.


DECLARE @mydate DATETIME = GETDATE();

SELECT CAST( FLOOR(CAST(@mydate AS FLOAT)) AS DATETIME)

Method 3 – if you are using SQL server 2008 just use Date variable it will automatically removes time portion when you are storing date time values. 🙂

-- Only works on SQL Server 2008 and above
DECLARE @mydate date = GETDATE();

SELECT @mydate

There are some other methods which use string manipulation to do the same task which I don’t like to use much often. I will research benchmark on these various methods and will blog about results to find the fastest most efficient method.

 
Leave a comment

Posted by on May 27, 2012 in MS SQL Server, SQL