Archive for the ‘SQL Server Questions’ Category

Store file data to SQL without streaming on server – MSDN TSQL forum

October 14, 2015 1 comment

–> Question:

I need to store file(s) to SQL without streaming / reading at Server. I have created a Web API with AngularJS and SQL.


var fileType = httpRequest.Files[file].ContentType;
var fileStrm = httpRequest.Files[file].InputStream;
var fileSize = httpRequest.Files[file].ContentLength;
byte[] fileRcrd = new byte[fileSize];
var file_Name = Path.GetFileName(filePath);
fileStrm.Read(fileRcrd, 0, fileSize);

Is it possible to send file data to SQL (in bytes) without streaming / reading at server?

I don’t want to put a load on server for large files. just read the data and send them to SQL where SQL will do the streaming and store data as varbinary.

–> Answer:

Store the file in File System via FileTable feature that uses filestream out of the box.

Check this blog on how to setup and use FileTables starting SQL Server 2012.

You can also setup Full Text Search over these files here.

Ref link.

What does “on-disk structure” means w.r.t Indexes in SQL Server – MSDN TSQL forum

August 18, 2015 Leave a comment

–> Question:

According to MSDN, an index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view.

In this definition, I don’t get what on-disk structure means.

–> My Answer:

Do not confuse with on-disk structure statement, not only index, a table is also an on-disk structure as all you data sits on-disk.

Check my response on you other similar question in MSDN TSQL forum.

Just like a table, an index is also on disk which can be built on top a table or separately.

– On top to restructure the whole table which is called the Clustered Index. Clustered Index is nothing but the table itself converted from a heap to a b-tree.

– Separately like a Non-Clustered index, which is a copy of the table with specific columns, created separately for performance reasons to read data will less columns.

Ref Link.

How exactly STATIC CURSOR works internally – MSDN TSQL forum

August 12, 2015 Leave a comment

–> Question:

In MSDN help mentions static cursor as follows:

STATIC: Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications

It say’s that modifications is not allowed in the static cursor.

I have a questions regarding that:

declare ll cursor global static
for select name, salary from ag
open ll
fetch from ll

while @@FETCH_STATUS=0
fetch from ll
update ag set salary=200 where 1=1

close ll
deallocate ll

In “AG” table, “SALARY” was 100 for all the entries.

When I run the Cursor, it showed the salary value as “100” correctly.

After the cursor was closed, I run the query select * from AG.

But the result had updated to salary 200 as given in the cursor.

Help file says modifications is not allowed in the static cursor. But I am able to update the data using static cursor.

–> My Answer:

MSDN meant that:

“After you create a STATIC Cursor if the table is updated by any other process that update will not be reflected in the Static Cursor rows.”

1. Let’s say your SPID=100 and you read the table with Salary=100.

2. You created a STATIC Cursor and read the above table with Salary=100.

3. Another SPID=105 updated that Salary=200

4. But when you will read the cursor it will still show you Salary=100.

But yes you can update the same table form the Cursor itself as you are doing above. Check this MSDN link for clear info:

Ref Link.

Difference between Index and Primary Key – MSDN TSQL forum

August 10, 2015 Leave a comment

–> Question:

What is the difference between the Index and the Primary Key?

–> My Answer:

In simple DBMS words:

– A Primary Key is a constraint or a Rule that makes sure to identify a table’s column uniquely and enforces it contains a value, ie. NOT NULL value.

– An Index on the other side is not a constraint, but helps you organize the table or selected columns to retrieve rows faster while querying with SELECT statement.

In SQL Server you can create only one Primary Key, and by-default it creates a Clustered Index on the table with the PK column as the Index key. But you can specify to create Non-Clustered Index with a PK also.

Indexes in SQL Server mainly are:

– Clustered Index

– Non Clustered

… you can specify them as unique or non-unique.

Other type of indexes are:

– ColumnStore

– Filtered


– Spatial

– Full Text

–> Another Answer by Erland:

A primary key is a logical concept. The primary key are the column(s) that serves to identify the rows.

An index is a physical concept and serves as a means to locate rows faster, but is not intended to define rules for the table. (But this is not true in practice, since some rules can only be defined through indexes, for instance filtered indexes.)

In SQL Server a primary key for a disk-based table is always implemented as an index. In a so-called memory-optimized table, the primary key can be implemnted as an index or as a hash.

–> Another Answer by CELKO:

PRIMARY KEY is define in the first chapter of the book on RDBMS you are too lazy to read. It is a subset of columns in the table which are all not null and unique in the table. An index is an access method used on the records of a physical file.

Many SQL products use indexes to implement keys; many do not (hashing is a better way for large DB products)

Check the video on Primary Keys:

PK Constraint

Ref link.

Error Msg 8672, MERGE statement attempted to UPDATE or DELETE the same row more than once – MSDN TSQL forum

August 5, 2015 Leave a comment

–> Question:

I am trying to do update/insert operation using merge statement, but getting below error:

“The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.”

I am using SQL Server 2014. I knew there was a bug in older version of SQL Server (like version 2008). Does this issue still persist in latest version of SQL Server?

–> My Answer:
There might be 2 reasons:

1. Your source/target tables might be having DUPLICATES.

2. There might NO DUPLICATES, but you might be using NOLOCK hint? If yes, then your source table might be very big and going through constant changes (INSERT/UPDATE/DELETE), which might be causing PAGE-SPLITS and causing to read single source row TWICE. Try removing NOLOCK and run your MERGE stmt.

–> Another Answer by Naomi:

You need to fix the source of the problem, not apply patches. If your source has duplicate rows for the same key used to update, when how would you know which row will be used to update? The simple solution is to eliminate duplicates by either using correct combinations of keys to join or creating an extra key with ROW_NUMBER() approach.

Ref Link.