FORCESEEK HINT is extended in Denali

When SQL Server chooses seek over scan while retrieving record details, it will compare key value with search input, based on comparison result, Query engine will move to appropriate page. If index has multiple columns, if we don't want all key columns to consider, in SQL Server 2012, we can mention the index columns to consider when the index has multiple key columns.

SELECT EmployeeID,EmployeeName,Account

FROM Employees 

WITH (FORCESEEK(Idx_Employee(EmployeeID)))
WHERE EmployeeID = 1024

Online Index Rebuild in Denali

The previous versions of SQL Server never allowed index operations (CREATE, DROP, REBUILD) on ONLINE mode, if the index includes a Large Value Type column (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) etc).

This limitation has been removed in SQL Server 2012 (Denali). The above code runs without an error in a SQL Server 2012 instance.

 

Restrictions on TEMPDB

• Adding filegroups.
• Backing up or restoring the database.
• Changing collation. The default collation is the server collation.
• Changing the database owner. tempdb is owned by dbo.
• Creating a database snapshot.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Running DBCC CHECKALLOC.
• Running DBCC CHECKCATALOG.
• Setting the database to OFFLINE.
• Setting the database or primary filegroup to READ_ONLY.

How the TempDB becomes Full?

• DBCC CHECKDB will perform its work in tempdb
• DBCC DBREINDEX or similar DBCC commands with "Sort in Tempdb" option can make the tempdb full
• Large resultsets involving unions, order by, group by, joins, temp tables etc. can also fill up tempdb
• Any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb
• An ODBC DSN with the option 'Create  temporary stored procedures' set can leave objects in tempdb.

Compatibility Levels

• 60 = SQL Server 6.0
• 65 = SQL Server 6.5
• 70 = SQL Server 7.0
• 80 = SQL Server 2000
• 90 = SQL Server 2005
• 100 = SQL Server 2008
 

Database Role Permissions

Please refer the following table for Database Level Permissions.

Role	DML Operations
db_accessadmin	Select, Update, Delete, Insert Not Working
db_backupoperator	Select, Update, Delete, Insert Not Working
db_datareader	Select Working
db_datawriter	Select Not Working     Update, Delete, Insert Working
db_ddladmin	Select, Update, Delete, Insert Not Working
db_denydatareader	Select, Update, Delete, Insert Not Working
db_denydatareader with db_datareader	Select, Update, Delete, Insert Not Working
db_denydatawriter	Select, Update, Delete, Insert Not Working
db_denydatawriter with db_datawriter	Select, Update, Delete, Insert Not Working
db_dbowner	Select, Update, Delete, Insert Working
db_dbowner with db_denydatareader	Select Not Working Update, Delete, Insert Working
db_dbowner with db_denydatawriter	Select Working  Update, Delete, Insert Not Working
db_securityadmin	Select, Update, Delete, Insert Not Working
sysadmin	Select, Update, Delete, Insert Working
sysadmin with db_denydatareader	Select, Update, Delete, Insert Working
sysadmin with db_denydatawriter	Select, Update, Delete, Insert Working

 

Login Created without Default Database

If you create an SQL Server login and if the login is not mapped to any databases, you will not be able to login into the Database Server. It will throw an error indicating Default Database is missing.