SQL Tidbits Feb 2010

February 28th 2010

  • Integration Services can be installed only once on a Server and is irrespective of the individual instances on that server.

February 27th 2010

  • Beginning from SQL Server 2005, exception handling can be performed using TRY / CATCH.

February 26th 2010

  • @@ERROR is used in error handling in SQL Server 2000.

February 25th 2010

  • When using Union / Union All, Order by can be used only at the end of the SQL statement.

February 24th 2010

  • From Keyword is an optional in a Delete Statement.

February 23rd 2010

  • SQL Server cannot be reinstalled on a compressed drive. (Contributed by Arsalan Khan)

February 22nd 2010

  • Default constraint will be enforced when a record is inserted into a table, when no value for that column is passed, the default value is inserted into that column.

February 21st 2010

  • Default constraint can be applied on any columns except Identity columns.

February 20th 2010

  • Into Keyword is optional in an Insert Statement.

February 19th 2010

  • Enterprise Evaluation Edition is free to download and expires after a period of 180 days of Installation, has all the features available in an Enterprise Edition.

February 18th 2010

  • Page Life Expectancy is a counter that indicates the average value (in seconds) the data pages are stored in the memory.

February 17th 2010

  • It is a good practice to name the database backups in such a way that it is easier to know what type of backup it is, which database does it belong to, the date and time it was performed such as “Testdb_Full_02172010_5PM.Bak“.

February 16th 2010

  • When restoring a database from database snapshot, only the snapshot from which it is being restored should exist, all the other snapshots needs to be dropped.

February 15th 2010

  • Having multiple data files for a large database has a performance benefit.

February 14th 2010

  • The Maximum Degree of Parallelism will take effect without restarting the SQL Server.

February 13th 2010

  • Non-Clustered is the default Index type created when the type of index is not mentioned in the create Index statement.

February 12th 2010

  • A Publisher is the source server / system in a Replication setup.

February 11th 2010

  • SSIS Packages can be deployed inside SQL Server or as File System packages.

February 10th 2010

  • Wide Tables are special kind of tables which can contain a maximum of 30,000 columns.

February 9th 2010

  • Triggers are also set of T-SQL code as stored procedures, but triggers are executed when an event occurs on which that trigger is defined they cannot be executed manually.

February 8th 2010

  • Stored procedure is piece of T-SQL code that is stored on the SQL Server and can be executed when needed by executing the stored procedure.

February 7th 2010

  • Inner Join is the default type of join.

February 6th 2010

  • Since differential backups contains all the changes since the last Full backup, they tend grow in size with the time.

February 5th 2010

  • When Authentication mode of an Instance is changed, the Instance needs to be restarted to take effect.

February 4th 2010

  • Backups of a higher version cannot be restored on a lower version of SQL Server, i.e. SQL Server 2008 Database backup cannot be restored on SQL Server 2005 or 2000 etc.

February 3rd 2010

  • A Table can have only 1 Primary Key assigned which can be on a single column or multiple columns.

February 2nd 2010

  • A maximum of 25 instances can be installed in a failover cluster in SQL Server 2005 / 2008.

February 1st 2010

  • A maximum of 32,767 files and 32,767 filegroups can be specified for each database.