SQL Server – Few best practices for T-SQL Coding
Best practices for T-SQL Coding
1) Use of in-line and block comments in T-SQL code will not affect the performance. In fact It will enhance your productivity when you or others come back to the code and try to modify it.
2) Avoid SQL Server cursors, it generally use a lot of SQL Server resources and reduce the performance.
3) Carefully evaluate that the SELECTE statement needs DISTINCT clause or not. Try to avoid it whenever possible because using DISTINCT may reduce the performance of the query.
4) Avoid using * in SELECT statement, make sure you retrieve only those columns which are required.
5) Always try to include WHERE clause in SELECT statement to filter the number of row returned.
6) When your application allows users to run queries and you are unable to prevent users from returning many rows of data then consider using TOP operator in the SELECT statement. In SQL Server 2012 new T-SQL feature OFFSET and FETCH have been introduce which can be used to avoid getting hundreds or thousands of records in the application. Making result set smaller is better for performance.
7) Try to avoid search arguments in the WHERE clause, such as IS NULL, <>, !=, NOT, NOT EXISTS, NOT IN, NOT LIKE, and LIKE. These operators can’t take the advantage of index which will lead the table scan or index scan.
SQL Server – Best Practices for using Service Accounts
Filed under SQL Server Best Practices
Securing SQL Server is one of the top priorities of SQL Server DBA. It is very important for DBA’s and system admin to make sure that SQL Server is HIGHLY SECURED and it is not exposed to users who don’t need access to SQL Server. Only valid and required users MUST be granted required permissions.
Apart from configuring the right security/permissions for SQL Server users/logins, it is also very important to select the right account to run SQL Server services.
When to use Domain User Account?
When your SQL Server interacts with other servers, services or resources on the network (ex: Files Shares, etc.) or when uses linked servers to connect to other SQL Server on the network, then a DBA must use a low privileged domain user account for running SQL Server service. Domain user account is the most recommended account for setting up SQL Server service that interact with others servers on the network. The advantage of using domain user account is that the account is controlled by Windows active directory therefore, domain level policy on the accounts apply to SQL Server account as well.
When to use Network Service Account
Never should a DBA use Network Service Account for running SQL Server services. Network Service accounts are shared with the other services running on the local computers. Network Service Account is a built-in account that has more access to server resources and objects than users accounts of local user groups.
Any SQL Server service that runs on Network Service Account, can access network resources by using the credentials of the computer account. This account shows up a (NETAUTHORITY\NETWORK SERVICE) when configured SQL Server Service.
When to use Local User Account
When SQL Server does not interact with other servers or resources on the network (ex: files, shares, linked servers, etc.) then DBA can use a low privileged local server account for running SQL Server Service.
Recommendation: DBA must not require using the local user account with administrative privileges to run SQL Server Service.
When to use Local System Account
Never ever should a DBA use local system account for sunning SQL Server Service. Local System Account has more permissions than a DBA would think. It is a very high-privileged built-in account created by Windows O/S.
Local System Account has extensive privileges on the entire local system and acts as a computer on your company’s network. This account shows up as (NT AUTHORITY\SYSTEM) when configuring SQL Server service.