28.2.08

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

27.2.08

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.


My T-SQL Gallery @code.msdn.microsoft


Created my own T-SQL Gallery in Microsoft site. Do visit the same and share your feedback,

http://code.msdn.microsoft.com/VenkatSQLSample/Thread/List.aspx

Thanks and Regards,
Venkatesan Prabu .J

SQL Server Interview questions - Part 1

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

What is SQL whats its uses and its component ?
The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. It enable us to retrieve the data from based on our exact requirement. We will be given a flexibility to store the data in our own format.


The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables


What is DTS in SQL Server ?
Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue based tool called Data Transformation services. More customization can be achieved using SSIS. A specialized tool used to do such migration works.


What is the difference between SQL and Pl/Sql ?

Straight forward. SQL is a single statement to finish up our work.Considering, I need some data from a particular table. “Select * from table” will fetch the necessary information. Where as I need to do some row by row processing. In that case, we need to go for Procedural Logic / SQL.

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

Difference between primary key and Unique key?
Both constraints will share a common property called uniqueness. The data in the column should be unique. The basic difference is,
· Primary key won’t allow null value. Whereas, unique key will accept null value but only one null value.
· On creating primary key, it will automatically format the data inturn creates clustered index on the table. Whereas, this characteristics is not associated with unique key.
· Only one primary key can be created for the table. Any number of Unique key can be created for the table.

Select Statement in SQL Server

Select Statement in SQL Server

String Functions in sql server

String Functions in sql server
Substring/Len/replace/Ltrim/Rtrim

SQL Server Interview Question - Part 2

What is normalization?

Normalization is the basic concept used in designing a database. Its nothing but, an advise given to the database to have minimal repetition of data, highly structured, highly secured, easy to retrieve. In high level definition, the Process of organizing data into tables is referred to as normalization.


What is a stored procedure:
Stored procedures are precompiled T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. As, its precompiled statement, execution of Stored procedure is compatatively high when compared to an ordinary T-SQL statement.


What is the difference between UNION ALL Statement and UNION ?
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Example for Stored Procedure?
They are three kinds of stored procedures,1.System stored procedure – Start with sp_2. User defined stored procedure – SP created by the user.3. Extended stored procedure – SP used to invoke a process in the external systems.Example for system stored proceduresp_helpdb - Database and its propertiessp_who2 – Gives details about the current user connected to your system. sp_renamedb – Enable you to rename your database


What is a trigger?

Triggers are precompiled statements similar to Stored Procedure. It will automatically invoke for a particular operation. Triggers are basically used to implement business rules.


What is a view?
If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.


What is an Index?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.


What are the types of indexes available with SQL Server?

There are basically two types of indexes that we use with the SQL ServerClustered -

1. It will format the entire table, inturn physically sort the table.

2. Only one clustered index can be created for a table.

3. Data will be located in the leaf level.

4. By default, primary key will create clustered index on the table.

Non-Clustered Index

1. It wont touch the structure of the table.

2. It forms an index table as reference to the exact data.

3. A reference to the data will be located in the leaf level.

4. For a table, we can create 249 non clustered index.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

SQL Interview question

Extent Vs Page?

Pages are low level unit to store the exact data in sql server. Basically, the data will be stored in the mdf, ldf, ndf files. Inturn, pages are logical units available in sql server.The size of the page is 8KB.

Eight consecutive pages will form an extent 8 * 8KB = 64KB.

Thus I/O level operation will be happening at pages level.The pages will hold a template information at the start of each page (header of the page).

They are,

1. page number,

2. page type,

3. the amount of free space on the page,

4. the allocation unit ID of the object that owns the page.

Extents will be classifed into two types,

1. Uniform extents

2. Mixed extents

Uniform Extents:It occupied or used by a single object. Inturn, a single object will hold the entire 8 pages.Mixed

Extents:Mulitple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.

Property of SQL Server :Initally if an object is created, sql server will allocate the object to the mixed extent and once if the size reaches 8 pages and more... immediately, a new uniform extent will be provided for that particular object.

Herecomes, our fragmentation and reindexing concepts.

Best Joke - Enjoy it

Best Joke - Enjoy it