/* Difference b/w DataSet and DataReader */
1.DataSet is a disconnected architecture, while DataReader has live
connection while reading data. If we want to cache data and pass to a
different tier DataSet forms the best choice and it has decent XML support.
2. DataReader provides forward-only and read-only access to data,
while the DataSet object can hold more than one table
(in other words more than one rowset) from the same data source
as well as the relationships between them.
Thus,when application needs to access data from more than one
table "DataSet" forms the best choice.
3. Dataset can persist contents while datareader can not persist contents,
they are forward only.Therefore,If we need to move back while
reading records,“datareader” does not support this functionality.
4. But one of the biggest drawbacks of DataSet is speed.
As “DataSet” carry considerable overhead because of relations,
multiple tables etc speed is slower than “DataReader”. Always try to use “DataReader” wherever possible, as it’s meant specially for speed performance.
/* Difference b/w Stored Procedure and Function */
1. Functions must return a value(scalar,inline table or multi statement table) whereas stored proc may or may not retun a value.
2.Stored Procedures can have input,output parameters for it whereas functions can have only input parameters.
3.Stored Procedure allow select as well as DML statement in it whereas function allow only select statement in it.
4.Functions can be called from SP whereas procedures cannot be called from function.
5.Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6.We can go for transaction management in procedure whereas we can't go in function.
7.Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
8.Procedures are Parsed and Compiled during design time itself whereas Functions are Compiled and executed during run time
9.A UDF returns table variables, while a SPROC can't return a table variable although it can create a table.
10.UDFs can't change the server environment or your operating system environment, while a SPROC can
11.We can pass 1024 parameters in one stored procedure.
/* Difference b/w TRUNCATE and DELETE */
1.TRUNCATE is DDL Command, whereas DELETE is DML Command.
2.TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains,whereas DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
3.TRUNCATE Resets identity of the table whereas DELETE does not reset identity of the table.If you want to retain the identity counter, use DELETE instead like :
4.TRUNCATE cannot be rolled back,whereas DELETE can be rolled back
But if we want to Rollback with Truncate we can use this :
BEGIN TRAN
TRUNCATE TABLE tablename
ROLLBACK TRAN
5.DELETE Can be used with or without a WHERE clause,
whereas Truncate cannot be
6.You cannot use TRUNCATE TABLE on a table referenced by a
FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged,
it cannot activate a trigger,whereas DELETE Activates Triggers.
/*------------------INDEXES---------------------*/
Advantages of Indexes
1.Searching For Records
2.Sorting Records.
3.Grouping Records
4.Maintaining a Unique Column
Index Drawbacks
1.Indexes and Disk Space
2.Indexes and Data Modification
Composite Indexes
A composite index is an index on two or more columns. Both clustered and nonclustered indexes can be composite indexes.
/*Difference B/W Clustered And Non Clustered Indexes */
1.clustered index is a special type of index that
reorders the way records in the table are physically stored.
Therefore table can have only one clustered index.
whereas A nonclustered index is a special type of index
in which the logical order of the index does not match the physical stored order of the rows on disk
2.The leaf nodes of a clustered index contain the data pages.
whereas The leaf node of a nonclustered index does not consist
of the data pages.Instead, the leaf nodes contain index rows.
3.If a PRIMARY KEY constraint is created for a database table and
no clustered index currently exists for that table,
SQL Server automatically creates a clustered index on the primary key.
4.For a clustered index, the database will sort the table's records
according to the column (or columns) specified by the index.
5.A clustered index contains all of the data for a table in the index,
sorted by the index key,just like a phone book is sorted by name
and contains all of the information for the person inline
whereas nonclustered indexes contain only the index key and
a reference to find the data,
6. Much like the index in the back of a book helps us to find keywords
quickly, so the database is able to quickly narrow the number of records
it must examine to a minimum by using the sorted list of UnitPrice
values stored in the index.Thats why Non Clustered Index is slow
7.SQL Server 2000 supports a maximum of 249 non-clustered indexes
per table. However, it’s important to keep in mind that non-clustered
indexes slow down the data modification and insertion process,
so indexes should be kept to a minimum
/* Difference B/w Set and Select */
1.SET is the ANSI standard way of assigning values to variables,
and SELECT is not.
2.Use SELECT to assign values to more than one variable at a time.
SET allows you to assign data to only one variable at a time.
Here's how:
/* Declaring variables */
DECLARE @Variable1 AS int, @Variable2 AS int
/* Initializing two variables at once */
SELECT @Variable1 = 1, @Variable2 = 2
/* The same can be done using SET, but two SET statements are needed */
SET @Variable1 = 1
SET @Variable2 = 2
3.one single SELECT statement can be used to assign values to
multiple variables. This very feature of SELECT makes it a
winner over SET,
when assigning values to multiple variables.
A single SELECT statement assigning values to 3 different variables,
is much faster than 3 different SET statements assigning values to 3 different variables.
In this scenario, using a SELECT is at least twice as fast, compared to SET.
So, the conclusion is, if you have a loop in your stored procedure that
manipulates the values of several variables, and if you want to squeeze
as much performance as possible out of this loop, then do all variable manipulations
in one single SELECT statement (or group the related variables into few SELECT statements)
as shown below:
SELECT @TestVar1 = @TestVar1 + 1, @TestVar2 = @TestVar2 - 1, @CTR = @CTR + 1
/* Difference B/w SQL 2008 and SQL 2005 */
1. SQL2008 has support for additional datatypes: date ; time ; geospatial ; timestamp with internal timezone .
2. In sql server 2005,There is no option to compress backup files, but in sql server 2008,there you find it. storing backup file takes 5 minutes without compression in sqlserver 2005,but it takes only 3 minutes in sql server 2008 for storing backup files with compression.
Therefore, CPU is used to compress the data before it is written to disk,so less data is written to disk.
1.DataSet is a disconnected architecture, while DataReader has live
connection while reading data. If we want to cache data and pass to a
different tier DataSet forms the best choice and it has decent XML support.
2. DataReader provides forward-only and read-only access to data,
while the DataSet object can hold more than one table
(in other words more than one rowset) from the same data source
as well as the relationships between them.
Thus,when application needs to access data from more than one
table "DataSet" forms the best choice.
3. Dataset can persist contents while datareader can not persist contents,
they are forward only.Therefore,If we need to move back while
reading records,“datareader” does not support this functionality.
4. But one of the biggest drawbacks of DataSet is speed.
As “DataSet” carry considerable overhead because of relations,
multiple tables etc speed is slower than “DataReader”. Always try to use “DataReader” wherever possible, as it’s meant specially for speed performance.
/* Difference b/w Stored Procedure and Function */
1. Functions must return a value(scalar,inline table or multi statement table) whereas stored proc may or may not retun a value.
2.Stored Procedures can have input,output parameters for it whereas functions can have only input parameters.
3.Stored Procedure allow select as well as DML statement in it whereas function allow only select statement in it.
4.Functions can be called from SP whereas procedures cannot be called from function.
5.Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6.We can go for transaction management in procedure whereas we can't go in function.
7.Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
8.Procedures are Parsed and Compiled during design time itself whereas Functions are Compiled and executed during run time
9.A UDF returns table variables, while a SPROC can't return a table variable although it can create a table.
10.UDFs can't change the server environment or your operating system environment, while a SPROC can
11.We can pass 1024 parameters in one stored procedure.
/* Difference b/w TRUNCATE and DELETE */
1.TRUNCATE is DDL Command, whereas DELETE is DML Command.
2.TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains,whereas DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
3.TRUNCATE Resets identity of the table whereas DELETE does not reset identity of the table.If you want to retain the identity counter, use DELETE instead like :
4.TRUNCATE cannot be rolled back,whereas DELETE can be rolled back
But if we want to Rollback with Truncate we can use this :
BEGIN TRAN
TRUNCATE TABLE tablename
ROLLBACK TRAN
5.DELETE Can be used with or without a WHERE clause,
whereas Truncate cannot be
6.You cannot use TRUNCATE TABLE on a table referenced by a
FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged,
it cannot activate a trigger,whereas DELETE Activates Triggers.
/*------------------INDEXES---------------------*/
Advantages of Indexes
1.Searching For Records
2.Sorting Records.
3.Grouping Records
4.Maintaining a Unique Column
Index Drawbacks
1.Indexes and Disk Space
2.Indexes and Data Modification
Composite Indexes
A composite index is an index on two or more columns. Both clustered and nonclustered indexes can be composite indexes.
/*Difference B/W Clustered And Non Clustered Indexes */
1.clustered index is a special type of index that
reorders the way records in the table are physically stored.
Therefore table can have only one clustered index.
whereas A nonclustered index is a special type of index
in which the logical order of the index does not match the physical stored order of the rows on disk
2.The leaf nodes of a clustered index contain the data pages.
whereas The leaf node of a nonclustered index does not consist
of the data pages.Instead, the leaf nodes contain index rows.
3.If a PRIMARY KEY constraint is created for a database table and
no clustered index currently exists for that table,
SQL Server automatically creates a clustered index on the primary key.
4.For a clustered index, the database will sort the table's records
according to the column (or columns) specified by the index.
5.A clustered index contains all of the data for a table in the index,
sorted by the index key,just like a phone book is sorted by name
and contains all of the information for the person inline
whereas nonclustered indexes contain only the index key and
a reference to find the data,
6. Much like the index in the back of a book helps us to find keywords
quickly, so the database is able to quickly narrow the number of records
it must examine to a minimum by using the sorted list of UnitPrice
values stored in the index.Thats why Non Clustered Index is slow
7.SQL Server 2000 supports a maximum of 249 non-clustered indexes
per table. However, it’s important to keep in mind that non-clustered
indexes slow down the data modification and insertion process,
so indexes should be kept to a minimum
/* Difference B/w Set and Select */
1.SET is the ANSI standard way of assigning values to variables,
and SELECT is not.
2.Use SELECT to assign values to more than one variable at a time.
SET allows you to assign data to only one variable at a time.
Here's how:
/* Declaring variables */
DECLARE @Variable1 AS int, @Variable2 AS int
/* Initializing two variables at once */
SELECT @Variable1 = 1, @Variable2 = 2
/* The same can be done using SET, but two SET statements are needed */
SET @Variable1 = 1
SET @Variable2 = 2
3.one single SELECT statement can be used to assign values to
multiple variables. This very feature of SELECT makes it a
winner over SET,
when assigning values to multiple variables.
A single SELECT statement assigning values to 3 different variables,
is much faster than 3 different SET statements assigning values to 3 different variables.
In this scenario, using a SELECT is at least twice as fast, compared to SET.
So, the conclusion is, if you have a loop in your stored procedure that
manipulates the values of several variables, and if you want to squeeze
as much performance as possible out of this loop, then do all variable manipulations
in one single SELECT statement (or group the related variables into few SELECT statements)
as shown below:
SELECT @TestVar1 = @TestVar1 + 1, @TestVar2 = @TestVar2 - 1, @CTR = @CTR + 1
/* Difference B/w SQL 2008 and SQL 2005 */
1. SQL2008 has support for additional datatypes: date ; time ; geospatial ; timestamp with internal timezone .
2. In sql server 2005,There is no option to compress backup files, but in sql server 2008,there you find it. storing backup file takes 5 minutes without compression in sqlserver 2005,but it takes only 3 minutes in sql server 2008 for storing backup files with compression.
Therefore, CPU is used to compress the data before it is written to disk,so less data is written to disk.
3. Server 2008 also added CMS which is Central Management Server. It only works with Windows Authentication but it allows you to management multiple SQL Servers at once. If SQL Server systems are popping up like weeds it will appear in the CMS provided that they point to the CMS via SSMS. Its a really cool feature.
4. PBM Policy-Based Management is another added feature introduced with SQL Server 2008. PBM allows you to define and enforce policies for configuring and managing SQL Server across your enterprise. It goes hand-in-hand with CMS.
5. One of my favorite is that fact that Reporting Services no longer requires IIS as it makes direct calls to HTTP.SYS.
4. PBM Policy-Based Management is another added feature introduced with SQL Server 2008. PBM allows you to define and enforce policies for configuring and managing SQL Server across your enterprise. It goes hand-in-hand with CMS.
5. One of my favorite is that fact that Reporting Services no longer requires IIS as it makes direct calls to HTTP.SYS.
Main Difference is over 2000,2005,2008 is
1. XML integration
2. Exception Handling
3. CLR ( common language runtime) integration
4. Varchar(Max) data type
5. DDL Triggers
6. DataBase Mirroring
7. RowNumber function for paging
8. Table fragmentation
9. Full Text Search
10. Easy User Interface
11. Versions
12. Bulk Copy Update
1. XML integration
2. Exception Handling
3. CLR ( common language runtime) integration
4. Varchar(Max) data type
5. DDL Triggers
6. DataBase Mirroring
7. RowNumber function for paging
8. Table fragmentation
9. Full Text Search
10. Easy User Interface
11. Versions
12. Bulk Copy Update
No comments:
Post a Comment