- You can not create Indexes on columns which are of datatype Text, ntext, image etc.
- A view can have a maximum of 1024 columns.
- DBCC TRACESTATUS (-1) displays the Trace flags that are currently on.
- Page level restore can be performed on databases which have either bulk-logged or full recovery model.
- It is recommended that the drives where SQL Server databases are placed should be a NTFS drives.
- Notification Services are discontinued / deprecated in SQL Server 2008.
- A user who needs to only read the data from tables in a database can be assigned with db_datareader database role.
- Sysadmin Fixed Server role is the highest possible server role that can be assigned to a user.
- Replication is one of th ways the tables inside Data Warehouse can be populated with.
- When logging to SQL Server remember that user name is not case sensitive where as the password is case sensitive.
- Clustered index can be created on a column which contains duplicate records.
- SQL Server provides different ways of encrypting data, one of them is Transparent Data Encryption and the other one is Cell level Encryption.
- A Common Table Expression can be used instead of dervied tables and ensures that CTE’s can be reused within the same SQL Server stored procedure / query.
- Between condition in a where clause, includes both the values used as the lower and higher range; example, age between 4 and 14 includes all records with age starting at 4 through 14.
- By default, Order By clause sorts the data in ascending order, to change the data order to descending, you need to use DESC after the column name.
- When submitting a workload in Database Engine Tuning Advisor, you can specify an end time for the workload.
- A Profiler Trace can be saved in a trace file or as a SQL Server table.
- For optimum performance, the number of TempDB files should be equal to the number of prcessorss on the machine.
- It is always suggested to set autogrowth option to true for all database files.
- Only Non-Clustered Indexes can have included columns, clustered Indexes do not allow Included columns.
- There can be multiple data and log files for a single database.
- Database Mirroring is considered as Hot Standby where as Log Shipping is considered as Warm Standby.
- Filegroups are not applicable to log files, they are only applicable for data files..
- Introduced in SQL Server 2008, multiple records can be inserted in table using a single INSERT statement.
- Text based execution plans might not be supported in the future releases of SQL Server.
- Execution plan can be viewed in 3 different formats ( as of this day), they are Text, Graphical and XML based execution plans.
- Side-by-Side Upgrades gives more work for the admin, takes more time to Upgrade and requires additional Server to host the SQL Server or additional Instance besides the existing instance.