In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
Deployment Manager NEW! Automate your .NET deployments
Deploy ASP.NET applications and SQL Server changes fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
 
Get the SQL Server concurrency eBook Free eBook: SQL Server Concurrency
Every DBA must understand SQL Server concurrency and how to troubleshoot any issues. Kalen Delaney's eBook explains all - download it today.
 
Take our database development survey $100 Amazon Vouchers Up For Grabs!
Take our short survey about database development and be entered into the prize draw! Begin database development survey.

In This Issue

T-SQL Restore Script Generator

A procedure that generates point in time restore database & log commands for your databases. More »


Free eBook: SQL Server Backup and Restore

You can download a free eBook from SQLServerCentral and Red Gate software on the most important task a SQL Server DBA or developer needs to understand. More »


Securing SQL Server 2012 Integration Services Packages using Digital Certificates

There are several different ways of restricting access to SSIS packages and confidential information they contain. However, preventing unauthorized users from running or viewing the content of your code is not the only security measure that you should take into consideration.  More »


From the SQLServerCentral Blogs - Day 17 of 31 Days of Disaster Recovery: When are Checksums Written to a Page

31 Days of Disaster Recovery Today is day 17 of 31 Days of Disaster Recovery. The series has skipped a... More »


Editorial - Serverless Software

I love the words "loosely coupled." At times in my career, I've built software and have aimed to ensure that processes, workflows, and components worked well on their own. In the places where I had to work with data or services on different physical machines, I tried to ensure that each item would run separately, and communicate when needed, but wouldn't fail if another machine was down.

That's kind of the idea proposed in the article that says the future of software is serverless? It's an interesting read, talking about the advances in cloud services that can change the way developers build applications. Developers should think about liking services, and pieces of applications, ignoring the idea their systems are tied to specific servers.

It's a future that I think makes sense for most environments. Development shouldn't be concerned about the size or care of physical machines. Instead they should think about building on a platform of services, and expecting the scale to grow or shrink as needed, without code changes.

I do think this could be the future in which development proceeds inside companies, as well as for commercial software. However vendors need to sell "private clouds" which function in the same way as the public ones, for those companies that want to control, and invest in, their own hardware.

» Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

» To submit an article, rant or editorial, log in to the Contribution Center


Question of the Day

Today's Question:

You’re creating a database to contain information for a university web site: news, academic announcements, admissions, events, research, etc. Should you use the relational model or XML?

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: Database Design. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Yesterday's Question of the Day

USE tempdb

CREATE TABLE dbo.MyTable
(
 MyTableID INT NOT NULL IDENTITY (1, 1),
 SomeText VARCHAR(100)
);

INSERT  INTO dbo.MyTable
        (SomeText)
VALUES  ('qwerty');

DBCC CHECKIDENT ('dbo.MyTable', RESEED, 100);

INSERT  INTO dbo.MyTable
        (SomeText)
VALUES  ('uiop');

SELECT  MyTableID
FROM    dbo.MyTable;

Which two values are returned? Assume this is SQL Server 2012.

Answer:

  • 1
  • 101

Explanation: SQL Server 2012 BOL for CREATE TABLE - http://msdn.microsoft.com/en-us/library/ms174979(v=sql.110).aspx

The Identity "seed" is the value used for the very first row loaded into the table. In the example code the first row inserted received an identity value of 1.

When supplying a new RESEED value the rules about which value will be issued next change depending on whether there are rows in the table when DBCC CHECKIDENT with RESEED was issued.

SQL Server 2012 BOL for DBCC CHECKIDENT - http://msdn.microsoft.com/en-us/library/ms176057(v=sql.110).aspx

DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )

Current identity value is set to the new_reseed_value. If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.

» Discuss this question and answer on the forums

SQL Server 2012 Query Performance Tuning

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

Get your copy from Amazon today.


Featured Script

Grouped Failover, a 2008R2 version of Availability Groups

One of the new cool features in SQL 2012 is the SQL Server Availability groups. In other words being able to failover a group of databases which are logically connected. i.e. SharePoint databases. Well, it is also possible to do that in SQL 2008 (R2). It’s called a Grouped Failover. More »


Database Pros Who Need Your Help!

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

Can Somebody answer this simply and briefly please? - Can Somebody answer this simply and briefly please? "We are experiencing long delays with SQL on the client replicating to...

Unable to set ShareWebDb back to MULTI_USER and ONLINE - - We had an issue with ShareWebDb where we lost access to companyweb because the 'user' was unable to login. I...

SQL logins orphan users - Hi All, Just question the poped up during discussion with my collegue regarding fixing SQL orphan users when they are available...

DBCC CHECKDB FAILED - Hi Experts, Got below messages when ran checkdb Executing the query "DBCC CHECKDB WITH NO_INFOMSGS " failed with the following error: "Process ID...

sp4 on cluster - ran the sqlserver2005 sp4 in windows server2008r2 cluster node1 and getting below error for Backward Compatability feature upgrade and except...

SQL Server 2005 : Business Intelligence

ssis space problem - Hi friedns i have small doubt in ssis plese tell me how to solve this issuse i have source table and...

SQL Server 2005 : SQL Server 2005 General Discussion

Check Datetime Existence within multiple columns - Hi there, I've a BillDate as date, and a Mark as bit column in First table. (Mark=0 by default) In Second table I've...

BACKUP CERTIFICATE error Msg 15466, Level 16, State 1, Line 2 - I am receiving the following errror when trying to backup the certificate I just created. Msg 15466, Level 16, State 1,...

Problem with xp_cmdshell - Hi there, hope in your help. I need to save in my hard disk the txt file generated with this code,...

two Backup of a database at same time - hello experts, just want to know, if backup is happening for a database and one scheduled job also triggered for the...

SQL Server 2005 : SQL Server 2005 Strategies

Run stored procedure when temp table is created/dropped - Hi guys and girls, I need to update 1 or 2 records in my database each time a temp table with...

SQL Server 2005 : T-SQL (SS2K5)

Delete specific node from XML column - TABLE tblProduct ProductId int Productname varchar(20) TABLE tblQuantityP QuantityPId Quantity int Price int ProductId XML DATA IN tblProduct ProductId ProductName 1 Prod1 2 Prod2 3 Prod3 4 Prod4 5 Prod5 6 Prod6 DATA IN tblQuantityP QuantityPId Quantity Price ProductId 1 12 12 <itemids> <itemid>1</itemid> <itemid>2</itemid&g

A case statement in the from clause? - I need some help with a query that involves changing the joins when a field is a certain value. The...

Rows with Min() values - Hi All Can someone help me how to select the row with min(E_DATE+E_Time) for each PATIID from the below table? [code="other"] PATID...

Check Datetime Existence within multiple columns - Hi there, I've a BillDate as date, and a Mark as bit column in First table. (Mark=0 by default) In Second table I've...

Need a Stored procedure - Hello, I need help writing a stored procedure that will take a parameter in as @hostName VARCHAR(100) and using xp_cmdshell,...

SQL Server 7,2000 : Administration

SQL SERVER services get stopped - Hi, Everyday we notice SQL SERVER services get stopped and we have to manually then start both services and agent.We...

SQL Server 2008 : SQL Server 2008 - General

sql doubut - hi friends i have small doubt in sql server plz tell me how to solve this one i have a table...

Update Statistics script - I am looking for a script where I can update the statistics of the objects in a database if only...

Can Somebody answer this simply and briefly please? - Can Somebody answer this simply and briefly please? "We are experiencing long delays with SQL on the client replicating to...

Subtracting specific dates from today - Hello, I know this isn't too difficult, but I am have a momentary brain lapse. How can I calculate the number...

Resource governor always uses default Work Load Group - When I setup resource governor with the below script, Both two user uses default Workload Group? Where do i do some...

Check Datetime Existence within multiple columns - [font="Courier New"]Hi there, I've a BillDate as date, and a Mark as bit column in First table. (Mark=0 by default) In Second table...

Select statement with condition. - Hi Team, I have below query: [b]SELECT lname +'.' +fname AS emp_name FROM employees[/b] Result : First_Name.Last_Name If any one (lname or fname is NULL, then...

QUERY SALVATION - Hi geniuses, I need you guys to fix me a query. Here's my table [code="sql"] ReceiverGroup PayerGroup Value GroupA GroupA 1000 GroupA GroupC 3000 GroupA GroupC...

Do I need a linked server if both databases reside within the same SQL Named Instance? - DatabaseA needs to be able to retrieve data from DatabaseB on a periodic basis. Both databases reside on the same...

Spotlight On SQL Server - Hi, Recently we've installed Quest Spotlight Tool on a Server to monitor all of our Servers. But as RDP limits only...

How can I find who/what process dropped view/table from my sql server 2008 database ? - Experts, How can I find who/what process dropped view/table from my sql server 2008 database ? Auditing is not implemented. Thanks, Smith

D/R CONSIDERATIONS - What are the factors that should be considered from storage and network analysis for D/R .

Solar winds for a server - How to evaluate solar winds information for a server any documents.

Dynamic script to verifying the backup's - Am using below script to verify the backup's dynamically... but am not getting expected output DECLARE @Count INT DECLARE @query nvarchar(2000) DECLARE @current...

what is difference between database and database instance - Hello everyone, Please let me know the difference between database and database instance. Thank you.

Inserting data with SELECT and UNION statements - Hi, Can someone please explain how inserting data with SELECT and UNION statements work, like in the following example; insert student(student_name) select...

CSV to Excel - How to automate the process of converting csv file to excel file?

Group By Inside CASE Statement - Hi geniuses, It is possible to have a 'GROUP BY' inside a CASE Statement? In order to Sum my values properly? What...

Trying to understand non-clustered index Fragmentation - Ive got a large transactional table. its has a clustered index on the 'rowID' column (bigint), which is in great...

Clock-In/ Clock-Out - I am working on a clock in and clock out system. I have one table with Employee id, punchTime, punchType. test...

how can i get count of sikped question depend record column and collectionofResponse column? - here i am having four table [code="sql"] DECLARE @question table ( QuestionId int, Record uniqueidentifier, indexnumber int, questiondetail text, IsActive bit ) -- select...

error - C:\Users\1245>sqlcmd -S MOLNEW0014\TEST -i c:\script.sql Sqlcmd: 'c:\script.sql': Invalid filename.

things to know when designing a new database - what are things one need to always keep in mind when designing a new database for an application

SSIS from 64 bit to a 32 bit machine, error on key not valid - From a SQL Server agent job I execute the following: "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "D:\Nickname2\Nickname2\Nickname2.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF...

XML with word document style - hello all. is it possible create one xml file with word document style from query result?

update trigger - there are two types of trigger for updating table,one is column level and another is row level update trigger what is...

Disaster Recovery (DR) Vs High Availability (HA) - hi all, What is the difference between DR & HA. What are different options available in SQL Server for DR/HA. The below concepts 1....

Handy and Simple tool for SSMS 2008 R2??? - Hello, I was wondering why SSMS does not have the standard builtIn plugin Quick Math (for example: sum, avg, max, min)....

Loading DB2 CLOB data into SQL Server 2008 R2 SP1 - We are developing a process that sources a CLOB field from Db2, and inserts it into a SQL Server database. The...

Upgrade SQL 2008 to SQL 2008 R2 - Has anyone performed an upgrade of SQL 2008 to SQL 2008 R2? I'm running SQL2008 sp1 EE and am planning upgrading...

SQL Server 2008 : T-SQL (SS2K8)

Query help - Hi, I have a query that returns a ProposalId a RiskId and RiskTaken bit flag. I've setup some test data below: [code="sql"]DECLARE...

where fldValue=123 vs join to table with one row - I have a huge DB and a view that pulls records from multiple tables, some as big as 2M records....

Get distinct value - I have the following tables Product --stored for productid ProductRelation -- storing linked product id's [code="sql"] DECLARE @Product table(ProductID int) DECLARE @ProductRelation TABLE (FirstProductID int,SecondProductID int) INSERT...

Check Datetime Existence within multiple columns - [font="Courier New"]Hi there, I've a BillDate as date, and a Mark as bit column in First table. (Mark=0 by default) In Second table...

Indexed Views - why don't they improve performance?!!!! - I have a query that takes quite a while to run even after optimization. DML statement on the underlying table...

How To Validate in Procedure - I Created a one Procedure ..its given Below... [code="sql"]create procedure [dbo].[USP_Reliance_Master_Upload] as begin declare @dt datetime , @Mnth varchar(10) BEGIN TRY SELECT @dt...

using a trigger - In a C# 2010 desktop application, I need to add an audit table to an application that would be similar...

XML query - I have the following basic XML document: [code="xml"]<MatchKeys> <fuzzyKeys> <key key1="MatchKey_PostOut" key2="MatchKey_Name1" /> <key key1="MatchKey_Name1" key2="MatchKey_PhoneticStreet" key3="MatchKey_PhoneticCompanyName"/> <key key1="MatchKey_PostOut" key2="MatchKey_PostIn" /> ...

SQL Server 2008 : SQL Server Newbies

Check Datetime Existence within multiple columns - Hi there, I've a BillDate as date, and a Mark as bit column in First table. (Mark=0 by default) In Second table I've...

How to fetch duplicate records - Hi, I am using SQL Server 2010 with Visual Studio for my application. Now I have a table say "EmEmployeesand that...

Cachestore flush - Hello Masters, Again need your help .. :-) During monitoring I found one of my database in restoring mode from last two days..!!upone...

SQL Deadlocks - In my production database I'm getting a few deadlocks between updates and selects. Where the selects are being killed off. I'm...

Linked Server - I am trying to create a linked server in my local database [b]"SQL_Server2"[/b] To [b]"SQL_Server1"[/b]. Only Option to connect to [b]"SQL_Server1"[/b]...

Torn page issue - Hello Masters, Can torn page issue occurs due to lack of disk space ? I know torn page is the page that...

SQL Server 2008 : Security (SS2K8)

Encryption basics. - Hi all. I am researching the feasability of encrypting a few columns in one of our databases. I have done...

SQL Server 2008 : SQL Server 2008 High Availability

Can Somebody answer this simply and briefly please? - Can Somebody answer this simply and briefly please? "We are experiencing long delays with SQL on the client replicating to...

SQL Server Network Name - Hello I am installing SQL Server 2008 R2 Enterprise on a 2 node cluster and reached the instance configuration in the...

replica of prod server - Hi, I am trying to create a replica of production server, so that we can divert some traffic. I am thinking...

Failover clustering with Database Mirroring - hi all, I'm learning these HA/DR things. [b] "Failover clustering with Database Mirroring"[/b] What does it means? Clustering: Two nodes being synchronized. (Physically & SQL...

Error in SQL Server2008 - When ever i am trying to view the properties of the Database, below error occurs everytime.. { Cannot Show requested dialog. Additional Information Cannot...

Creating an Active/Passive cluster - Friends, I am exploring an option to create SQL cluster with active/passive node on an already existing cluster. Here is the scenario: Existing:...

In which file is the transaction was backed up - First let me apologize if this is not the correct palace for this topic but it is strongly connected. when we...

Failover restart policies - Helpful folks, I'm in an odd situation where, in my new job, I don't have a test cluster in which I...

Identity columns - Hello all, I have set up one way pull transactional replication with sql 2008 r2 and the subscription was initialized from...

SQL servers agent service failure - I am installing SQL server on cluster but SQL Agent has failed to comeonline with bellow errors in event log. Cluster...

SQL Server 2008 : SQL Server 2008 Administration

Rebuild Info - When executing the query it shows that the fragmentation in percent is 70% but after rebuilding the indexes the fragmentation still...

Error in Sql Server 2008 - When ever i am trying to view the properties of the Database, below error occurs everytime.. { Cannot Show requested dialog. Additional Information Cannot...

How to take a print of ER diagram from SQL Server? - Hi, I wanted to take a print of ER diagram but it all the tables gets saved as a single image....

need a suggestion on Exposing the Data to Customers - Hi All, I joined a new team and need a suggestion on exposing data to downstream customers. I have 5 SQL...

Programming : General

Check Datetime Existence within multiple columns - Hi there, I've a BillDate as date, and a Mark as bit column in First table. (Mark=0 by default) In Second table I've...

Programming : XML

Another XML to tabular question - Hello All, I'm having a bit of a problem getting my XML into a correct tabular format. Below, I have a...

SQLServerCentral.com : Anything that is NOT about SQL!

What is the Best way for getting day wise data in production server in mysql - Can any one help me............. Please tell me what is the best way for getting data in production server. If possible tell...

Data center outside US? - We currently host our own (aging) hardware on site and are considering moving to the cloud. Our consultant suggested a...

Reporting Services : Reporting Services

Error applying filter at tablix level? (Cannot compare data of types System.Decimal and system.double) - When I apply a filter on a tablix i receive the error (the report runs fine without the filter): "an...

Issue with Small matrix spilling over 2 pages - I have a small matrix that measures 3.5 on the ruler. my report is set as landscape. but it seems...

Timetable report - Looking for some advise on how to construct a timetable report for students. Normal stuff - 7 days a week down the...

Database Design : Disaster Recovery

Quantum - We are replacing our tape backups with something "better", my backup expert tells me that Quantum is the best, especially...

Database Design : Design Ideas and Questions

SEQUENCE Problems - Hello. I've started using a SEQUENCE in a table instead of an identity. I seem to be experiencing problems of the sequence...

Data Warehousing : Integration Services

Generic SSIS Package Creation - Hello, I have an Idea and want to see if this can be possible or any real time Developed solution already...

SSIS convert to date issue - im developing an ssis package. while im in the sql server , there is a field that contains a text string , for...

Data Warehousing : Analysis Services

SSAS browser missing Data - Hi all, I have a new SSAS Cube that I have designed and I am finding that the data doesn't seem...