Currently browsing tag

T-SQL

Comma Separated Value CSV

Comma Separated Value (CSV) In this article I am trying to demonstrate, how to generate comma separated value (CSV) from a particular table objects fields. So I am going to demonstrate it as step by step that we can understand it properly. Step-1 [ The base Table Objects ] CREATE …

spprocoption

sp_procoption One of my friends asks me to run a stored procedure, when he starts the MS SQL server. His main problem is to run the stored procedure automatically when he starts the MS SQL Server. This article is related to auto starts the stored procedure when the MS SQL …

Running Total

Running Total When I am reviewing a stored procedure, I find that to calculate the value of running total the stored proc contains CURSOR… WHILE Loop etc and contain complex queries. In this article I am demonstrating a simple way to finding the running total in single select statements. Let’s …

Why VIEW takes long time to Execute

Why VIEW takes long time to Execute Here in this article we are discussing related to the performance of the views. Why the performance of the view is slow. Please note that I am not taking about the indexed views, it’s another thing and out of the scope of this …

Finding OBJECTS in DB

Finding OBJECTS in DB Sometime we have confusion that the object presents in the database or not. To find the objects present we have to query the sys.sysobjects. I am trying to make it easy by a stored procedure.  The limitation of the stored procedure is it helps to find …

Partition Table

Partition Table The Table partitioning can make very large tables and index easier to manage and improve the performance of the query. This article is related to table partitioning concept and how you crate the partition table. Hope all of my readers will like it. History Table partitioning was introduced …

GETDATE Vs SYSDATETIME

GETDATE() Vs SYSDATETIME() To get the current system date and time we can use GETDATE() or  SYSDATETIME() function in SQL server.  The question is what the difference between them is. To get the answer let execute the SQL statements mentioned bellow. SELECT GETDATE() ‘GETDATE’,   SYSDATETIME() ‘SYSDATETIME’ The output is mentioned …

SQL 2008 FILESTREAM storage

SQL 2008 FILESTREAM storage SQL Server never is good to storing unstructured data like video, graphics file, MS-Office file etc.  Before SQL server 2008, we have two choices to manage such kind of data mentioned bellow.  1.    By using VARBINARY(MAX) columns inside the database.   2.    Store the data outside …

CROSS APPLY on SQL 2005

CROSS APPLY on SQL 2005 A new feature of Microsoft SQL Server 2005 is “CROSS  APPLY”. It restricted “INNER JOIN” between a table (outer query) and a table-valued function (common usage), or derived table from correlated subquery. The table-valued function is evaluated only for the parameter values supplied by the …

IDENTITY Columns Violation

IDENTITY Columns Violation We all know about the identity columns of SQL Server and how important it is. This article is related to it but I am representing this article to solving a common proble. First take a quick look about the identity columns and how it works. Then we …

XQuery

XQuery XQuery is a very important part of SQL Server and it is introduced from SQL Server 2005. In this article I am trying to explain the very useful features of SQL Server XQuery. Hope it will be interesting. T-SQL supports the subset of XQuery language that is used for …

Computed Columns

Computed Columns This article illustrate about the feature of computed columns. A table can have one or more computed columns. It is an expression defining the value of the specified columns. Let’s take an example CREATE TABLE ComputedColumns       (                   QUANTITY   INT   NULL,                   COST       MONEY NULL,                   INVESTMENT …

TableValued Parameters

Table-Valued Parameters In my previous article I am trying to demonstrate, “How to pass a table or table variable in a stored procedure via xml string”. Some of my reader, asking about Table-valued parameters, the new feature of SQL Server 2008. In this article I am trying to demonstrate related …

TRIM

TRIM() ? One of my friends who love to work at ORACLE, ask me that the Function TRIM() is supported by any version of SQL Server. Like ORACLE the SQL server does not have function which can trim leading or trailing spaces of any string at the same time.   …