RSS

Tag Archives: MS SQL Server

Connecting to SQL Server from NodeJs

With NodeJs it is more common to use MySQL, MongoDB, PostgreSQL than Microsoft SQL server as the database server, at least what I have encountered. However if we happens to prefer using Microsoft SQL with NodeJs there are few of options we could use.  Advantage of using NodeJs server for SQL server communication is it can be hosted in any platform with minimal issues.

Using an ORM ~ Using Sequelize?

Sequelize is a great ORM utility to use with NodeJs which also has the advantage of having abstraction layer for separate database drivers to interact with Microsoft SQL server, PostgreSQL, MySQL, MariaDB, SQLite and MSSQL. This is by far the best option we had so far. Other major benefit is the friendly API that it supports with promise based API which many NodeJs, AngularJS developers are following these days. Sequelize uses appropriate NodeJs package internally for each database server.

For Microsoft SQL server tedious is the node package to be used with Sequelize. Following two steps should be followed to getting started with connecting SQL server from NodeJs.

npm install --save sequelize //sequelize package

npm install --save tedious // SQL server TDS driver

Connecting SQL Server using Tedious without ORM?

Tedious is the npm package that is being internally used by Sequelize to connect to SQL server. This actually uses Tabular data stream protocol(TDS) for connecting to SQL server natively. Another compelling reason to use Tedios is that it is being actively contributed by Microsoft from recent past.

If you do not prefer using ORM for connecting to SQL server from NodeJs, you can also use Tedious without using Sequelize by using following steps. Tedious also supports SQL Azure by supporting encryption.

var Connection = require('tedious').Connection;

  var config = {
    userName: 'test',
    password: 'test',
    server: '192.168.1.210',

    // If you're on Windows Azure, you will need this:
    options: {encrypt: true}
  };

  var connection = new Connection(config);

  connection.on('connect', function(err) {
    // If no error, then good to go...
      executeStatement();
    }
  );

Connecting to SQL server from Sequelize

Connecting to SQL server from node js is simple you can abstract this in your factory class in a way that consumer code of backed database will be unaware about the database server completely.

var sequelize = new Sequelize('database name', 'username', 'password',
	{ host: 'localhost', dialect: 'mssql',
	  port: 1433,
	  pool: { max: 5, min: 0, idle: 10000 },
	  dialectOptions: { instanceName: 'instancename'}
	});

Any database driver specific code as above can be passed to dialect options.

Advertisements
 
Leave a comment

Posted by on December 14, 2016 in ASP.NET, MS SQL Server, NodeJs

 

Tags: , , , ,

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

 
1 Comment

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

 

Tags: , , ,

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: , ,

How to prevent table locks in sql server for long running DML commands

Every statement in SQL server by default considered as read committed transaction unless you have specified specific transaction isolation mode. So if you have to for example delete hundreds of thousands  records from a table it might put locks in your tables for long time. The way to go here is to do the long running query in separate units. Consider the following script.


CREATE TABLE #temp ( Id INT )

--this will insert dummy records for test
INSERT  #temp
( ID )
VALUES  ( 1 )
GO  10000

essentially above script will insert 10000 records in to #temp table.Lets say you need to delete all those rows .At first glance it seems very trivial to write just one

delete from #temp

table statement however this might be long running query depending your server resources much better approach in situations like these is to use followin method. (This can be applied to any DML statement) If you are using SQL server 2000 you could use following script.


-- if you are using SQL server 2000

WHILE ( EXISTS ( SELECT *
FROM   #temp ) )
BEGIN
SET ROWCOUNT 4

DELETE  FROM #temp

SET ROWCOUNT 0
END

If you are using SQL Server 2005+ you could use more sophisticated following method which uses “Top” TSQL keyword for DML statements which is not allowed in SQL server 2000.

-- if you are using SQL server 2005+
--Method 1
WHILE ( EXISTS ( SELECT *
FROM   #temp ) )
BEGIN

DELETE TOP ( 4 )
FROM    #temp

END

--Method 2
DECLARE @limit INT
SET @limit = 4

again:

DELETE TOP ( @limit )
FROM   #temp

IF @@ROWCOUNT != 0
GOTO again ;

So as you can see above methods partition the DML statements into chunks which will help other users to minimize locks.

 
Leave a comment

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

 

Tags: ,