SQL Tidbits Mar 2010

March 31st 2010

  • In Replication, a database that is chosen as a publisher can be set to any of the three recovery models.

March 30th 2010

  • A database must necessarily be in Full Recovery Model to be configured with Database Mirroring.

March 29th 2010

  • SQL Server Failover Clustering does not provide a redundancy in case of failure of the shared disks.

March 28th 2010

  • In a Failover Cluster, the shared drives needs to be added to SQL Server dependency before using those drives to hold database files.

March 27th 2010

  • There are 8 types of Data Pages in SQL Server.

March 26th 2010

  • SSMS cannot be used to work with SQL Server 2000 database diagrams, Enterprise Manager should be used instead.

March 25th 2010

  • One of the important things to monitor on a SQL Server – Free space available in the Databases.

March 24th 2010

  • Msdb.dbo.syscategories table contains the list of categories that a SQL Server Job can be categorized.

March 23rd 2010

  • Using a Linked Server, a remote database can be queried using 4 part notation.

March 22nd 2010

  • In SQL Server, the total size of the Index Key columns cannot be greater than 900 bytes.

March 21st 2010

  • Dettach / Attach cannot be performed on System Databases in SQL Server.

March 20th 2010

  • TempDB Database cannot be backed up.

March 19th 2010

  • In Addition to the database options, all the database objects of the Model database are created when a new database is created.

March 18th 2010

  • A SQL Agent Job need not always start from the step 1, it can be configured to start from any step other than the 1st step.

March 17th 2010

  • Using SQL Server Dynamic Management View sys.dm_io_cluster_shared_drives we can query and retrieve the shared drives for the SQL Server in a clustered environment.

March 16th 2010

  • SQL Agent Jobs can have multiple schedules.

March 15th 2010

  • By default every new database that is created has exactly the same database options that are set to Model database.

March 14th 2010

  • MSDB database has a table dbo.backupset which contains information regarding all backups of databases on that instance.

March 13th 2010

  • It is a good practice to name the database objects without spaces, i.e a table with name “EmployeeDetails” is better than a table named “Employee Details”.

March 12th 2010

  • The database snapshot can be created only on the same SQL Server on which the source database exists.

March 11th 2010

  • The database is not available with Detach/Attach procedure.

March 10th 2010

  • Detach / Attach DB is one of the ways to move a database from one server to another server.

March 9th 2010

  • .NET Framework 3.5 is one of the software pre-requisites for Installing SQL Server 2008.

March 8th 2010

  • Online indexing is available only in the Enterprise Edition of SQL Server 2005 / 2008.

March 7th 2010

  • Online indexing is introduced in SQL Server 2005, enables users to access the data from the underlying tables when indexes are created or maintained.

March 6th 2010

  • In a Database Mirroring configuration, the Witness Server need not be of the same SQL Server Edition as the Principal and the Mirror Servers.

March 5th 2010

  • A Maximum of 16 Columns can be used in a composite index key.

March 4th 2010

  • When installing a 64-bit SQL Server, you do not need to enable AWE option in SQL Server.

March 3rd 2010

  • In a 32-bit SQL Server installation, you need to enable AWE option in SQL Server so that additional memory above 3 GB can be utilized by SQL Server.

March 2nd 2010

  • Local temporary tables created using a #tablename are deleted as soon as the connection that creates it is closed or ends.

March 1st 2010

  • Having clause can be used only with a Group by clause.