Currently browsing tag

SQL Function

Can Views take the Input Parameters

Can Views take the Input Parameters One of my friends is trying to passing some values in views. He told me that is there any options in SQL server to pass the parameters in views.  The answer is NO. It is not possible at any version of the SQL server …

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 …

TIME data type

TIME data type SQL Server 2008 introduced a new data type called TIME, which allow the TIME without DATE. Before SQL Server 2008 it is not possible. In this article I am going to explain the TIME data type by creating scenario to understand it better way. Scenario-1 [ The …

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 …

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

QUOTENAME

QUOTENAME()    QUOTENAME() returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.    Syntax QUOTENAME ( ‘character_string’ [ , ‘quote_character’ ] ) Parameters character_string Is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 …

SQL 2012 FORMAT for Date formatting

SQL 2012 FORMAT for Date formatting In SQL Server 2008 and earlier version used the CONVERT() function to handle the date formatting. As we notice that the function is not very user friendly. In SQL Server 2012, a new function named FORMAT() has been introduced which is much easier to …

WITH ROLLUP

WITH ROLLUP ROLLUP operator is used to generate subtotals and totals. The ROLLUP generates a result set that shows aggregate for a hierarchy of values in the selected columns. Without going in the theoretical details we are going to an example to understand the power of ROLLUP operators. Step-1 [ …

STUFF Function

STUFF Function    In this article I am trying to illustrate a vary useful function of SQL Server called STUFF. STUFF Function Stuff is a T-SQL Function used to delete a specified length of characters within a string and replace with another set of characters.    Syntax: STUFF(Character_expression1, start, length, …