- Estimated Execution Plan and Actual Execution Plan might be might differ, they are not supposed to be similar.
- Activity Monitor in SQL Server 2008 Management Studio is available on right clicking on the SQL Server Instance name in the object explorer.
- Activity Monitor from a SQL Server 2008 Management Studio does not work on SQL Server 2000 Instance.
- There can be multiple secondary databases and secondary servers for a single Primary Database in Log Shipping.
- In a Log Shipping configuration, the primary database is the actual database on which the Transaction Log backups occur.
- A new SQL Server error log is created each time SQL Server is started.
- The number of SQL Server error logs for a SQL Server can be set to a value between 6 and 99. By default it is set to 6 log files.
- sp_spaceused stored procedure helps to retrieve the size of a table.
- Date and time data types in SQL Server (Smalldatetime and Datetime) includes both a date value along with time, if the time value is not included while inserting, updating the time value is stored as Midnight 12:00:00 AM.
- The only way the new system database, Resource database can be restored is manually, i.e, doing a file copy and paste and replace the existing file.
- Surface are configuration tool is not available in SQL Server 2008, it has been included as a facet in the newly introduced Policy based Management.
- Surface Area Configuration is a tool introduced in SQL Server 2005, which helps in enabling / disabling additional features in SQL Server before continuing to use / utilize the features.
- One of the advantages in Windows Authentication Mode is the is authenticated by windows need not be authenticating to SQL Server separately / additionally.
- Among the SQL Server fixed server roles, sysadmin role has higher privileges, that is a user with sysadmin role permission has the most or max permissions on the SQL Server.
- It is a SQL Server best practices to have the Data files and Log files separate physical drives to ensure recoverability of the databases.
- Primarily Log shipping can be broken into three main tasks, transaction log backup,Copying the backup to destination server and restoring the backup to secondary database.
- Implementing SSIS packages, not always means either a source or destination (in case of data transfer) is a SQL Server. Both source and destination can be non SQL Server such as a flat file source and Oracle destination or a Excel source and Access destination and so on..
- In SQL Server 2005, Activity Monitor can be found under Management node of the Object Explorer in Management Studio.
- In SQL Server 2008, Activity Monitor can be opened by right clicking on the Instance and from the pop up menu choose Activity Monitor.
- In SQL Server 2000, Logins cannot be disabled where as in SQL Server 2005, they can be disabled and enabled when needed.
- Sysadmin fixed server role is the role with maximum set of permissions and members of that role have all rights on a SQL Server Instance.
SELECT COUNT(*) FROM TableNamegives the row count of a table.
- In Database Mirroring, the principal database will continue to work in case of the mirrored database not available.
- Every time SQL Server is restarted, a new file named ERRORLOG is created and SQL Server messages are logged in to that file.
- When a database snapshot is created on a database, the source database cannot be deleted unless the database snapshot is dropped or deleted.
- When a database is in Read-Only, mode only select statements can be executed against it.
- When connected from SQL Server Management Studio, DTS packages of a SQL Server 2000 instance are displayed/shown under Management node -> Legacy -> Data Transformation Services.
- Job activity Monitor under SQL Server Agent Node enables to view the status of the Jobs in SQL Server.
- Schedules in SQL Agent can be enabled and disabled.
- Spatial datatypes, geography and geometry are introduced in SQL Server 2008.