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

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.

"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

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.

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.

Regards,
Venkatesan Prabu. J

Clustered index Vs Non Clustered index

Clustered index is used to sort the data physically. Where as non clustered index will have a reference to the data stored.

Clustered index is too fast where as non clustered index is comparatively slower.

Clustered index will hold huge memory for creation and for working when compared to non clustered index.

Only one clustered index can be created for a table where as 249 non clustered index can be created.

Regards,
Venkatesan Prabu. J

Cursor Vs While loop in sql server

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).

In cursors, we have to create the cursor, open it, use it, close it and deallocate the cursor. So we are facing additional overhead in case of using cursors.

Cursors are heavy weight object holds huge memory when compare to while loop.

In case of any breakage in the mid of looping the cursor we have to take care about the exception handling mechanism to deallocate the cursor. Its an additional overhead for us.

Regards,
Venkatesan Prabu

Know about me??????

I am a Microsoft trained Smart Dotnet candidate,working for HCL Technologies as a senior Dotnet developer and SQL DBA. Completed my BE-CSE in Government engineering college, Tirunelveli.

My Awards :

1. Got SUBJECT MATTER EXPERT (SME) award on SQL Server 2000/2005 from HCL technologies for the year June 2007 - 08.

2. Got Special Contribution award on Dotnet skills from HCL Technologies in the year 2007. Provided training on sql for more than 400 hclt employees and rated as highest internal trainer.

3. I have been awarded as Most Valuable Member (MVM) of Dotnet spider site.

Achievements:

1. Published papers in National conferences,
Topic : Query analyzed image coded cipher for image
Processing (Year 2003)
National conference held at Gobi Arts & Science College, Gobi, Tamil Nadu National conference held at National Engineering college, Kovilpatti.
National conference held at Noorul Islam College, Kanyakumari, Tamil Nadu

2. Presented 25 paper presentations in National level, state level, university symposiums.
Certifications:

Code Snippet

Certified Technology Specialist (MCTS) on sql server 2005 Microsoft Certified Application Developer (MCAD) on Windows                 application/SQL server/Web Services B.B Certified ASP.net professional B.B Certified RDBMS expert B.B Certified Networking expert B.B Certified C# professional Cisco Certified Network Associate (CCNA) E.R Certified Networking professional QAI Certified Software Engineering professional

My MSN group on SQL Server is,

http://groups.msn.com/SqlMSUG/messages.msnw

Planning to write my articles in my own Blog

Oops atlast, I have created my own blog to post my articles.