Comparing File Names in a directory with the list in Excel File

Here is the Script...

---- Get File Names rom you directory

Declare @vOSFiles Table (MyFileName nvarchar(1000),MyDepth int, IsFile bit)

Insert into @vOSFiles

       EXEC xp_dirtree 'G:\Atif\', 1, 1

--Select * from @vOSFiles where IsFile = 1

---- Get List of Files from Excel

----  Insert Excel File list to another Temp Table.

---- The structure o fthis table depends upon you excel file and requirement

Declare @vExcelFileList Table (MyFileName nvarchar(1000))

---- This is for xlsx files

 Insert into @vExcelFileList

       SELECT [MyFileName] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

       'Excel 12.0 Xml;Database=G:\Budjet.xlsx;HDR=Yes;IMEX=1',

       'SELECT * FROM [Sheet2$]')

--Select *

--from @vOSFiles

---- Join the two tables on the File Names

Select a.*, case when b.MyFileName is not Null then 'Exists' Else 'Not Exists' end as FileExistance

from @vOSFiles a

Left Outer Join @vExcelFileList b on b.MyFileName = a.MyFileName

Where a.IsFile = 1

You might want to enable ad hoc query options;

---- Enable AdHoc Queries

--USE MSDB

--GO

--sp_configure 'show advanced options', 1;

--GO

--RECONFIGURE;

--GO

--sp_configure 'Ad Hoc Distributed Queries', 1;

--GO

--RECONFIGURE;

--GO

---- Add OLEDB referance for xlsx

--EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'

--    , N'AllowInProcess', 1

--GO

--EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'

--    , N'DynamicParameters', 1

--GO

For XLS files (earlier 2007 versions of Excel), you can run the OpenRowset query as;

SELECT * FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0'

,'Excel 4.0;Database=G:\Budjet.xls','SELECT * FROM [sheet2$]')

5% OFF All Lenovo ThinkPad's. Coupon Code:CJDEC5UK Lenovo UK Homepage

Error Handling in SQL Server 2012

As we all know that from SQL Server 2005 and onward, we had TRY CATCH to handle the exceptions / errors in t-sql code along with @@ERROR methodology.

@@ERROR

Returns an error number if the previous statement encountered an error. If the error was one of the errors in the sys.messages catalog view, then @@ERROR contains the value from the sys.messages.message_id column for that error. You can view the text associated with an @@ERROR error number in sys.messages.

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later.

Simple Example;

CREATE TABLE [dbo].[tblTest](

       [NAME] [varchar](100) NOT NULL,

 CONSTRAINT [PK_tblTest_1] PRIMARY KEY CLUSTERED

(

       [NAME] ASC

)

)

GO

INSERT INTO tblTest 

       Select 'Atif'

       Union

       Select 'Sheikh'

Update tblTest

Set Name = 'Atif'

Where Name = 'Sheikh'

if @@ERROR <> 0

       print 'Error Generated'

else

       Print 'No Error.'

TRY CATCH

We can also make use of TRY CATCH block in order to catch the error;

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

       print 'Error Generated.'

END CATCH

We can make use of transactions and roll back transactipon in the CATCH block. The TRY...CATCH construct also supports additional system functions (ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE) that return more error information than @@ERROR. TRY...CATCH also supports an ERROR_NUMBER function that is not limited to returning the error number in the statement immediately after the statement that generated an error.

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

       print 'Error Generated.'

      

       SELECT

    ERROR_NUMBER() AS ErrorNumber

    ,ERROR_SEVERITY() AS ErrorSeverity

    ,ERROR_STATE() AS ErrorState

    ,ERROR_PROCEDURE() AS ErrorProcedure

    ,ERROR_LINE() AS ErrorLine

    ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

THROW

Now, in SQL Server 2012, we can also use THROW to throw the exception to the application. You can say it works like RAISERROR;

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

    print 'Error Generated.';     

       THROW;

END CATCH

You can use THROW statement like RAISERROR as well;

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

    print 'Error Generated.';     

       THROW 51000, 'Primary Key Violates.', 1;

END CATCH

Limitatoin of Sequence Objects in SQL Server 2012

Sequence Object introduced in SQL Server 2012 is a good features added by MS SQL Server team. I have already discussed this in my post "Sequence Object in SQL Server 2012" but there are many limitations. I will try to explain the prominent limitations.

Let us create a sequence object in our test database.

CREATE SEQUENCE LimitSequence AS INT
 START WITH 1
 INCREMENT BY 1
GO

Also, create a test table with ID generated by using the sequence object;

CREATE TABLE dbo.TestLimit(ID INT,Name VARCHAR(100))
GO
INSERT INTO dbo.TestLimit VALUES
 (NEXT VALUE FOR LimitSequence,'Atif'),
 (NEXT VALUE FOR LimitSequence,'Sheikh'),
 (NEXT VALUE FOR LimitSequence,'Asif')
GO

Cannot Use DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT 

Now, if you try to run the query using sequence object with any of these clauses, you will get an error message;

Select Distinct NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

On executing the above query, you get an eror message as ;

Msg 11721, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

This applies to all operators.

Using simple ORDER BY clause

If you try the query using the sequence object with simple ORDER by clause, it will generate an error;

Select NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

Order by ID

Error message is;

Msg 11723, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.

Good part for this is to user OVER (ORDER BY ). 

Select NEXT VALUE FOR LimitSequence,*, ROW_NUMBER() Over (Order by ID) as RNO from dbo.TestLimit

The above query will execute without any error.

TOP 

Cannot use with TOP.

Select Top(10) NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

This query will generate error message as;

Msg 11739, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET. 

As stated in the error message, if ROWCOUNT is set or TOP and OFFSET is used, the query will generate an error message.

CASE, CHOOSE, COALESCE, IIF, ISNULL, or NULLIF

All these are not allowed. I will give the example if ISNULL function;

Select Isnull( NEXT VALUE FOR LimitSequence,0),* from dbo.TestLimit

Erro rmessage is;

Msg 11741, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

You can try the rest of the functions yourself.

WHERE Clause

You cannot use it in WHERE clasuse;

Select * from dbo.TestLimit

where NEXT VALUE FOR LimitSequence = 6

Error message will be;

Msg 11720, Level 15, State 1, Line 2

NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, or ORDER BY clauses.

So, these are few prominent limitation for the sequence objects. 

Contained Database

What is it?

A contained database is a database within a sql server instance that is not dependent upon the instance itself in terms of users and metadata. All users and metadata is stored within the contained database.

What is the purpose?

These database are easy to port from instance / server to instance / server. You don't have to worry about the user logins associated with the database. This is very handy when you are dealing with large number of database users.

Any Important Note / drawback?

Important note is that in SQLServer 2012, only Partial containment type is supported. It means that your database can be a None contained database or partially contained database. A partially contained database is a contained database that can allow some features that cross the database boundary. SQL Server includes the ability to determine when the containment boundary is crossed. Fully contained user entities (those that never cross the database boundary), for example sys.indexes. Any code that uses these features or any object that references only these entities is also fully contained.

As stated above, SQL Server 2012 is supported with only partially contained state. A partially contained database is a contained database that allows the use of uncontained features.

According to BOL, use the sys.dm_db_uncontained_entities and sys.sql_modules view to return information about uncontained objects or features. By determining the containment status of the elements of your database, you can discover what objects or features must be replaced or altered to promote containment.

The behavior of partially contained databases differs most distinctly from that of non-contained databases with regard to collation. 

How to create?

Ok, enough theory. there are three steps to create a contained database;

1. First, we need to enable the "contained database authentication". We can do this using T-SQL;

sp_configure 'contained database authentication', 1;

GO

RECONFIGURE

GO

2. Then we create a new database as contained database;

USE master

GO

CREATE  DATABASE TestDB

CONTAINMENT=PARTIAL
GO

--Create table with records

USE TestDB
GO

CREATE TABLE tblTest(

id int ,
Name varchar (250)
)

GO

INSERT INTO tblTest

VALUES

(10,'Atif'),
(20,'Imran'),
(30,'Asif')
GO 

3. Then, at last, we will be creating the user(s) for the our partially contained database;

USE TestDB

GO

CREATE USER TestUser WITH PASSWORD=N'testCUser1$',DEFAULT_SCHEMA=dbo

GO

EXEC sp_addrolemember'db_owner', 'TestUser'

GO

In order to test our exercise, we have to log into a new session using the above created user. Check you server Server authentication settings. As I am using sql server user and not a Domain user, the instance should be configured as "SQL Server and Windows authentication mode". In the figure I have specified the user name and password of the user that we have created in the contained database;

Next, we have to specify the name of the contained database. You will have to type it as the user you have specified does not exists in the sql server instance.

Once the login name is authenticated, you will have you object explorer listing only the contained database;

That's it. We have successfully created a contained database.
Ok, what about the old databases that i need to convert to partial contained database?

We can convert an uncontained database to partial contained database by using the following 3 steps;

1. Enable the "contained database authentication" as above.
2. Alter the database as;

ALTER DATABASE [MyDatabase]

SET CONTAINMENT=PARTIAL

GO

3. In the last step, we move the user(s) to the database;

sp_migrate_user_to_contained

@username = N'MyDBLogin',

@rename = N'keep_name',

@disablelogin = N'disable_login'

GO

In the above statement, we have un-authorized the 'MyDBLogin' to log in the instance. We did not changed the login name here by specifying the value 'keep_name' for @rename.

Now, if you log in using this user as in our last example, you will only see the 'MyDatabase' in teh object explorer.

Hope this will help you.

Sequence Object in SQL Server 2012

MS SQL Server has introduced a new feature of Sequence Objects in SQL Server 2012. In the prior versions, IDENTITY was used to generate sequence numbers. The limitation of IDENTITY was that it was related to one table. The scope of Sequence Objects is database. So, if you want to add a same value to two tables, you can use this feature. Also, you can generate the next sequence number before going into the transaction. The IDENTITY is incremented in the transaction at the time of INSERT. Another plus point of Sequence Object is that you can define the maximum value for the object and re-initialize the counter of the object without any extra programming effort.

Here is the code of creating teh new sequence object in SQL Server 2012 Management Studio;

CREATE SEQUENCE dbo.NextCustomerID
    as BIGINT
    START WITH 1
    INCREMENT BY 1;
GO

This will add a new sequence object. You can see this object in Object Explorer under your database in Programming --> Sequence folder.

Well, it not over yet. The query used to create this object creates the object with the default values. You can set many parameters. If you right click the newly created object and click properties, this will open the property window for this object. Here you can set Minimum value, Maximum Value, Cycle and Cache.

Minimum and Maximum are the minimum and maximum values for the object. Cycle check box states that the value will re-initialize when the maximum value is reached. You can also set the re-start value (From where it will start after reaching the maximum value). Cache option tells the object how many values it will have remaining to avoid extra Disk I/Os during the increment process. For example, if cache size is set to 25, it will  not generate 25 numbers in it memory. It will only keep the current value and the remaining values. If 3 IDs are generated for the object with cache of 25, it will have 4 and 22 in its memory. For more information on cache mechanism you can Click Here and check the Cache Management section.

Ok, here is the code for generating the sequence number from the object;

-- Get the next available ID
DECLARE @CustID BIGINT;
SELECT @CustID = NEXT VALUE FOR dbo.NextCustomerID;
SELECT @CustID as 'Customer ID';

Please remember that the sequence object is not transaction specific. Once you generate the sequence with the NEXT VALUE, it is consumed weather the transaction is committed and rolled back. This is similar to the IDENTITY. Hence, the sequence objects also may have gaps in between as we have in IDENTITY columns.

In case you want to restart the sequence, you can use;

ALTER SEQUENCE dbo.NextCustomerID

RESTART

You can use the OVER ORDER BY clause with NEXT VALUE FOR for generating the next sequence values;

SELECT NEXT VALUE FOR dbo.NextCustomerID OVER (ORDER BY RNO) AS ListNumber,

    Name

FROM tblTest ;

You can check books online for more information.

There are also the limitations of the Sequence Objects which I will cover in the separate topic and try to explain with examples.

Columnstore Indexes

This is the new feature added in SQL Server 2012 to overcome query performance and index data compression issues. The data stored in the columnstore index is pretty much compressed and very optimized. In SQL Srever 2012, if you have a columnstore index, you cannot perform the DML operations on the table i.e. you cannot insert / update or delete any record. The purpose of this index is for Data warehousing. You have to devise the logic in order to populate the data, for example table partitioning. But this issue is resolved in SQL Server 2014. But there, you will have to create a CLUSTERED COLUMNSTORE INDEX.

You can add this index simply by right clicking the Index folder under the table on which you want to add the column store index;

On clicking the option, a screen will open asking to add the column(s) on which you want to create a column store index (FirstName, LastName).

Leave rest of the properties to default. Click OK button to add the index.

Now, question is how the data will be stored? We have two types of data storge in sql server, RowStore and Column Store. Assume you have a tabel as

RNO       NAME          FNAME
1              Atif                Sheikh
2              Asif                Ahmed
3             Imran              Khan

In RowStore index, the data will be saved as;

Atif,Sheikh
Asif,Ahmed
Imran,Khan

The above data will be saved as a row in the 8k page.

And this will go on until the 8k page is complete.

In case of Columnstore index, the data will be stored as a segment of its respective column. Each segment have only one column. This goes for all the columns in the index. A Column can span on multiple segments and a segment may have multiple data pages. As for above example the data will be stored as;

Col1               Col2

Atif                  Sheikh
Asif                 Ahmed
Imran              Khan

These columns are just like pointers in the 8k page of the index. One page can have multiple columns

Now if you will query with NAME or FNAME in your WHERE condition, the data search and retrieval will be very fast as each condition will have to check one single column. You can check this with Statistics and Execution plans on you tables.

Paging in SQL Server 2012

As we all know that MS has given us the FETCH NEXT clause in the query to apply paging. Let me explain this feature by adding a temp table of 1 million records.

with wcte as (

Select Row_number()over (order by a.name) as RNO

from sys.columns a, sys.columns b, sys.columns c

) Select rno , 'Name: ' + CAST(rno as varchar(10))  as Name

into tblTest

from wcte where RNO <= 1000000

Add primary key on RNO column through the designer. This will add a clustered index on this column.

Now, lets write the query for the SQL Server 2005 / 2008 / 2008 R2 using CTE;

;With wcte as (

Select Row_Number() over (Order by Rno) Rno2,* from tblTest WHERE Name is Not Null

) Select * from wcte where rno2 > 899979 and rno2 < 900000

order by rno

 If you look at the execution plan of this query, it will be like this;


Now lets write the query for SQL Server 2012 paging;

Declare @vPageNo int

Declare @vOffset int

Declare @vPageSize int

Set @vPageNo = 45000

Set @vPageSize = 20

Set @vOffset = (@vPageNo-1) * @vPageSize

SELECT *

FROM tblTest

WHERE Name is Not Null

ORDER BY rno

   OFFSET @vOffset ROWS

   FETCH NEXT @vPageSize ROWS ONLY

I have added the variables for passing OFFSET and PAGESIZE. This gives an additional hint for passing these from the front end as Input parameters of stored procedure.

The execution plan for this query is;

Now, it is very clear that the paging query of SQL Server 2012 performed very limited functions and performed 100% index scan. The SQL Server 2008 query performed many additional functions which makes it a bit smaller with my 2 column table.

Results might be different if you apply the same scenario directly on the clustered indexed column (RNO of tbltest in my case) instead of applying Row_Number() function. This is not the real time case as you never have IDs in sequence in you db tables as sequences break when you delete or migrate. More importantly, you apply paging on queries with WHERE clause. And you cannot use ID columns for paging in any case with WHERE Clause.

Connection Strings - Impact on Performance & Security

You can use the following values of connection string intelligently to get maximum performance according to your environment;
1. Asynchronous Processing: When true, enables asynchronous operation support. Recognized values are true, false, yes, and no.

2. Connection Lifetime or Load Balance Timeout: When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. This is useful in clustered configurations to force load balancing between a running server and a server just brought online. A value of zero (0) causes pooled connections to have the maximum connection timeout.

3. Connection Timeout: The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. Valid values are greater than or equal to 0 and less than or equal to 2147483647.

4. Max Pool Size: This property is an integer with a default of 100 and specifies the maximum number of connections to maintain in the pool.

5. Min Pool Size: This property is an integer with a default of zero. It specifies a minimum number of connections to maintain in the pool. If you set this to 5, for example, the first time you connect to the server, ADO.NET will create five connections and prepare them for pooling.

6. The pool limiter

The Max Pool Size property acts as a limiter to the connection pool. If you leave this property set to 100 (the default), up to 100 connections from your application will be placed in the pool. When the 101st connection request comes along, it is queued to wait for an available connection. If no connection becomes available during the timeout period for the connection string (this period can be customized with the Connect Timeout connection string property and defaults to 60 seconds), an error is returned instead of an open connection.

Create trigger to restrict a user(s)

Create trigger to restrict a user(s) from accessing table for INSERT/DELETE/UPDATE in given time;

CREATE TRIGGER dbo.trg_Trigger

   ON dbo.YourTable

   AFTER INSERT,DELETE,UPDATE

AS

BEGIN

    SET NOCOUNT ON;

   -- If you are using SQL Server authentication, you can use '%\A' or 'domainname\A'

   if SYSTEM_USER = 'A' And DatePart(hh,getdate()) between 10 and 13

   begin

     Raiserror('Un Authorize access.',11,1)

     Rollback

     Return

   end

END

GO

Filtered Indexes

If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.
For example;

CREATE TABLE TestTable (a int, b varbinary(4));

GO

IF EXISTS ( SELECT name from sys.indexes

    WHERE name = N'TestTabIndex'

    AND object_id = OBJECT_ID (N'dbo.TestTable'))

DROP INDEX TestTabIndex on dbo.TestTable

GO

CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)

WHERE b = 1;

GO

The above CREATE INDEX statement generates error.
To rectify it, we have to use CONVERT or CAST

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)

WHERE b = CONVERT(Varbinary(4), 1);

GO

Moving the data conversion from the left side to the right side of a comparison operator might change the meaning of the conversion. In the above example, when the CONVERT operator was added to the right side, the comparison changed from an integer comparison to a varbinary comparison.
Another Issue is that the query optimizer will not use a filtered index if the query can return rows that are not in the filtered index. If a filtered index is used explicitly as a table hint and the filtered index might not contain all of the query results, the query optimizer generates query compilation error 8622.

SET QUOTEDIDENTIFIER ON/OFF can also generate the error. This thing is explained here