Function to Split Multi-valued String

1. Can you write a query to split a comma seperated value?
2. Can you create a function to spilt a delimitted string into multipled rows? Delimiter can be any char like comma (,), @, &, ; etc.
3. How to use a multi valued parameter in a Stored Procedure to filter report data? I am sure you can't use a multi valued parameter directly in T-SQL code without splitting multiple values.

To find the answer of above questions create a user defined function using below T-SQL code:

/**********************************************
CREATED BY HARI
PURPOSE : To split comma seperated values
--------------------------------------------
Use this function to split any multivalued string
seperated by any delimiter into multiple rows
***********************************************/
CREATE FUNCTION [dbo].[SplitMultivaluedString]
(
   @DelimittedString [varchar](max),
   @Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
   DECLARE @sTemp [varchar](max)
   SET @sTemp = ISNULL(@DelimittedString,'') + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,CharIndex(@Delimiter,@sTemp)-1)
     
      SET @sTemp = RIGHT(@sTemp,LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
   END
   RETURN
END
GO

/* How to use this function:
SELECT * FROM [dbo].[SplitMultivaluedString] ('1,2,3,4', ',')
SELECT * FROM [dbo].[SplitMultivaluedString] ('1;2;3;4', ';')
*/

COUNT Number of Records for all the Tables in a Database

Easiest way to get an exact value of Number of Rows for all the tables in a SQL Server database

1. Use DBCC UPDATEUSAGE - this updates the values of rows for each partition in a table.
2. Use undocumented stored procedure sp_msForEachTable and store the result set in a table.

Below is the query to get required output:

USE [DatabaseName]
GO

DECLARE @DynSQL NVARCHAR(255)
SET @DynSQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC(@DynSQL)

IF OBJECT_ID('tempdb..#T','U') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T (TableName nvarchar(500),NumberOfRows int)
GO

INSERT INTO #T
EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1) as TableName,COUNT(*) as NumberOfRows FROM ?'
GO 
SELECT * FROM #T ORDER BY NumberOfRows DESC

How to get Month Number from Month Name

How to convert month name to month number?
Here are two solutions, however, first one is best solution.

--Approach 1 [Best way]--
---------------------------------
DECLARE @MonthName varchar(15)
SET @MonthName = 'January'
SELECT MONTH(CAST(@MonthName + '1 2010' AS datetime))  AS MonthNo
GO


--Approach 2 --
---------------------------------
DECLARE @MonthName varchar(15)
SET @MonthName = 'January'
SELECT CASE @MonthName
   WHEN 'January' THEN 1
   WHEN 'February' THEN 2
   WHEN 'March' THEN 3
   WHEN 'April' THEN 4
   WHEN 'May' THEN 5
   WHEN 'June' THEN 6
   WHEN 'July' THEN 7
   WHEN 'August' THEN 8
   WHEN 'September' THEN 9
   WHEN 'October' THEN 10
   WHEN 'November' THEN 11
   WHEN 'December' THEN 12
END MonthNo
GO

What is WITH TIES clause in SQL Server?

Have you ever used WITH TIES keyword?

Actually TOP keyword specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows. Here is Transact-SQL Syntax:

[

      TOP (expression) [PERCENT] 

      [ WITH TIES ]

 ]

Arguments

expression: is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

PERCENT: indicates that the query returns only the first expression percent of rows from the result set.

WITH TIES: specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.

Example:

DECLARE @TEST TABLE(
ID int, Amount int)
INSERT INTO @TEST VALUES
(1,10),(2,30),(3,60),
(4,50),(5,50),(6,70),
(7,20),(8,70),(9,70),
(10,44),(11,80),(12,90)

Below is the result-set using TOP without and with clause WITH TIES.

What is Log Shipping in SQL Server 2008?

How to Perform SQL Server Log Shipping: Log shipping is the process of automating the backup ofdatabase and  transaction log files on a production SQL server, and then restoring them onto a standby server. Only Enterprise Editions support log shipping. In log shipping, the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db can be used this as the Disaster Recovery plan. The key feature of log shipping is that is will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

Log shipping

• Automated process to ship transaction log backups to another server
• Three jobs to perform the required actions: backup, copy and recover
• Involves three servers/instances: primary, secondary and (optional) monitoring server
• Requires full or bulk logged mode
• Can have multiple secondary copies
• Information about log shipping kept in MSDB

Log Shipping Options

• Interval – Default is 15 minutes. Consider the impact before using a smaller interval
• Delayed log restore – option that allows you to have an older copy
• If secondary is far behind. Consider backup/copy to media/restore, consider partitioning the data
• Careful – Backup typically goes to a file server share. Agent account needs access to shared files
• Careful – Weekend maintenance jobs can make you run out of disk space
• Monitoring – MSDB tables, agent history

Problems with Log Shipping

Log shipping is a compromise but it is not the ideal solution, it is a practical solution given real-world budget constraints. Some of the problems with log shipping include:

• Log shipping failover is not automatic. The DBA must be present when the failover occurs.
• The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over.
• Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable.
• The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally.
• When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user's applications to the new standby server. In some cases, neither of these options is practical.

What is a NOLOCK?

Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is used in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared locks, means multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking).

How to use:
SELECT * FROM TableName (NOLOCK)

What is the difference between a local and a global temporary tables?

1. As far as syntax is concern, global temporary tables are created using ## as prefixed while Local temporary tables with #.
2. Global temporary table is available to any connection once created. They are cleared when the last connection is closed. Local temporary table is available to the current DB connection for current user and are cleared when connection is closed.
3. Multiple users can’t share a local temporary table however Global temporary table can be shared by multiple user sessions.
4. The Local temporay table available only in source connection, not for other connections.

Types of User Defined Functions (UDF) in SQL Server 2008?

There are three types of User-Defined functions in SQL Server:

1. Scalar Function
2. Inline Function
3. Multi-statement Table-valued Function

Scalar UDFs

Scalar UDFs return a single value. They are similar to built-in functions such as GETDATE(), or OBJECT_NAME(), which return a single string, date, or integer. The value returned by a scalar UDF can be based on the parameters passed.
Scalar UDFs can return any scalar system-supplied data type, except TIMESTAMP. You cannot return values with a user-defined data type from scalar UDFs. If you want to do so, you must specify the underlying system-supplied data type instead.

In-line UDF
In-line UDFs return a single row or multiple rows and can contain a single SELECT statement. Because in-line UDFs are limited to a single SELECT, they can't contain much logic. They can be effective, however, for lookups that return multiple values, such as the top five best-selling books with title, author, and publication date.

Multi-statement UDFs

The multi-statement UDFs can contain any number of statements that populate the table variable to be returned. Notice that although you can use INSERT, UPDATE, and DELETE statements against the table variable being returned, a function cannot modify data in permanent tables. Multi-statement UDFs come in handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.

Difference between Function and Stored Procedure?

• User Defined Function (UDF) can be used in the SQL statements anywhere in the SELECT, WHERE, and HAVING section where as Stored procedures cannot be used.
• Functions are designed to send their output to a query or T-SQL statement while Stored Procedures use EXECUTE or EXEC to run.
• We can not use EXECUTE and PRINT commands inside a function but in SPROC
• UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
• UDFs can't change the server environment or your operating system environment, while a SPROC can.
• Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
• Stored Procedures are stored in compiled format in the database where as Functions are compiled and excuted runtime.
• SPROC can be used with XML FOR Clause but Functions can not be.
• SPROC can have transaction but not Functions.
• Functions can be used in a SPROC but SPROC cann't be used in a Function. Only extended stored procedures can be called from a function.
• Of course there will be Syntax differences and here is a sample of that

CREATE PROCEDURE dbo.ProcedureName
(
   @parameter1 datatype = DefaultValue,
   @parameter2 datatype OUTPUT
)
AS 
BEGIN
   T-SQL statements
   RETURN
END
GO

CREATE FUNCTION dbo.FunctionName
(
   @parameter1 datatype = DefaultValue,
   @parameter2 datatype
)
RETURNS datatype
AS
BEGIN
   SQL Statement
   RETURN Value
END
GO

What is User-Defined Function (UDF) in SQL Server?

User-Defined Functions allow to define its own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type. A function can have a maximum of 1024 input parameters. When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value. This behavior is different from parameters with default values in user-defined stored procedures in which omitting the parameter also implies the default value. User-defined functions do not support output parameters. When you can't find a built-in function that meets your needs, you can write your own.

User-defined functions are created using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and removed using the DROP FUNCTION statement. Each fully qualified user-defined function name (schema_name.function_name) must be unique.


Valid Statements in a Function
The types of statements that are valid in a function include:

• DECLARE statements can be used to define data variables and cursors that are local to the function.
• Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.
• Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.
• Control-of-flow statements except TRY...CATCH statements.
• SELECT statements containing select lists with expressions that assign values to variables that are local to the function.
• UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.
• EXECUTE statements calling an extended stored procedure.

 The following nondeterministic built-in functions cannot be used in Transact-SQL user-defined functions.

1. NEWID
2. RAND
3. NEWSEQUENTIALID
4. TEXTPTR

What is Stored Procedure?

A stored procedure is the saved collection of T-SQL statements in the SQL Server database that can take and return user-supplied parameters. Stored Procedures (also called sproc) can have input parameters and output parameters. Same stored procedure can be used over the network by several clients by passing different input data for input parameters. When a stored procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance.

Here is the syntax of SPROC:

CREATE PROC [ EDURE ] [owner.] ProcedureName
[ { @parameter data_type } [= DefaultValue] [OUTPUT] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
AS
BEGIN
   T-SQL-statements
END

Here is an example:
CREATE PROC ProductList (
   @ProductType varchar(30) = 'Electronics')
WITH ENCRYPTION
AS
BEGIN
   SELECT
     ProductName, ProductType,
     Model, UnitPrice
   FROM Products (NOLOCK)
   WHERE ProductType = @ProductType
END

How to execute:
-- Return all the products belongs to Electronics
EXECUTE ProductList
GO

-- Return all the products belongs to Auto Mobiles
EXECUTE ProductList 'Auto Mobiles'
GO


SQL Statement Limitations in a Procedure
Any SET statement can be specified inside a stored procedure except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, which must be the only statements in the batch.

Inside a stored procedure, object names used with certain statements must be qualified with the name of the object owner if other users are to use the stored procedure. The statements are:
•ALTER TABLE
•CREATE INDEX
•CREATE TABLE
•All DBCC statements
•DROP TABLE
•DROP INDEX
•TRUNCATE TABLE
•UPDATE STATISTICS

Permissions
CREATE PROCEDURE permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. Permission to execute a stored procedure is given to the procedure owner, who can then set execution permission for other database users.