How can I change the sa password without knowing the old password?

Use OSQL with the –E option (for authenticated user). If you are an administrator of the OS, you will get in without being prompted for the sa password. Once you are at the OSQL interactive prompt, issue the following command:

EXEC sp_password NULL, 'newpassword', 'username' GO 

How can I attach .MDF file?

It is recommended that you backup your database every day. Please note that at the time of server crash backup files are the main resources for recovery. However attaching .MDF files are always the second option.

The steps to attach an .MDF file are.

You can use Microsoft SQL Server Management Studio Express attach MDF file.

1. Start > Programs > Microsoft SQL Server 2005
2. Open Microsoft SQL Server Management Studio Express
3. Connect the server
4. Right click on Database in Object Explorer
5. Will find a list of options
6. Click on Attach
7. Click on Add
8. Select .Mdf file from browse screen
9. Click ok on browse screen
10. Delete log file using remove button
11. Click Ok and the database should attach.

How to configure SQL Server 2005 to allow remote connections

When you try to connect to an instance of Microsoft SQL Server 2005 from a remote computer, you may receive an error message. This problem may occur when you use any program to connect to SQL Server. For example, you receive the following error message when you use the SQLCMD utility to connect to SQL Server:

Sqlcmd: Error: Microsoft SQL Native Client: 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.

This problem may occur when SQL Server 2005 is not configured to accept remote connections. By default, SQL Server 2005 Express Edition and SQL Server 2005 Developer Edition do not allow remote connections. To configure SQL Server 2005 to allow remote connections, complete all the following steps:

• Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
• Turn on the SQL Server Browser service.
• Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

This article describes how to complete each of these steps.

To enable remote connections on the instance of SQL Server 2005 and to turn on the SQL Server Browser service, use the SQL Server 2005 Surface Area Configuration tool. The Surface Area Configuration tool is installed when you install SQL Server 2005.

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 toConfiguration 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, expandDatabase 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, expandDatabase 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

If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections. For example, SQL Server 2005 Express is installed with a default instance name of Computer Name\SQLEXPRESS. You are only required to enable the SQL Server Browser service one time, regardless of how many instances of SQL Server 2005 you are running. To enable the SQL Server Browser service, follow these steps.

Important These steps may increase your security risk. These steps may also make your computer or your network more vulnerable to attack by malicious users or by malicious software such as viruses. We recommend the process that this article describes to enable programs to operate as they are designed to, or to implement specific program capabilities. Before you make these changes, we recommend that you evaluate the risks that are associated with implementing this process in your particular environment. If you choose to implement this process, take any appropriate additional steps to help protect your system. We recommend that you use this process only if you really require this process.

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point toConfiguration 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.

Note When you run the SQL Server Browser service on a computer, the computer displays the instance names and the connection information for each instance of SQL Server that is running on the computer. This risk can be reduced by not enabling the SQL Server Browser service and by connecting to the instance of SQL Server directly through an assigned TCP port. Connecting directly to an instance of SQL Server through a TCP port is beyond the scope of this article. For more information about the SQL Server Browser server and connecting to an instance of SQL Server, see the following topics in SQL Server Books Online:

• SQL Server Browser Service
• Connecting to the SQL Server Database Engine
• Client Network Configuration

Create exceptions in Windows Firewall

These steps apply to the version of Windows Firewall that is included in Windows XP Service Pack 2 (SP2) and in Windows Server 2003. If you are using a different firewall system, see your firewall documentation for more information.

If you are running a firewall on the computer that is running SQL Server 2005, external connections to SQL Server 2005 will be blocked unless SQL Server 2005 and the SQL Server Browser service can communicate through the firewall. You must create an exception for each instance of SQL Server 2005 that you want to accept remote connections and an exception for the SQL Server Browser service.

SQL Server 2005 uses an instance ID as part of the path when you install its program files. To create an exception for each instance of SQL Server, you must identify the correct instance ID. To obtain an instance ID, follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point toConfiguration 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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.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 Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK.
   
   Note The path may be different depending on where SQL Server 2005 is installed.

Ref:

http://support.microsoft.com

Error 3205 Too Many Backup Devices Specified.

Error Message.

When you are trying to restore a backup created on a Microsoft SQL Server 2005 instance to a different Microsoft SQL Server instance using Microsoft SQL Server Management Studio, you get the following error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Additional information:
Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

Cause.

You are trying to restore a Microsoft SQL Server 2005 database into Microsoft SQL Server 2000 instance.

Backups created with Microsoft SQL Server 2005 cannot be restored to an earlier version of SQL Server⁽¹⁾.

Solution.

1. Make sure you are restoring to the intended SQL Server instance.
2. Run the following command "SELECT @@version" on the target instance of the restore operation, and make sure you are not restoring to a SQL Server 2000 instance.

References.

(1) Books Online 2005 (April 2006) - RESTORE (Transact-SQL)

A Deadlock Occurrence and Resolution

http://www.sqlservercentral.com/articles/deadlock/64315/

We are using MSSQL for our site which a large number of users visit at the same time. Our problem is that we encounter the "deadlock" problem so often when the number of these visitors increase. Is there anything you could recommend us to get rid of this problem?

There are many solutions to this problem. But you have to see the business requirement and I can suggest you some standard solutions.

1. Access objects in the same order.
2. Avoid user interaction in transactions.
3. Keep transactions short and in one batch.
4. Use a low isolation level.
5. Use locking hints like NOLOCK (Check Business Requirement)
6. Use FAST_Forward / Read Only cursors
7. Try to get as minimum rows as possible.
8. Try to minimize the use of triggers
9. Make sure that all heavy-processing logic is in SP only.
10. Check normalization

Uninstalling a SQL Server Clustered Instance

Uninstalling a SQL Server Clustered Instance

Viewing the SQL Server Error Log

View the SQL Server error log to ensure that processes have completed successfully (for example, backup and restore operations, batch commands, or other scripts and processes). This can be helpful to detect any current or potential problem areas, including automatic recovery messages (particularly if an instance of SQL Server has been stopped and restarted), kernel messages, or other server-level error messages.

View the SQL Server error log by using SQL Server Management Studio or any text editor. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.

A new error log is created each time an instance of SQL Server is started, although the sp_cycle_errorlog system stored procedure can be used to cycle the error log files without having to restart the instance of SQL Server. Typically, SQL Server retains backups of the previous six logs and gives the most recent log backup the extension .1, the second most recent the extension .2, and so on. The current error log has no extension.

SQL Express 2005 Setup Failure

You have SQL 2005 SP2 Express Edition or any of earlier version of SQL Express and you decide to apply SP3 to SQL Express edition.

You download the http://www.microsoft.com/downloads/details.aspx?FamilyID=3181842a-4090-4431-acdd-9a1c832e65a6&displaylang=en

And start the setup and suddenly notice the below Error:

The is quite strange as you would say I am not trying to install another Instance of SQL Express but just upgrading the existing instance.

Well, the error is In-appropriate in this case, but you can be sure something is not right. You check the Setup logs present in “C:\Program Files \Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt” and found no major information.

You check the rest of the files present in “Files” folder and in SQLSetup0010_BATALA1_Core(Local).log you finally find below errors.
Error: Action "ReportChainingResults" threw an exception during execution.

DwLaunchMsiExec() returned : 1638

Error Code: 0x80070666 (1638)

Windows Error Text: Another version of this product is already installed. Installation of this version cannot continue. To configure or remove the existing version of this product, use Add/Remove Programs on the Control Panel.

Source File Name: sqlchaining\sqlchainingactions.cpp

Compiler Timestamp: Sat Oct 25 08:47:07 2008

Function Name: sqls::ReportChainingResults::perform

Source Line Number: 3416

Error: Failed to add file :"C:\Program Files (x86)\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0010_BATALA1_.NET Framework 2.0.log" to cab file : "C:\Program Files (x86)\Microsoft SQL Server\90\Setup Bootstrap\LOG\SqlSetup0010.cab" Error Code : 2

Running: UploadDrWatsonLogAction at: 2010/4/11 17:45:32

Message pump returning: 1638

CAUSE:

The previous Setup of SQL Express Edition was not done in a recommended way. This happens if you install the SQL Server by extracting the Express Edition and running SQL*.msi files present in \setup folder directly OR invoking SQL*.msi files through msiexec from commandline.

Installing via above method causes a Successful Setup but with incorrect GUID. Default GUID for SQL Express Edition is EB76B218-8FC5-41DF-9F1F-7FF3E0448383. However if you installed the SQL via above msiexec method then the GUID is 6C428277-232D-4CC2-90ED-A1DCFE7DF64F

Due to Incorrect GUID, When you run the SQL SP3 setup, setup is unable to find the GUID EB76B218-8FC5-41DF-9F1F-7FF3E0448383 and goes in to Perform other configurations Checks like checking number of instance\ names of instance. Here it detects that there is an SQL Express Edition already installed and hence reports the above error.

Also if you try to Uninstall this SQL Express Edition, you will not be able to do so.

POTENTIAL WORKAROUND:

Ideally we should reinstall the SQL Express Edition by taking backup. But you are in trap here because you will also face problem while uninstalling.

The solution remains same i.e to Reinstall the SQL server but as you will not be able to Uninstall SQL Instance directly, you have to run the following command to uninstall the current SQL express edition.

C:\Documents and Settings\user>msiexec /x {6C428277-232D-4CC2-90ED-A1DCFE7DF64F}

The above command should ideally uninstall the SQL Server, however incase you are still facing some issues while uninstalling, you can follow the kb article to remove SQL Instance manually:http://support.microsoft.com/kb/909967

SQL Server 2005 Error Log Management

SQL Server 2005 Error Log Management

SQL Server 2005 connectivity error messages

One of the more visible changes we made in connectivity space for SQL Server 2005 was enhancing error messages reported to the user in case of connection failures. I have seen several cases over the last several months, and in this post I would like to list some of them with the root cause that caused them. The cause may not be the only one triggering a particular error message, and there may be other error messages of interest.

This is a semi-random selection from real cases I investigated. Likely I will post another batch some time in the future, and I would be happy to see replies with additional errors, particularly if the root cause is unclear.

The examples are from various client stacks – ODBC or OLEDB from SQL Native Client or from managed SqlClient.

The exact formatting of the messages will depend on the application you use. Most of the examples below used OSQL, SQLCMD, or SQL Server Management Studio.

• Connecting to a server by the server’s name from SqlClient, the server name is aliased to TCP, the server is up and running but it does not listen on TCP (or is not running at all):

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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

• Local connection from SqlClient; server is not running:

(a) default instance:

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) (Microsoft SQL Server, Error: 2)

(b) named instance:

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: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

• Remote connection from ODBC, Windows Firewall is turned on on the server machine but there is an exception for File and Printer Sharing:

[SQL Native Client]Unable to complete login process due to delay in opening server connection

• A successful TCP connection from SqlClient to the server was broken due to a TCP keep-alive heartbeat failure (typically indicating problems with the underlying network infrastructure):

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Possibly:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

• OBDC connection attempt when server is not ready to process a new local connection, possibly due to overload:

[SQL Native Client]Shared Memory Provider: Timeout error [258].
[SQL Native Client]Login timeout expired
[SQL Native Client]Unable to complete login process due to delay in prelogin response

[SQL Native Client]Shared Memory Provider: Could not open a connection to SQL Server [121].
[SQL Native Client]Login timeout expired
[SQL Native Client]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.

• There is a space after server name in the connection string:

osql -E -S " ,1433"
Login failed for user ''. The user is not associated with a trusted SQL Server connection.

The server ERRORLOG/EventLog shows something similar to:

2005-08-11 12:46:04.29 Logon Error: 17806, Severity: 20, State: 2.
2005-08-11 12:46:04.29 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: ]
2005-08-11 12:46:04.29 Logon Error: 18452, Severity: 14, State: 1.
2005-08-11 12:46:04.29 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: ]

• Remote OLEDB connection using TCP to a server that is blocked by Firewall

HResult 0x274C, Level 16, State 1
TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

Error: Microsoft SQL Native Client : 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..
Error: Microsoft SQL Native Client : Login timeout expired.

• Remote OLEDB connection using TCP to a server that is either not running or does not have TCP/IP protocol enabled for incoming connections (but is not blocked by the Firewall on the server machine):

HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine actively refused it.

Error: Microsoft SQL Native Client : 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..
Error: Microsoft SQL Native Client : Login timeout expired.

Ref: http://blogs.msdn.com

Microsoft SQL Server Error 5030

So, if you are trying to change the collation of a database and getting error 5030, this is much likely because you cannot change the collation of a database when it is in Multi_User mode. In this case, you should try to run the following query.

-- the following line sets the database to "Single User" mode

ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- the following line sets the new collation

ALTER DATABASE DBNAME COLLATE COLLATIONNAME

-- the following line sets the database back to "Multi User" mode

ALTER DATABASE DBNAME SET MULTI_USER

DBNAME: Database name

COLLATIONNAME: New collation’s name. E.g.: Latin1_General_CI_AI

How to Change SA Password or Other MS SQL Server Passwords?

A few days ago, I had a painful exprience that Ilost sa password on myMS SQL Serverdatabase, and I almost took the whole weekend to look for some efficient SA password recoverysolutions. I asked friends, searched on Internet and even bought some books… Fortunately, I finally gotMS SQL Server Password Unlocker and it instantly changed the SA password but no data loss. Besides, I also got a couple of good “recover SA password” solutions in order to save your weekend in case you have lost orforgot MS SQL Server passwords (SA password!)

Option 1 : Use MS SQL Server Password Unlocker to Change SA Password

The most direct, efficient and fastest way to reset SA password is to take use of the third part utility – MS SQL Server Password Unlocker. Just two steps:

Step 1: Access to master.mdf file, which is default at C:Program FilesMicrosoft SQL ServerMSSQLDatamaster.mdf

Step 2: Select SA password and reset SA password

So, Change SA password success!

Option 2 : Change SA Password with Other SQL Server Login

If there is any other SQL Server Login that is a member of sysadmin role, you can log in using that account and reset SA password of SQL Server. Changethe SA password account as described here :

SQL SERVER – Change Password of SA Login Using Management Studio.

Login into SQL Server using Windows Authentication.

In Object Explorer, open Security folder, open Logins folder. Right Click onSA account and go to Properties.

Change SA password, and confirm it. Click OK

Make sure to restart the SQL Server and all its services and test new password by log into system using SA login and new password.

Option 3 : Reset SA Password with Windows Login that is a member of Windows Admin Group

If there is any other Windows Login that is a member of Windows Admin Group, log in using that account. Start SQL Server in Single User Mode as described followings :

SQL SERVER – Start SQL Server Instance in Single User Mode.

There are certain situation when user wants to start SQL Server Engine in“single user” mode from the start up.

To start SQL Server in single user mode is very simple procedure as displayed below.

Go to SQL Server Configuration Manager and click on SQL Server 2005 Services. Click on desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params inStartup Parameters box.

Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server services to take this in effect. Once this is done, now you will be only able to connect SQL Server using sqlcmd.

Make sure to remove newly added params after required work is completed to restart it in multi user mode.

Create a new login and give it sysadmin permission.

Note : If you have SQL Server Agent enabled, it starts before SQL Serverservice. If you have enabled SQL Server in a single user mode, it will connect it first, so it is recommended to turn that off before attempting any of the above options.

Reference : Pinal Dave (http://blog.SQLAuthority.com) Password Unlocker(http://www.passwordunlocker.com)

source: http://blog.passwordunlocker.com

Is there a way to find out when a stored procedure was last updated?

Simple answer is 'No'. The crdate column in the sysobjects table always contains the stored procedure create date, not the last updated date. You can use Profiler to trace ALTER PROC calls to the database, but you can't really afford to run a trace for ever, as it's resource intensive. Here is a simple idea! Whenever you have to alter your stored procedure, first drop it, then recreate it with the updated code. This resets the crdate column of sysobjects table. If you can make sure your developers always follow this plan, then the crdate column of sysobjects will always reflect the last updated date of the stored procedure. For example, if I have to modify a procedure named MyProc, instead of doing "ALTER PROC MyProc", here's what I would do:

- Use sp_helptext to get the current code of MyProc.
- Change the code as needed.
- Run the following code to drop the existing version of MyProc:

IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'MyProc' AND type = 'P' AND USER_NAME(uid) = 'dbo')
BEGIN
DROP PROC dbo.MyProc
END

- Run the updated code to recreate MyProc

There is a much more powerful way out, if you can use Visual Source Safe (VSS). VSS is a version control software, that lets you manage your code. With VSS in place, you will have to maintain all your object creation scripts as script files and check them into VSS. When you have to modify a particular stored procedure, check out that script from VSS, modify it, test it, create the stored procedure, and check the script back into VSS. VSS can show you when a script got modified, by who and a whole lot of other information.

Advantages of using VSS
- You can version control your software, as VSS maintains all your changes as different versions
- You can go back to a previous known good version of your stored procedure, if a developer makes a mistake
- Using the labelling feature, you can revert back to an entire set of scripts at a particular point in time
- You can control access to your source code by configuring permissions to your developers
- By maintaining backups of VSS database, you can secure all your code centrally, instead of worrying about individual script files

.NET Application Development

.NET Application Development

How to Round Up and Round Down with MSSQL

Often, we will need to control how the rounding is done. We can either round up or down down.

The standard thinking is that if the number is .5 and up, it should round up, and if it is less than .5 then round down. But what about .5?

Microsoft SQL Server provides a few ways of controlling the rounding with the functions: ROUND, FLOOR, & CEILING.

Rounding up and rounding down

DECLARE @num1 FLOAT;

DECLARE @num2 FLOAT;

SET @num1 = 123.12345;

SET @num2 = 12345.98765;

SELECT @num1 AS 'Number 1', @num2 AS 'Number 2',

CAST(@num2 AS INTEGER) AS 'Integer', FLOOR(@num2) AS 'Floor/round down',

CEILING(@num2) AS 'Celing/round up'

go

Result:

Number 1 Number 2 Integer Floor/round down Celing/round up

------------ ---------------- ----------- --------------------- ----------------

123.12345 12345.98765 12345 12345 12346

(1 row(s) affected)

Rounding up and rounding down to a specified length

DECLARE @num1 FLOAT;

DECLARE @num2 FLOAT;

SET @num1 = 123.12345;

SET @num2 = 12345.98765;

SELECT @num1 AS 'Number 1',

@num2 AS 'Number 2',

ROUND(@num1,2) AS 'Rounded #1 2 digits',

ROUND(@num2,2) AS 'Rounded #2 2 digits',

ROUND(@num1,-1) AS 'Rounded #2 0 digit',

ROUND(@num2,-3) AS 'Rounded #2 0 digit'

go

Result:

Number 1 Number 2 Rounded #1 2 digits Rounded #2 2 digits Rounded #2 0 digit Rounded #2 0 digit

---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------

123.12345 12345.98765 123.12 12345.99 120 12000

(1 row(s) affected)

Notes:

• Round will round to the specified length.
• The rounding can be positive: it will round the decimal places.
• The rounding can be negative: it will round the integer places.

GROUPBY, HAVING and ORDER BY Usage in SQL Server

I have often seen T-SQL beginners having confusion over the sequence and usage of GroupBy, Having and Order By clause. Here’s a simple example:

I am using the AdventureWorks database.

USE AdventureWorksGOSELECT CustomerID, SUM(TaxAmt) AS CustomerTaxFROM Sales.SalesOrderHeaderGROUP BY CustomerIDHAVING SUM(TaxAmt) > 2000ORDER BY CustomerID

As you can see, we are using HAVING to filter rows based on an aggregate expression. Note that you can include nonaggregate columns in the HAVING clause, however the condition is that these columns must appear in the GROUP BY clause.