Advertisements

Archive

Archive for the ‘SQL Server Questions’ Category

“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.


Advertisements

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: https://sqlwithmanoj.com/2012/02/28/sql-server-2012-a-k-a-denali-new-feature-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]) 
VALUES('srv1','sp_addlinkedsrvlogin','')

INSERT INTO [dbo].[audit]([server_instance_name],[statement],[additional_information]) 
VALUES('srv2','','<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><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:

;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data' as ns)
SELECT 
	 [server_instance_name]
	,[statement]
	,[additional_information]
	,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)
GO

-- OR -- 

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data')
SELECT 
	 [server_instance_name]
	,[statement]
	,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)
GO

 

Drop table finally

DROP TABLE [audit]
GO

 

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.

e.g.

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.