Determining when a procedure has been altered

In Microsoft SQL Server, you can easily retrieve this information from the sys.procedures catalog view. The following query demonstrates this. SELECT [name] ,modify_date ,create_date ,* FROM sys.procedures Of course you can take it a step further by limiting the results to a period of time where you know that no changes should have been made. For example, the following query lists all stored procedures that have been changed since August 1, 2007 (the time I last visited this client). SELECT [name] ,modify_date ,create_date ,* FROM sys.procedures WHERE modify_date > '2007-08-01'   From http://weblogs.sqlteam.com/joew/archive/2007/08/29/60312.aspx

Difference between delete,drop and truncate

Delete Delete remove the Data only, the Table structure remains intact. This is a DML Statement Rollback possible No Commit is performed neither before nor after. (Because it is a DML Statement). They take locks on rows, They generate redo (lots of it) They require segments in the UNDO tablespace. A delete does not relinquish segment space thus a table in which all records have been deleted retains all of its original blocks. A truncate does not move the High Water Mark of the table back to zero, it retains it's original position. Delete deletes the specific rows filtered by where statement. and log is maintained for it. It can activate the triggers. Truncate Truncate remove the Data only, the Table structure remains intact. This is a DDL Statement Rollback not possible (Except in SQL 2005) It issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. (Because it is a DDL Statement) No row-level locks are taken. No redo or rollback is generated. They do not require segments in the UNDO tablespace. All extents bar the initial are de-allocated from the table A truncate moves the High Water Mark of the table back to zero Truncate deletes the page associated with the table so all indexes are reset It does not activate the triggers. Drop Drop permanently removes both the Data as well as the Table Structure. This is a DDL Statement Rollback not possible It issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. (Because it is a DDL Statement) No row-level locks are taken. No redo or rollback is generated. They do not require segments in the UNDO tablespace. All extents bar the initial are de-allocated from the table A truncate moves the High Water Mark of the table back to zero Truncate deletes the page associated with the table so all indexes are reset It does not activate the triggers. FurtherMore.... The Main Difference Between DELETE & TRUNCATE Are :- [1] DELETE - is a DML Command & TRUNCATE - is a DDL Command [2] After DELETE - can rollback the Records & After TRUNATE - cannot rollback the records [3] In DELETE Command you can give the conditions in WHERE Clause & In TRUNCATE you cannot give conditions [4] After using DELETE Command The memory will be occupied till the user does not give ROLLBACK or COMMIT & After using TRUNCATE Command The memory realeased immediately when ever u r using delete statement the trigger is fired.in truncated trigger is not fired. we can mention where clause in delete.in truncate we can't mention. From only4gurus.org

SQL Server 2005 Tutorial and Future of SQL Server

Brief and easy tutorial for SQL Server 2005 Tutorial Lesson 1. SQL Server 2005 Overview Lesson 2. Overview of SQL Server 2005 Architecture Lesson 3. Installing SQL Server 2005 Lesson 4. Transact-SQL Enhancements in SQL Server 2005 Lesson 5. XML integration with SQL Server 2005 Lesson 6. Using the .NET CLR in SQL Server 2005 Lesson 7. Developing Client Applications with ADO .NET 2.0 Lesson 8. Using Service Broker Lesson 9. Using Native HTTP Support Lesson 10. Using Notification Services

SQL2005 Table comparison tool

TableDiff.exe is a table comparison tool that comes with the sql server. It's installed on the server in the: "C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe" Example use: This compares 2 tables in the same database on the same server and creates a new table called DiffsTable that holds the differences: CODE "C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver MyServer1                                                              -sourcedatabase MyDatabase1                                                              -sourcetable MyTable1                                                              -destinationserver MyServer1                                                                -destinationdatabase MyDatabase1                                                              -destinationtable MyTable2                                                              -et DiffsTable This compares 2 tables in the same database on the same server and creates a new table called DiffsTable that holds the differences and creates a T-SQL script file at d:\MyTable1_MyTable2_diff.sql that holds the UPDATE/INSERT/DELETE statements to synchronize the 2 tables: CODE "C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver MyServer1                                                                -sourcedatabase MyDatabase1                                                              -sourcetable MyTable1                                                              -destinationserver MyServer1                                                                -destinationdatabase MyDatabase1                                                              -destinationtable MyTable2                                                              -et DiffsTable                                                              -f d:\MyTable1_MyTable2_diff.sql You can also get simple GUI for this tool: http://www.sqlteam.com/downloads/TableDiffGui.zip

FAQ Interview Questions - Sql Server

SQL Queries FAQ FAQ on Indexes in SQL FAQ on Data Types in SQL Server FAQ on Joins in SQL Server FAQ on Stored Procedure in SQL Server FAQ on Trigger in SQL Server FAQ on Lock in SQL Server FAQ on View in SQL Server FAQ on Transaction in SQL Server SQL Server FAQ on Random Category FAQ on Tools in SQL Server FAQ on Permissions in SQL Server FAQ on Administration in SQL Server