Difference between Truncate and Delete

Truncate	Delete
TRUNCATE is a DDL command	DELETE is a DML command
TRUNCATE TABLE always locks the table and page but not each row	DELETE statement is executed using a row lock, each row in the table is locked for deletion
Cannot use Where Condition	We can specify filters in where clause
It Removes all the data	It deletes specified data if where condition exists.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.	Delete activates a trigger because the operation are logged individually.
Faster in performance wise, because it doesn't keep any logs	Slower than truncate because, it keeps logs
Rollback is not possible	Rollback is possible
Drop all object's statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table	keeps object's statistics and all allocated space. After a DELETE statement is executed, the table can still contain empty pages.
TRUNCATE TABLE removes the data by DE allocating the data pages used to store the table data and records only the page DE allocations in the transaction lo	The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column	DELETE retain the identity
Restrictions on using Truncate Statement 1. Are referenced by a FOREIGN KEY constraint. 2. Participate in an indexed view. 3. Are published by using transactional replication or merge replication.	Delete works at row level, thus row level constrains

Avoid losing Permissions When Subscription is REINITIALIZED

You must have experienced this scenario several times that whenever a snapshot is reinitialized it loses all the granted permissions.
So what can be done to avoid this? Well there are two ways you can handle this scenario:

 Please note: By default, all the objects in the subscription database are dropped and recreated whenever subscription is reinitialized.

1) You can re-apply all the permissions after the re-initialization. I recommend that when you setup the permissions manually, you script out all the object/statement level permissions and store it separately so that you can use it right after you reinitialize subscription.


2) You can configure your subscription so that it does not drop any objects whenever you reinitialize subscription.   
You can use SP_CHANGEARTICLE system stored procedure to configure the value of PRE_CREATION_CMD for the parameter @PROPERTY and a value of 'NONE','DELETE' or 'TRUNCATE' for the parameter @Value.


Also, in the Article Properties dialog box in the destination object section, select a value of "KEEP EXISTING OBJECT UNCHANGED, DELETE DATA"
"IF ARTICLE HAS A ROW FILTER, DELETE ONLY THAT MATCHES THE FILTER"
"TRUNCATE ALL DATA IN THE EXISTING OBJECT"


You much test this in your test environment and go thru the latest BOL for up-to-date information.






Credits to H.S our SQL Hero.

YOU HAVE LOST ACCESS TO SQL SERVER. NOW WHAT?

You are working as a trusted DBA responsible for some extremely important SQL Servers for your company. For the sake of security, you have performed the following steps to secure SQL Servers:

• You have removed any and all built-in administrators account from SQL Server logins
• You have removed all the users (except SA) that were part of SYSADMIN server role (Including any Windows Accounts and/or SQL Server logins)
• You have set the password of SA to something extremely complex which is hard to remember.
• For day-to-day operations on SQL Server, you use your domain user account which has DBO permissions on couple of databases but doesn't have SYSADMIN privileges.

Since you set the SA password to be complex and you have not been using it, you forgot the SA password. You are the only person in the company who would know the SA password and now you have lost the SA password.


What would you do now?
Some quick options I can think of are listed below:

1. You will try to look for the SA password on your computer hard-drive or in your emails (If you stored it in some file which is a bad practice)
2. You will rebuild Master database or reinstall SQL Server and attach all the user databases. However, this could take some time and also doesn't guarantee that all your logins, users, permissions and server configurations will be recovered unless you plan to restore the Master database from an old backup. However, as you don't remember the SA password, restoring the Master database will not help you and you are back to square one.
3. You will call up Microsoft PSS

You are now running out of options. What would you do?


There's a way with which you can gain SYSADMIN access to your SQL Server. However, that would mean your Windows account will need to be a member of the local administrators group.


SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges.


Here are the steps you will need to perform:

1. Start the SQL Server instance using single user mode (or minimal configuration which will also put SQL Server in single user mode)
   
   From the command prompt type: SQLServr.Exe –m (or SQLServr.exe –f)

Note: If the Binn folder is not in your environmental path, you'll need to navigate to the Binn folder.

(Usually the Binn folder is located at: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn)

1. Once SQL Server service has been started in single user mode or with minimal configuration, you can now use the SQLCMD command from command prompt to connect to SQL Server and perform the following operations to add yourself back as an Admin on SQL Server instance.

SQLCMD –S <Server_Name\Instance_Name>

You will now be logged in to SQL Server as an Admin.

1. Once you are logged into the SQL Server using SQLCMD, issue the following commands to create a new account or add an existing login to SYSADMIN server role. 

To create a new login and add that login to SYSADMIN server role:

1> CREATE LOGIN '<Login_Name>' with PASSWORD='<Password>'

2> go

1> SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN'

2>go

To add an existing login to SYSADMIN server role, execute the following:

1. SP_ADDSRVROLEMEMBER '<LOGIN_NAME>','SYSADMIN'

The above operation will take care of granting SYSADMIN privileges to an existing login or to a new login.

1. Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need –f or –m)

Credits : Saleem Hakani

SQL SERVER Denali CTP3 is Coming Soon!!!

Register below for the latest update on Denali ....

http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx

SQL SERVER BLACK BOX !!!

Ever wanted to find out what was happening when SQL Server crashed? Your investigation should also include reviewing the SQL Server activities much like what profiler would do. SQL Server 2008 (Including SQL2K5) out of the box comes with a default trace enabled. This trace keeps track of configuration changes, process level information and other information that can be very helpful for troubleshooting SQL Server related issues.


The default trace file can be opened and examined by launching SQL Server Profiler and by loading the log.trc file from (\Program Files\Microsoft SQL Server\<Instance_Name>\MSSQL\Log\) location or by querying it with Transact-SQL using the fn_trace_gettable system function.


Alternatively, you can query the trace file using the following T-SQL statement:
SELECT
*
FROM
fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log.trc',
default);
-- Make sure you point to the right drive instead of C:\ drive.


Note: By default this trace is on
and can't be stopped by using the SP_Trace_SetStatus system stored procedure. Instead, you will need to stop the default trace by using SP_Configure option. *It is not a good idea to stop this trace.


Some of the information provided by the default trace includes:
Configuration change history
Schema Changes History
Memory Consumption
All Blocking Transactions
Top Sessions
Top Queries by Average CPU time
Top Queries by Average IO,
Etc.

This data can also be accessed from the Admin reports (from SQL Server Management Studio right-click the registered server and select "Reports")

Credits: Saleem Hakani

SAVE TIME CONNECTING TO SQL SERVER USING MANAGEMENT STUDIO

You are a developer responsible for working with a specific database. Every time you open up SQL Server Management Studio, you have to provide the name of the SQL Server Instance, Authentication Type, User Name and Password and if you are planning on working with a specific database, you will need to click on Options and then select the default database. Once you are logged in, you will then need to click on New Query to open the query editor.


Performing the above operations on a frequent basis to connect to SQL Server using management studio could time consuming. Wouldn't it be nice if you could simply click on SQL Server Management Studio and it logs you in and also connects to the database you usually work with and open up query editor by default for you?


You can now change the behavior of SQL Server Management Studio to make it work the way you want and here's how you can do that. Please note that there are two changes that need to be done:

1. Update the SSMS.EXE to include the connection parameters in the Shortcut link:
   
    

CONNECT TO SQL SERVER THRU MANAGEMENT STUDIO WITH DEFAULT VALUES

Syntax: SSMS.EXE –S <ServerName> -d <Database_Name> -E Example: SSMS.EXE –S TK2SAMSQL01 –d MSSOLVE –E

You can update the shortcut link of SQL Server Management Studio from Start->Programs->SQL Server 2008-> SQL Server Management Studio link. (Simply right click on the link and select properties to update the link)

1. Configure SQL Server Management Studio to open Object Explorer and Query Editor by default:
   
    

OPEN QUERY EDITOR BY DEFAULT WHEN MANAGEMENT STUDIO IS LAUNCHED
STEPS	ACTION
1	SELECT TOOLS FROM SQL SERVER MANAGEMENT STUDIO MENU
2	SELECT OPTIONS FROM THE TOOLS MENU
3	SELECT GENERAL FROM THE ENVIRONMENT FOLDER
4	CLICK ON THE DROP DOWN LIST OF "AT STARTUP" OPTION
5	FROM THE DROP DOWN, SELECT "OPEN OBJECT EXPLORER AND NEW QUERY"
6	CLICK ON OK AND CLOSE AND RESTART SQL SERVER MANAGEMENT STUDIO

After both the above changes are implemented, SQL Server Management Studio will need to be closed. When you launch SQL Server Management after making the above changes, it will bypass the security dialog box and will connect you straight to the database you want to work with and will also launch query editor along with object explorer. This could potentially save the time of a developer by not having to go thru multiple manual steps.

SQL SERVER MANAGEMENT STUDIO CAN OPEN UP 4 DIFFERENT TYPES OF WINDOWS AT STARTUP

OBJECT EXPLORER (This is the default window) NEW QUERY WINDOW OBJECT EXPLORER AND QUERY EDITOR (You should select this for this example) EMPTY ENVIRONMENT

Important: You will need to close SQL Server Management studio and launch it again for the above changes to take effect.


Credits : Saleem Hakani

SQL Server 2008 Evaluation Edition Expired?

Many folks think that it is not possible to upgrade from an Evaluation Edition of SQL Server 2008 to Enterprise/Standard/Developer Edition. They think that the only way is to uninstall the Evaluation Edition and then install the desired Edition of SQL Server. Well, that is simply not true. You can very well upgrade from Evaluation Edition to any licensed edition by running the setup file and selecting Edition Upgrade option under Maintenance. You might wonder what if the evaluation period has expired. No problem, there is a work around: Just change the system date back to a date before the date of expiration of the software, and then do the upgrade. However, please note that SQL Server Management Studio and SQL Server Profiler tools will still complain with the following error message after the evaluation period of the original edition expires.

Evaluation period has expired. For information on how to upgrade your evaluation software please go to http://www.microsoft.com/sql/howtobuy (http://www.microsoft.com/sql/howtobuy)

There are two ways to fix the above error message:
1. Install Service Pack 1 before upgrading SQL Server 2008 evaluation edition to any licensed edition.

2. Modify Registry. Go to Start->Run->Regedit. In the Registry Editor, select the following key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\ConfigurationState and change the value of the Common Files REG_DWORD to 3.

After completing the registry changes, run the edition upgrade and you are done. For a list of supported version and Edition upgrades, check out the following:
http://msdn.microsoft.com/en-us/library/ms143393.aspx