SQL Server 2008 R2 November CTP

Hello,

November CTP of SQL Server 2008 R2 is out. If you are a technet or MSDN subscriber then its available now. If you don't have a subscription to any of these then you can download it from 11th Nov 2009.

Major changes from August CTP are:

• PowerPivot for SharePoint to support the hosting and management of PowerPivot applications in SharePoint
• Application and Multi-Server Management for enrolling, gaining insights and managing up to 25 instances
• Master Data Services for data consistency across heterogeneous systems
• Data Compression now enabled with UCS-2 Unicode support

SQL Server 2008 R2 delivers higher mission-critical scale, more efficient IT, and expanded reporting and analytics through self-service business intelligence. SQL Server 2008 R2 introduces two new premium editions to meet the needs of large scale datacenters and data warehouses.

 SQL Server 2008 R2 Datacenter

• SQL Server 2008 R2 Parallel Data Warehouse

 Following capabilities are new to SQL Server 2008 R2 Standard:

• Backup Compression to reduce data backups by upto 60% and help reduce time spent on backups *
• Can be managed instance for Application and Multi-Server Management capabilities

The PowerPivot for Excel 2010 component of SQL Server 2008 R2 (formerly known as "Gemini") will be available in the November CTP.

PowerPivot for Excel 2010 (formerly known as "Gemini") is a data analysis tool that delivers unmatched computational power directly within the application users already know and love—Microsoft Excel. It provides users with the ability to analyze mass quantities of data and IT departments with the capability to monitor and manage how users collaborate by integrating seamlessly with Microsoft SharePoint Server 2010 and Microsoft SQL Server 2008 R2.

You can sign up to get Notification on PowerPivot for Excel 2010 here:

https://profile.microsoft.com/RegSysProfileCenter/wizard.aspx?wizid=27d33a54-f9f5-4357-b652-6f03ba8a4d23&lcid=1033

Download it & start using it.

Regards.........

Deleting Data in SQL Server using TRUNCATE vs DELETE commands

Hi,

Today we will discuss various ways to delete rows from table.

There are two main keywords used for deleting data from a table: TRUNCATE and DELETE. Although both achieves the same result, the method employed for each vastly differ. There are advantages, limitations, and consequences of each that you should consider when deciding which method to use.

TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation. Records removed by the TRUNCATE TABLE statement cannot be restored unless you use TRANSACTION.

--Create a simple table with 1 identity column
create table ttt
(id int identity)

insert into ttt --Insert 100 rows into table
default values
go 100

select * from ttt --check number of rows from the table

--Now run this in a transaction and roll back and check if you are able to retrieve your data or no.
begin tran
truncate table ttt
rollback

select * from ttt


You cannot specify a WHERE clause in a TRUNCATE TABLE statement-it is all or nothing. The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas.

In addition, TRUNCATE TABLE statements cannot be used for tables involved in replication or log shipping, since both depend on the transaction log to keep remote databases consistent.
TRUNCATE TABLE cannot used be used when a foreign key references the table to be truncated, since TRUNCATE statements do not fire triggers. This could result in inconsistent data because ON DELETE/UPDATE triggers would not fire. If all table rows need to be deleted and there is a foreign key referencing the table, you must drop the index and recreate it. If a TRUNCATE TABLE statement is issued against a table that has foreign key references, the following error is returned:

Cannot truncate table 'TableName'

DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Although this consumes more database resources and locks, these transactions can be rolled back if necessary. You can also specify a WHERE clause to narrow down the rows to be deleted. When you delete a large number of rows using a DELETE FROM statement, the table may hang on to the empty pages requiring manual release using DBCC SHRINKDATABASE (db_name).

When large tables require that all records be deleted and TRUNCATE TABLE cannot be used, the following statements can be used to achieve the same result as TRUNCATE TABLE:

DELETE from "table_name"
DBCC CHECKIDENT("table_name", RESEED, "reseed_value")

CHECKIDENT checks the current identity value for the specified table and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

DBCC CHECKIDENT("ttt", NORESEED)

This will return the current last value of Identity in table.
DBCC CHECKIDENT("table_name", RESEED, "reseed_value")
This will reset the Identity column value to start from "reseed_value", if you have a unique constraint, then this will fail as it will start from 1, if you seed was 1.

DBCC CHECKIDENT("ttt", RESEED, 0)

Happy SQL Coding..................................................

Using the NOWAIT option with the SQL Server RAISERROR statement

Hi,

Today we will discuss scenario where we execute sql statements or RAISERROR which include PRINT statements in between, but we really don't get to see the output of PRINT statement, it always come after sometime.

Use WITH NOWAIT clause of the RAISERROR statement. It may be surprising but using RAISERROR doesn’t require that there is an error condition. If the severity level given to RAISERROR is 0 through 10 SQL Server treats the RAISERROR as a plain message and not an error at all. Execution continues with the next statement, even if there is a TRY/CATCH block or if SET XACT_ABORT is ON. Use the combination of a RAISERROR severity of 0 to 10 and the WITH NOWAIT clause for a statement that sends output to the Messages windows immediately, like this one:

RAISERROR ('Not an error message!', 0, 1) WITH NOWAIT

When query results are being sent to a grid, these panes are shown as soon as command execution begins and the Messages window is hidden unless there are no results. There are two ways to address this. One option is to send the results to text using either the menu or CTRL+T. The other option is to allow results to go to the grid and click on the messages window or use the SSMS menu command Window/Next Pane, which by default is tied to the F6 key or Shift+F6 in Query Analyzer.

Once you've sent Results to Text with CTRL+T try this script:

DECLARE @time char(8)
PRINT '1- Before anything else ' + convert (varchar(30), getdate(), 8)
SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('2- RAISERROR before W/O NOWAIT %s', 0, 1, @time)
WAITFOR DELAY '00:00:05'
PRINT '3- After the first delay ' + convert (varchar(30), getdate(), 8)
SET @time = convert (varchar(30), getdate(), 8)
RAISERROR ('4- RAISERROR with NOWAIT %s', 0, 1, @time) WITH NOWAIT
WAITFOR DELAY '00:00:10'
PRINT '5- After the second delay ' + convert (varchar(30), getdate(), 8)

What you’ll see is something like this result:

1- Before anything else 15:17:58
2- RAISERROR before W/O NOWAIT 15:17:58
3- After the first delay 15:18:03
4- RAISERROR with NOWAIT 15:18:03
These results show up in 5 seconds. Neither the PRINT statements on lines 1 and 3 nor the RAISERROR on line 2 show up before the RAISERROR WITH NOWAIT on line 4 is executed. Then after another 10 seconds you’ll see:

5- After the second delay 15:18:13

As you can see from the time stamps, lines 1 and 2 were executed at 15:17:58 then there was a 5 second delay until 15:18:03 when lines 3 and 4 are executed and appear. It's the NOWAIT clause on the RAISERROR that produces line 4 that forces lines 1 through 4 to the message window.

One way to make the NOWAIT clause convenient is to write it into a simple stored procedure like this.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[ns_log_nowait]
@Msg nvarchar(2047)
/* Send a message to the caller so that it's available  immediately.
exec dbo.ns_log_nowait 'This is the immediate message'
waitfor delay '00:00:10'
print 'This is after a 10 second delay'
compare this to print 'before the call'
raiserror ('this is the msg', 0, 1)
waitfor delay '00:00:10'
print 'This is after a 10 second delay'
***********************************************************/
AS
RAISERROR (@Msg, 0, 1) WITH NOWAIT
go
GRANT EXECUTE on [dbo].[ns_log_nowait] to PUBLIC
go

RAISERROR with severity under 11 isn’t treated as an error in a TRY/CATCH construct and NOWAIT has no effect on this behavior. To see that RAISERROR with severity 0 is treated like a print statement try this script in SSMS:

DECLARE @time char(8)

BEGIN TRY
PRINT '1 In the TRY block ' + convert (varchar(30), getdate(), 8)
SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('2 RAISERROR with NOWAIT %s', 0 , 1, @time) WITH NOWAIT
WAITFOR DELAY '00:00:05'
END TRY
BEGIN CATCH
PRINT '3 In the CATCH block ' + convert (varchar(30), getdate(), 8)
PRINT '3A Error Number = ' +convert (VARCHAR, ERROR_NUMBER()) + ' Severity = ' + convert (varchar, ERROR_SEVERITY())+ ' Msg = ''' + ERROR_MESSAGE() + ''''
END CATCH
PRINT '4 After the CATCH block ' + convert (varchar(30), getdate(), 8)

The output from this script is shown here:

1 In the TRY block 15:22:49

2 RAISERROR with NOWAIT 15:22:49
4 After the CATCH block 15:22:54

Notice that lines 3 and 3A, in the CATCH block aren't reached even though a RAISERROR was executed. However, if you change the severity on the RAISERROR to 11, the statement is treated as an error. Here’s the script again with only the severity of the RAISERROR changed:

DECLARE @time char(8)

BEGIN TRY
PRINT '1 In the TRY block ' + convert (varchar(30), getdate(), 8)
SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('2 RAISERROR with NOWAIT %s', 11, 1, @time) WITH NOWAIT
WAITFOR DELAY '00:00:05'
END TRY
BEGIN CATCH
PRINT '3 In the CATCH block ' + convert (varchar(30), getdate(), 8)
PRINT '3A Error Number = ' +convert (VARCHAR, ERROR_NUMBER())+ ' Severity = ' + convert (varchar, ERROR_SEVERITY())+ ' Msg = ''' + ERROR_MESSAGE() + ''''
END CATCH
PRINT '4 PRINT after the CATCH block ' + convert (varchar(30), getdate(), 8)
 
Which produces this output:

1 PRINT in the TRY block 15:24:33

3 PRINT in the CATCH block 15:24:33
3A Error Number = 50000 Severity = 11 Msg = '2 RAISERROR with NOWAIT 15:24:33'
4 PRINT after the CATCH block 15:24:33

Note that the RAISERROR statement is handled by the TRY/CATCH and doesn't produce message output on its own. Instead the error is handled by the CATCH block, which has code that prints the message on line 3A along with the severity.

Happy SQL Coding.......

Do let me know if you have any comments/feedback.......

SQL Server Questions

Hi,

Today we will take up some general questions.

Question. What are the SQL Server system databases and can you outline the general functionality of each database?

Ans:
Master - Database responsible for SQL Server instance related data. 
Resource - Database responsible for SQL Server system objects. This database was introduced in SQL Server 2005 and is intended to ease the upgrade and rollback of SQL Server system objects.
Model - Template database for the creation of new user defined databases.
MSDB - Database responsible for SQL Server Agent related data such as Jobs, Alerts, Operators, Backup, restore, database diagrams etc.
TempDB - Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
Distribution - Database responsible for managing replicated data. This database could reside on the publisher or subscriber.

Question. Is it a good idea to create objects in the system databases?

Ans:
Logically objects should not be created in the system databases. It is a best practice to create a separate database for user defined objects that would be used instance wide or create the objects in each of the needed databases. From a DBA perspective, it is a common practice to create a dedicated DBA database in order to perform custom administrative tasks.
If objects are needed for future user defined databases those can be created in the Model database.

Question. Does Reporting Services create system databases during the installation process?

Ans.
Yes. It does create following databases:

• ReportServer - Stores the reports related data.
• ReportServerTempDB - Temporary database storage for Reporting Services.

Question. What are the typical objects that are created in the TempDB database?

Ans.
following objects use TempDB database:

• Temporary tables (#temptable or ##temptale)
• Table variables
• Cursors
• Work tables
• Row versioning
• Create or rebuild indexes sorted in TempDB

Question. Does size of the TempDB database really make a difference and how should TempDB be sized?

Ans.
In situations where temporary objects are created in T-SQL code (i.e. temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc.) the TempDB database makes a significant difference in overall performance. In these situations the database should be sized appropriately and moved to a separate disk drive in order to support the I/O requests. If not, the default location and size may be appriopriate.

Question. Explain relationship between logins and users in the system and user databases.

Ans.
Logins - All logins reside in the master database
Users - All users reside in the master database, other system databases and in the user defined databases.

Question. What's the differences in restoring the Master database versus a user defined database?

Ans.
In order to restore the Master database, SQL Server instance must be in single user mode. After the Master database is restored, the SQL Server instance restarts.

When a user defined database is restored, it has no impact on other databases.

Question. What's Resource database?

Ans.
The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database. The Resource database was introduced in SQL Server 2005.

Question. What are some of the tools that you use to troubleshoot any SQL Server issue?

Ans.

• SQL Server Error Log
• SQL Server Agent Error Log
• Windows Application Event Log
• SQL Server Integration Services Logs
• Custom Application\Process Logs

Question.How can you help to prevent a SQL Injection attack?

Ans.

• Review firewall and web server logs for potential threats.
• Prevent particular strings from passing through the firewall.
• Prevent IP traffic from particular web sites.
• Implement technology to scan your public facing web sites for potential issues.

Question. What is an identity? What is the value? How can you capture the last identity value per column?

Ans.
An identity is a property of a column where we specify seed and increment values. The seed is the starting value for the column and increment is the value by which the identity grows. In most cases seed and increment values are 1 wherein the initial value is 1 and the identity grows by 1.

The value of the identity column is managed by relational engine. SQL Server provides three different functions for capturing the last generated identity value on a table that contains an identity column:
@@IDENTITY
SCOPE_IDENTITY()
IDENT_CURRENT(‘tablename’)

Question. What are the different indexing options available and what columns do we typically index? What is the value of indexing columns?

Ans.

SQL Server offers two types of indexes, clustered and non-clustered. A clustered index is an index that stores the actual data and a non-clustered index is just a pointer to the data. A table can only have one Clustered index and up to 249 Non-Clustered Indexes. If a table does not have a clustered index it is referred to as a Heap.

The primary reason indexes are built is to provide faster data access to the specific data your query is trying to retrieve. This could be either a clustered or non-clustered index. Without having an index SQL Server would need to read through all of the data in order to find the rows that satisfy the query. In the query plan the difference would be an Index Seek vs a Table Scan as well as some other operations depending on the data selected.
Indexes are typically created on these columns:

• Primary keys
• Foreign keys
• Columns in WHERE, GROUP BY, ORDER BY, etc.
• Single value, range of values, etc.

Question.How you will tuning a SQL Server query? How to identify the query causing the issue?

Ans.
Review the query plan by issuing SHOWPLAN_TEXT, SHOWPLAN_ALL, Graphical Query Plan or sys.dm_exec_query_stats.

Review the individual query components to determine which components of the query have the highest cost.
Outline options to improve the query such as moving from cursor based logic to set based logic or vice versa, changing the JOIN order, WHERE clause or ORDER BY clause, adding indexes, removing indexes, creating covering indexes, updating statistics, removing use of functions in where clause etc.
Test the options to determine the associated performance improvement.

Question. What are the options in SQL Server to rebuild indexes?

Ans.

• CREATE INDEX with DROP_EXISTING
• DROP INDEX and CREATE INDEX
• ALTER INDEX

Question.What happens when we add a column in the middle of a table (Employee) in Management Studio?

Ans.
Management Studio creates a temporary table called dbo.Tmp_Employee with the new structure.
If there is data in the original table dbo.Employee this data is inserted into the new temp table dbo.Tmp_Employee (now you have two sets of the same data).
The original table dbo.Employee is dropped.
The new table dbo.Tmp_Employee is renamed to dbo.Employee.
If the table has indexes all of the indexes are recreated.

Question.How do you backup Analysis Services databases?

Ans.
Create XML statement to backup the Analysis Services databases, then create a SQL Server Agent Job to perform the task on a daily basis.

Question.What is a common use case for the OPENROWSET function?

Ans.
Import from Excel to SQL Server or vice-versa.

Question.What is the difference between a deterministic and non deterministic function?

Ans.
Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database.
Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.

Question.What is the Guest user account? What login is it mapped to? Can we drop it?

Ans.
The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object. It is not mapped directly to any login, but can be used by any login. Depending on your security needs, it may make sense to drop the Guest user account, in all databases except Master and TempDB, although sufficient testing should be conducted to validate applications will not break with this security restriction.

Question.What do synonyms do and when could you make the case for using them?

Ans.
Synonyms were released with SQL Server 2005. Synonyms enable the reference of another object (View, Table, Stored Procedure or Function) potentially on a different server, database or schema in your environment. In short, this means that the original object that is referenced in all of your code is really using a completely different underlying object, but no coding changes are necessary. It is an alias, means to simplify migrations and application testing without the need to make any dependent coding changes.
Synonyms can offer a great deal of value when converting underlying database objects without breaking front end or middle tier code. This could be useful during a re-architecture or upgrade project.

Question.With SQL Server 2005, what is the technique to manage errors in Integration Services?

Ans.
Event Handlers can be setup to capture errors and perform operation such as writing the errors to a table. Additionally, custom logging can be setup at the Control Flow level and perform custom logging as well.

Question.Explain the difference between the INTERSECT and EXCEPT operators? What is the value of these commands over other techniques?

Ans.
INTERSECT - gives final result set where values in both of the tables match.
EXCEPT - gives final result set where data exists in the first dataset and not in the second dataset.

The advantage of these commands is that it allows you to get a distinct listing across all of the columns such as the UNION and UNION ALL operators do without having to do a group by or do a comparison of every single column.

Question.Explain service oriented architecture (SOA) technology introduced with SQL Server 2005 and what are some of the components of the technology?

Ans.
Service Broker is the service oriented architecture (SOA) technology introduced with SQL Server 2005. It includes:
Endpoint - Communication point in the database for Service Broker. In addition, ability to specify the authentication, encryption and message forwarding. A single Service Broker endpoint can be created for each database, however the HTTP and Database Mirroring can be created as well.
Message Type - Format for the message that is being sent and received. The format could be simply well formed XML or be bound to a schema.
Contract - Which message types are sent by either the initiator or the target.
Route - For the specific Service Broker Service which SQL Server instance and database the messages will be routed to during the Conversation.
Queue - This is the logical container in the Service Broker application. The queue serve as a storage mechanism in Service Broker. All data is RECEIVED from the queue for processing. Although the queues are all named differently, they are actually all of the same format with both relational and binary columns that can be converted to an XML format.
Service - Service correlates the queue and the contract.
Remote Binding Service - Defines the Service Broker user to with the Service.

Question.What is the OUTPUT clause and what sorts of options does it provide?

Ans.
The OUTPUT clause is introduced in SQL Server 2005 and has the ability to access the INSERTED and DELETED tables as is the case with a trigger. The OUTPUT command can be added to your stored procedures or T-SQL scripts in order to write the data out to an auditing table or return the data back to the front end client.
The OUTPUT clause has the ability to mimic and/or replace some of the functionality typically addressed by triggers. In SQL Server 2008, it can also be used with MERGE command to access INSERTED, UPDATED or DELETED values

Question. What is database mirroring?

Ans.
Database mirroring was introduced in SQL Server 2005. Database Mirroring consists of two mandatory (Principal and Mirror) roles and an optional third (Witness) role. These roles would be installed on the Enterprise and Standard editions of SQL Server 2005. The Witness role can be installed on an any version of SQL Server including Express Edition.

In terms of data protection, Database Mirroring has three different options. First is High Availability Operating Mode. High Availability Operating Mode provides durable, synchronous transfer of data between the principal and mirror instances including automatic failure detection and failover. Second is High Performance Operating Mode. With the High Performance Operating Mode the overall architecture acts as a warm standby and does not support automatic failure detection or failover. Third is High Protection Operating Mode. The High Protection Operating Mode operates very similar to the High Availability Mode except the failover and promotion (mirror to principal) process is manual.

Question. How does the database recovery model impact database backups?

Ans.
The database recovery model is responsible for the retention of the transaction log entries. So based on the setting it determines if transaction log backups need to be issued on a regular basis i.e. every minute, five minutes, hourly, etc. in order to keep the transaction log small and/or maintain a log shipping solution.
Simple - Committed transactions are removed from the log when the check point process occurs.
Bulk Logged - Committed transactions are only removed when the transaction log backup process occurs.
Full - Committed transactions are only removed when the transaction log backup process occurs.

Question.What are some common post restore processes?

Ans.

• Sync the logins and users
• Validate the data is accurate
• Notify the team\user community
• Cleanse the data to remove sensitive data i.e. SSN's, credit card information, customer names, personal information, etc.
• Change database properties i.e. recovery model, read-only, etc.

Question.For differential backups, how is the data determined for those backups?

Ans.
As data is changed in the extent, the extent is marked as changed and the entire extent is backed up.

Question.How is a point in time recovery performed?

Ans.
It depends on which backup types are issued. In this example let's assume its full, differential and transaction log backups are issued.

• Restore the most recent full backup with the NORECOVERY clause
• Restore the most recent differential backup with the NORECOVERY clause
• Restore all of the subsequent transaction log backups with the NORECOVERY clause except the last transaction log backup
• Restore the last transaction log backup with the RECOVERY clause and a STOPAT statement if the entire transaction log does not need to be applied.