Database Query tuning

Database Query tuning

To enhance the performance of the queries, we have to tune the queries,

1. Avoid using "select *" instead specify the needed columns in the select clause.

2. Index the frequent usage columns (which are used in the where clause). The index may be clustered or non clustered based on your business functionality.

3. Specify the table using [servername].[databasename].[owner or schema name].[table name] (This can be priorly used in the transactions involving huge number of tables and huge number of databases.

4. Avoid using cursors in looping the table rows which will make a server roundtrip for each row. Instead, you can opt while loop (transfer the data to temp tables).

5. Avoid using more temporary tables instead you can use table variables.

6. Use set nocount on in the start of your stored procedure. This will avoid frequent updation details about the affected rows to the server.
7. Avoid using views to retrieve the datas instead we can directly hit the tables.

8. Normalisation is the key concept to increase the performace of database.

9. Frequently updating the statistics will surely improve the performance.
10. Reindexing or reorganising the database will improve the performance of the databases.

Stored Procedure Vs User Defined functions in sql server

Stored Procedure:
Its a pre-compiled statements incorporating a set of T-SQL statements.
We can't use it in DML statements.
We can't use SP in Joins.
Stored procedure won't return table variables.
User defined functions:
Its a set of T-SQL statements. Repeated T-SQL statements can be grouped as a User defined functions.
We can use UDF's in DML statements and in joins.
UDF returns table variables.

"Like" Condition in sql server

"Like" condition is used to match the column datas with the necessary condition.
Consider my example i want to check for the names which starts with 'a' then the query resembles as below,
select name from venkatTable where name like 'a%'
Am trying to create a stored procedure to get the count with the column having the word "Ven"

Code Snippet

create procedure ImplementingLike as begin select count(*) from VenkatTable where Name like '%Ven%' end

Temporary Table Vs Table variable

Usage of Temporary table:
If we want to use the table for some other stored procedure or else to be used after my scope of the query. We will prefer temporary table.
Manually, before creating a temporay table we have to delete the existing one
Two types of tables can be created,
1. Local Temporary table
2. Global Temporary table
If we restart the server, temporary tables will get deleted.
Usage of table variable:
If we need table's existence to be restricted with in the scope of the query and no need to be used in future. then we can prefer table variable.
The table variables will automatically get deleted after the scope may be between a begin and end statements.

A new table variable cannot be created if an another table variable exists in the scope.

No need to explicitly drop the table.

Table variables are comparatively too fast in accessing the data because it will reduce the round trip like accessing the temp database to retrieve the data.

Constraints Vs Rules in sql server

Constraints:
Constraints are the condition provided on a column. If the column gets deleted by default, my constraint will get deleted.
Before applying the constraint the column should satisfy the condition.If i try to put a constraint on this column like the column should be unique, then my compiler will indicate an error like its not possible to create the constraint.

Rules:
Rules are nothing but the condition or terms created in the database and the column or table who want to implement that rule can use it.
If your trying to bind the rule with my column it wont think about your past data in the table and will try to abide for the future data