Find Table Locked Session

Some time your table may locked by some session and you want to know that session and why i can not access my table?

This mean your begain transaction is completed and insert/update/delete is runing but Comit transacion is yet be run

OR you can create the situation by running the below query:


BEGIN TRANSACTION
DELETE * FROM Table1

Below script will allow you to find which session is Locking your table:

SELECT
OBJECT_NAME(P.object_id) AS TableName,
Resource_type,
request_session_id
FROM
sys.dm_tran_locks L
join sys.partitions P
ON L.resource_associated_entity_id = p.hobt_id
WHERE   OBJECT_NAME(P.object_id) = 'Table1'

To release the Loking you can kill that session using below script:

KILL 54

 

If you want to know in detail why, when and which session is locking then below script is useful:


SELECT
blocking_session_id AS BlockingSessionID,
session_id AS VictimSessionID,
(SELECT [text] FROM sys.sysprocesses
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE spid = blocking_session_id) AS BlockingQuery,
[text] AS VictimQuery,
wait_time/1000 AS WaitDurationSecond,
wait_type AS WaitType,
percent_complete AS BlockingQueryCompletePercent
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0

How to pass parameters dynamically

How to pass parameters dynamically?
MS-SQL has many features to call the procedure from other application and from SQL itself, See the example.
Some time you need optional parameter in Procedure then assign null as default so that it will not ask you compulsory.

CREATE PROCEDURE dbo.TESTPROCEDURE
@param1 VARCHAR(32) = NULL,
@param2 INT = NULL
AS
BEGIN
SET NOCOUNT ON

SELECT Param1 = COALESCE
(
@param1,
'@param1 was Blank'
)

SELECT Param2 = COALESCE
(
RTRIM(@param2),
'@param2 was Blank'
)
END
GO

EXEC dbo.TESTPROCEDURE @param1='HELLO', @param2=1
EXEC dbo.TESTPROCEDURE @param1='HELLO'
EXEC dbo.TESTPROCEDURE @param2=1
EXEC dbo.TESTPROCEDURE 'HELLO',1
EXEC dbo.TESTPROCEDURE 'HELLO

There are so many way to get the result from procedure, Choose any of them which you like.

Format money/decimal with commas

SQL format money/decimal with commas:

Sometimes you want to have your money fields properly formatted with commas like this: 13,243,543.57
You can use the CONVERT function and give a value between 0 and 2 to the style and the format will be displayed based on that

Below is an example:

DECLARE @v MONEY
SELECT @v = 1322323.6666

SELECT CONVERT(VARCHAR,@v,0)  --1322323.67
Rounded but no formatting

SELECT CONVERT(VARCHAR,@v,1)    --1,322,323.67
Formatted with commas

SELECT CONVERT(VARCHAR,@v,2)    --1322323.6666
No formatting

If you have a decimal field it doesn’t work with the convert function
The work around is to convert it to money:

DECLARE @v2 DECIMAL (36,10)
SELECT @v2 = 13243543.56565656

SELECT CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.57
Formatted with commas


Difference with (nolock) vs (nolock) as a SQL Table Hint

Difference with (nolock) vs (nolock) as a SQL Table Hint:

select sum(amount) from reward_expired (nolock)

difference was between using the table hint (nolock) and the table hint with (nolock).
Look at MSDN under SQL 2005,

“In SQL Server 2005, with some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. Table hints also must be specified with parentheses.

The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone.”

Micorosft plan to remove the ability to use just (nolock), and to future proof all SQL, users should write all table hints using the with keyword.

Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

HOW TO USE ROLLUP and CUBE

HOW TO USE CUBE:

The CUBE operator generates a result set that is a multidimensional cube. A multidimensional cube is an expansion of fact data, or data that records individual events. The expansion is based on columns that the user wants to analyze. These columns are called dimensions. The cube is a result set containing a cross tabulation of all the possible combinations of the dimensions.

The CUBE operator is specified in the GROUP BY clause of a SELECT statement. The select list contains the dimension columns and aggregate function expressions. The GROUP BY specifies the dimension columns and the keywords WITH CUBE. The result set contains all possible combinations of the values in the dimension columns, along with the aggregate values from the underlying rows that match that combination of dimension values.

For example, a simple table Inventory contains:

Item                 Color                Quantity
-------------------- -------------------- --------------------------
Table                Blue                 124
Table                Red                  223
Chair                Blue                 101
Chair                Red                  210

This query returns a result set that contains the Quantity subtotal for all possible combinations of Item and Color:

SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

Here is the result set:

Item                 Color                QtySum
-------------------- -------------------- --------------------------
Chair                Blue                 101.00
Chair                Red                  210.00
Chair                (null)               311.00
Table                Blue                 124.00
Table                Red                  223.00
Table                (null)               347.00
(null)               (null)               658.00
(null)               Blue                 225.00
(null)               Red                  433.00

The following rows from the result set are of special interest:

Chair (null) 311.00

This row reports a subtotal for all rows having the value Chair in the Item dimension. The value NULL is returned for the Color dimension to show that aggregate reported by the row includes rows with any value of the Color dimension.

Table (null) 347.00

This row is similar, but reports the subtotal for all rows having Table in the Item dimension.

(null) (null) 658.00

This row reports the grand total for the cube. Both the Item and Color dimensions have the value NULL showing that all values of both dimensions are summarized in the row.

(null) Blue 225.00
(null) Red 433.00

These two rows report the subtotals for the Color dimension. Both have NULL in the Item dimension to show that the aggregate data came from rows having any value for the Item dimension.

Using GROUPING to Distinguish Null Values
The null values generated by the CUBE operation present a problem: How can a NULL generated by the CUBE operation be distinguished from a NULL returned in the actual data? This is achieved using the GROUPING function. The GROUPING function returns 0, if the column value came from the fact data, and 1 if the column value is a NULL generated by the CUBE operation. In a CUBE operation, a generated NULL represents all values. The SELECT statement can be written to use the GROUPING function to substitute the string ALL in place of any generated NULL. Because a NULL from the fact data indicates the data value is unknown, the SELECT can also be coded to return the string UNKNOWN in place of any NULL from the fact data. For example:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

Multidimensional Cubes
The CUBE operator can be used to generate n-dimensional cubes, or cubes with any number of dimensions. A single dimension cube can be used to generate a total, for example:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item WITH CUBE
GO

This SELECT statement returns a result set showing both the subtotals for each value of Item and the grand total for all values of Item:

Item                 QtySum
-------------------- --------------------------
Chair                311.00
Table                347.00
ALL                  658.00

SELECT statements that contain a CUBE with many dimensions can generate large result sets, because these statements generate rows for all combinations of the values in all the dimensions. These large result sets may contain too much data to be easily read and understood. One solution to this problem is to put the SELECT statement into a view:

CREATE VIEW InvCube AS
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

The view can then be used to query only the dimension values of interest:

SELECT *
FROM InvCube
WHERE Item = 'Chair'
AND Color = 'ALL'

Item                 Color                QtySum
-------------------- -------------------- --------------------------
Chair                ALL                  311.00

HOW TO USE  ROLLUP:

The ROLLUP operator is useful in generating reports that contain subtotals and totals. The ROLLUP operator generates a result set that is similar to the result sets generated by the CUBE operator. For more information, see Summarizing Data Using CUBE.

The differences between CUBE and ROLLUP are:

CUBE generates a result set showing aggregates for all combinations of values in the selected columns.

ROLLUP generates a result set showing aggregates for a hierarchy of values in the selected columns.
For example, a simple table Inventory contains:

Item                 Color                Quantity
-------------------- -------------------- --------------------------
Table                Blue                 124
Table                Red                  223
Chair                Blue                 101
Chair                Red                  210

This query generates a subtotal report:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Item                 Color                QtySum
-------------------- -------------------- --------------------------
Chair                Blue                 101.00
Chair                Red                  210.00
Chair                ALL                  311.00
Table                Blue                 124.00
Table                Red                  223.00
Table                ALL                  347.00
ALL                  ALL                  658.00
(7 row(s) affected)

If the ROLLUP keyword in the query is changed to CUBE, the CUBE result set is the same, except these two additional rows are returned at the end:

ALL Blue 225.00
ALL Red 433.00

The CUBE operation generated rows for possible combinations of values from both Item and Color. For example, not only does CUBE report all possible combinations of Color values combined with the Item value Chair (Red, Blue, and Red + Blue), it also reports all possible combinations of Item values combined with the Color value Red (Chair, Table, and Chair + Table).

For each value in the columns on the right in the GROUP BY clause, the ROLLUP operation does not report all possible combinations of values from the column (or columns) on the left. For example, ROLLUP does not report all the possible combinations of Item values for each Color value.

The result set of a ROLLUP operation has functionality similar to that returned by a COMPUTE BY; however, ROLLUP has these advantages:

ROLLUP returns a single result set; COMPUTE BY returns multiple result sets that increase the complexity of application code.

ROLLUP can be used in a server cursor; COMPUTE BY cannot.

The query optimizer can sometimes generate more efficient execution plans for ROLLUP than it can for COMPUTE BY.

SQL running serial generating tips

SQL running serial generating tips:

Traditionally developers and Database administrators used temporary tables and co-related sub-queries to generate calculated row numbers in a query. Now SQL Server 2005 provides a function, which replaces all of the additional resources we used to generate row numbers.

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Example:

select ROW_NUMBER() OVER(ORDER BY CLM1),* from Table1

Remark:

The ORDER BY clause determines the sequence in which the rows are assigned their unique
ROW_NUMBER within a specified partition.

Enable remote connection SQL Server

Error Description ::

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition

You must enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:

  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
  2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
  3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.Note Click OK when you receive the following message:
    Changes to Connection Settings will not take effect until you restart the Database Engine service.
  4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Enable the SQL Server Browser service:

  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
  2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
  3. On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic for Startup type, and then click Apply.Note When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
  4. Click Start, and then click OK.

Create exceptions in Windows Firewall:

  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
  2. In SQL Server Configuration Manager, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
  3. On the SQL Server Browser Properties page, click the Advanced tab, locate the instance ID in the property list, and then click OK.

To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK

Create an exception for SQL Server 2005 in Windows Firewall

To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:

  1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
  2. In the Add a Program window, click Browse.
  3. Click the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlservr.exe executable program, click Open, and then click OK.Note The path may be different depending on where SQL Server 2005 is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
  4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.

Create an exception for the SQL Server Browser service in Windows Firewall

To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:

  1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
  2. In the Add a Program window, click Browse.
  3. Click the C:Program FilesMicrosoft SQL Server90Sharedsqlbrowser.exe executable program, click Open, and then click OK.