Posts Tagged ‘MSDN TSQL forum’

What is external tables for in SQL Server 2016 ? – MSDN TSQL forum

May 1, 2016 1 comment

–> Question:

Running 2016 CTP3.

I would appreciate your personal comments/experience more than links.

–> Answer:

External Tables in SQL Server 2016 are to setup the new Polybase feature with SQL Server.

With this new feature (Polybase) you can connect to Azure blog storage or Hadoop to query non-relational data from SSMS and integrate with SQL Server relational tables.

You can check some details here, where I’ve :

Check here how to setup Polybase:


Ref link.

“SELECT *” or “SELECT 1” or “SELECT column_Name” while using EXISTS() – what shoud I use? – MSDN TSQL forum

March 9, 2016 2 comments

–> Question:

Please tell me which method is good and fast

1) select a,b from t1 where exists (select 1 from t2 where t1.t1id = t2.t1id )

2) select a,b from t1 where exists (select * from t2 where t1.t1id = t2.t1id )

3) select a,b from t1 where exists (select t2.t1id from t2 where t1.t1id = t2.t1id )

–> My Answer:

It does not matter what you use with SELECT inside EXISTS () statement

Because, as soon as EXIST() statement get one row it breaks/exits, and it just ignores the column’s list in SELECT clause.

You can check by this by simple example:

select a,b from t1 where exists (select 1/0 from t2 where t1.t1id = t2.t1id)

The “SELECT 1/0” ideally should give divide-by-zero error, but here inside EXISTS() it does not.

So, I use “SELECT *” happily within EXISTS() clause 🙂 like:

select a,b from t1 where exists (select * from t2 where t1.t1id = t2.t1id)


–> Answer By CELKO:

The optimizer is smart enough to figure out the exist () predicates are the same. However, this was not always true in early versions of SQL. Oracle originally liked using a constant or an actual column name, because it is it did not have a good optimizer. The original SQL–86 specs implied (but did not require) that the table be materialized. We really were not very smart in those days.

Today, for stylistic reasons and to make searching easier, we prefer exists (SELECT * FROM ..) In code. This makes it easier to locate the exist predicates with this text search, and shows that the operation is performed on an entire table rather than a single column.

SQL is much like a natural language; you can be understood if you speak with poor grammar, but people respect you more if you have good grammar and style.

Ref link.

How to import/store photos with file name and other properties into SQL Server table – MSDN TSQL forum

January 22, 2016 Leave a comment

–> Question:

I was tasked to load several thousand of photos into the SQL Server. Those photos are saved in a specific folder. I want to store the file name in one column and the photo in another column. It would take month for me to enter one by one.

Is there a way to loop through a given folder and add the file name and photo into the tables using TSQL?

–> Answer:

If you are on SQL Server 2012 and ahead, you can use FileTables feature, which is built on top of FileStream and very easy to implement.

FileTable feature does not store files (or images in your case) in SQL Database, but in a secure Windows File-System location which is only visible via SSMS.

Check my blog post on FileTables:

After implementing FileTables feature you just need to copy-paste all these images or any kind of file on this folder and you are done.

The retrieval of data is very easy and you get all the properties stored in the metadata files.

Ref link.

Parse or Query XML column with XMLNAMESPACES (xmlns namespace) – MSDN TSQL forum

January 15, 2016 Leave a comment

–> Question:

We have SQL audit information.
We would like to select XML column in some user friendly way.

CREATE TABLE [dbo].[audit](
	[server_instance_name] [NVARCHAR](128) NULL,
	[statement] [NVARCHAR](4000) NULL,
	[additional_information] XML NULL

INSERT INTO [dbo].[audit]([server_instance_name],[statement],[additional_information]) 

INSERT INTO [dbo].[audit]([server_instance_name],[statement],[additional_information]) 
VALUES('srv2','','<action_info xmlns=""><session><![CDATA[Audit$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info>')

SELECT * FROM [dbo].[audit]

Output of the XML column:

XML parse

Required Output:

XML parse2


–> Answer:

	,t.c.value ('ns:session[1]', 'varchar(50)') AS session
	,t.c.value ('ns:action[1]', 'varchar(50)') AS action
	,t.c.value ('ns:startup_type[1]', 'varchar(50)') AS startup_type
	,t.c.value ('ns:object[1]', 'varchar(50)') AS object
FROM [audit] as a
OUTER APPLY a.additional_information.nodes('//ns:action_info') as t(c)

-- OR -- 

	,t.c.value ('session[1]', 'varchar(50)') AS session
	,t.c.value ('action[1]', 'varchar(50)') AS action
	,t.c.value ('startup_type[1]', 'varchar(50)') AS startup_type
	,t.c.value ('object[1]', 'varchar(50)') AS object
FROM [audit] as a
OUTER APPLY a.additional_information.nodes('//action_info') as t(c)


Drop table finally

DROP TABLE [audit]


Ref link.

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.