Using OPENROWSET to read large files into SQL Server

Using OPENROWSET to read large files into SQL Server

The BULK option was added to T-SQL in SQL Server 2005 and it persists in SQL Server 2008. When using SQL Server 2000 it was possible to read and write to the file system using the sp_OA_Create and sp_OA_Method extended stored procedures. These XPs continue to work, but are disabled by default because of security concerns and it is a better practice to use more secure capabilities like OPENROWSET when they are available.

http://www.mssqltips.com/tip.asp?tip=1643

Using SQL Server DMVs to Identify Missing Indexes

Using SQL Server DMVs to Identify Missing Indexes
The dynamic management views are:

- sys.dm_db_missing_index_details - Returns detailed information about a missing index
- sys.dm_db_missing_index_group_stats - Returns summary information about missing index groups
- sys.dm_db_missing_index_groups - Returns information about a specific group of missing indexes
- sys.dm_db_missing_index_columns(index_handle) - Returns information about the database table columns that are missing for an index. This is a function and requires the index_handle to be passed.

http://www.mssqltips.com/tip.asp?tip=1634

SQL Server Schema Binding and Indexed Views

Recently while creating an archival job I noticed that job was taking way too long to complete. To help troubleshoot I executed the stored procedure directly and it was also taking a long time to fetch the rows from the view that I was using. Based on the query plan it looked like creating an index on the view may help the issue, so I first looked to see if any indexes were in place for the views, but none were found. The next step was to create an index on the view, but I was presented with this error message "Cannot create index on view, because the view is not schema bound".

http://www.mssqltips.com/tip.asp?tip=1610