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.

Advertisements

Table Hints (Transact-SQL)

Table Hints (Transact-SQL)

Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query processing operation such as a table scan or index seek, or other options.

WITH ( <table_hint> [ [ , ]...n ] )

<table_hint> ::=
[ NOEXPAND ] {
    INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
  | FASTFIRSTROW
  | FORCESEEK
  | HOLDLOCK
  | NOLOCK
  | NOWAIT
  | PAGLOCK
  | READCOMMITTED
  | READCOMMITTEDLOCK
  | READPAST
  | READUNCOMMITTED
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK
} 

<table_hint_limited> ::=
{
    KEEPIDENTITY
  | KEEPDEFAULTS
  | FASTFIRSTROW
  | HOLDLOCK
  | IGNORE_CONSTRAINTS
  | IGNORE_TRIGGERS
  | NOWAIT
  | PAGLOCK
  | READCOMMITTED
  | READCOMMITTEDLOCK
  | READPAST
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK
}

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.

For example:

Select * FROM table1 (TABLOCK)

Select * FROM table1 WITH (TABLOCK, INDEX(myindex))

Reducing SQL Server Deadlocks

Deadlocking and SQL best practices:

Deadlocking refers to the condition in which one resource is waiting on the action of a second, while that second action is waiting on the first. This is different from being blocked, or having to wait for a resource. Using the locks above, if a transaction had a shared lock, then you issued a delete on those same records held by the first lock, you would not be deadlocked. Instead, you would be blocked. When the shared lock was released, your delete statement would complete. Blocking implies some performance hit, but the transaction will complete. It simply has to wait for something else to finish first. A deadlock on the other hand, means there is no way to finish. Your transaction is stuck in a loop with some other transaction. At this point, the database system will usually pick one transaction to be killed so the other can complete.

Here are some tips on how to avoid deadlocking on your SQL Server:

  • Esure the database design is properly normalized.
  • Have the application access server objects in the same order each time.
  • During transactions, don’t allow any user input. Collect it before the transaction begins.
  • Avoid cursors.
  • Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server.
  • Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
  • If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
  • Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
  • If appropriate, use as low of an isolation level as possible for the user connection running the transaction.
  • Consider using bound connections
  • Remember to SET NOCOUNT ON at the beginning of your SQL bataches, stored procedures, triggers to avoid network traffic. This will also reduct the chances of error on linked server.
  • Index should be created on highly selective columns, which are used in JOINS, WHERE and ORDER BY clause.
  • Use Column name in ORDER BY clause instead of numbers.
  • Do not use TEXT or NTEXT if possible. In SQL Server 2005 use VARCHAR(MAX) or NVARCHAR(MAX).
  • Do not use SELECT *, use proper column names to decrease network traffic and fewer locks on table.

Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.

To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. You can configure SQL Server Profiler to extract Deadlock graph events to a single file that contains all Deadlock graph events, or to separate files. This extraction can be done in any of the following ways:

  • At trace configuration time, using the Events Extraction Settings tab. Note that this tab does not appear until you select the Deadlock graph event on the Events Selection tab.
  • Using the Extract SQL Server Events option on the File menu.
  • Individual events can also be extracted and saved by right-clicking a specific event and choosing Extract Event Data.

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.