A community of more than 1,500,000 database professionals and growing

Repairing a Broken Log Shipping Plan From a Differential Backup

Perry Whittle from SQLServerCentral.com

This article details how to reinitialise a Log Shipping scenario from a primary differential backup More »

An Interview with Amazon Web Services

Press Release from SQLServerCentral.com

A short interview with Sundar Raghavan of Amazon Web Services about SQL Server and their support and offerings of cloud data services. More »

Temporary Tables in Oracle and SQL Server

Press Release from SQLServerCentral.com

Jonathan Lewis (Oracle Ace Director, OakTable Network) and Grant Fritchey (Microsoft SQL Server MVP) will host a live discussion on Oracle and SQL Server, this time in relation to temporary tables. More »

SQL Database Management Survey

Press Release from Red-Gate

Win one of two $50 Amazon vouchers by entering our database management survey. We’re finding out more about how SQL database professionals are doing backup and recovery, using cloud services and more. Answer the short survey for a chance to win. More »

Ten Things I Wish I'd Known When I Started Using tSQLt and SQL Test

Additional Articles from SimpleTalk

The tSQLt framework is a great way of writing unit tests in the same language as the one being tested, but there are some 'Gotchas' that can catch you out. Dave Green lists a few tips he wished he'd read beforehand. More »

From the SQLServerCentral Blogs - The State of Microsoft Self-Service Reporting

pturley from SQLServerCentral.com

(article published on the MVP Award Program Blog) I really enjoy this topic.  One of the reasons, I suppose, it’s such... More »

Security is becoming more of an issue for many companies. Increased regulation, more scrutiny from the media, and customers that are more concerned about their data handling and privacy are forcing companies to spend more resources to ensure better security. However building secure systems takes skill and experience, something many companies lack. Acquiring those skills can be hard, and I think as an industry, we do not disperse information on secure coding well. If we could get developers and administrators to read about security with the same level of interest they show in the iPhone 5 v the Galaxy S3 v the Lumia 920 debate, perhaps things would be different.

One vendor is betting that companies will look to outsource security needs. Webroot has moved to a hosted service, confident that customers will move to the new service for its ease of implementation and arguably better protection. By controlling the software themselves, and updating it as quickly as possible to meet new threats, Webroot can do a much better job of ensuring security than most companies can by managing the service themselves.

That's an interesting idea, and it's similar to the idea of threat detection that Bruce Schneier wrote about a decade ago. This doesn't solve application issues, but it can improve security for threats that attack your network. That's if the vendor actually does their job correctly.

There are any number of issues with this model, but as more companies get comfortable with the idea of renting services and platforms, moving to the "cloud", I can see this idea growing. For us in the data world, this means that we have another group to interface with, potentially audit to ensure they are not violating any data protection. As far as your actions if you find issues? That can be complicated, but it certainly will require that your communication skills are strong.

The Voice of the DBA Podcasts

We normally publish three versions of the podcast each day for you to enjoy. Today there is no podcast due to Steve being ill. Hope to have the podcasts return tomorrow.

Steve Jones from SQLServerCentral.com

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

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

Today's Question:

Consider the table structure given below and the series of queries which are used to insert value in this table

CREATE TABLE IdentityTbl
  (
     id INT IDENTITY
  )
GO

-- Query#1
INSERT INTO IdentityTbl

-- Query#2
SET IDENTITY_INSERT ON

DECLARE @id INT

SELECT @id = ISNULL(MAX(id) + 1, 1)
FROM   IdentityTbl

INSERT INTO IdentityTbl (id)
VALUES      (@id)

SET IDENTITY_INSERT OFF

-- Query#3
INSERT INTO IdentityTbl
DEFAULT VALUES

-- Query#4
INSERT INTO IdentityTbl (id)
VALUES (DEFAULT)

Out of the 4 insert queries, select the query which inserts the value in IdentityTbl.

By Lokesh Vij

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

This question is worth 1 point in this category: T-SQL. 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.

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.

How many rows are returned?

CREATE TABLE #TABLE1
(
  Col1 INTEGER
)

CREATE TABLE #TABLE2
(
  Col2 INTEGER
)

INSERT INTO #TABLE1 VALUES (1), (1), (1), (1), (1)
INSERT INTO #TABLE2 VALUES (1), (1), (1), (1), (1)

SELECT Tab1.Col1, Tab2.Col2
 FROM #TABLE1 Tab1 
  INNER JOIN #TABLE2 Tab2 
   ON Tab1.Col1 = Tab2.Col2

By Amit Yenurkar

Answer: 25

Explanation: If all the rows get matched then the query will act as a cross join.

» Discuss this question and answer on the forums

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Find all actual words from Dictionary Table using jumbled word

Ajeet Singh from SQLServerCentral.com

How to get all actual word from a table,if I pass a jumbled word More »

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

SQL Server 2005 : Administering

How can I find which certificate is used ? - Experts, Suppose a user gives a backup taken from one of the SQL server 2008 encrypted with TDE. But user doesn't...

String or binary data would be truncated. - I am getting error in below code: SET NOCOUNT ON CREATE TABLE #EVENTS (LOGDATE DATETIME, PROCESSINFO VARCHAR(50), VCHMESSAGE VARCHAR(800), SOURCE VARCHAR(20),...

Need help auditing - Hi everyone, Before I got to this new job, people were allowed to create many unused objects in our production...

Maintenance Jobs Fail After Changing sa Password - On a number of different instances of various SQL versions, Including SQL2005 and 2008, I am having Maint jobs failing with...

Date Calculation Spin Error/Warning Messages - Has anyone see messages similar to the following on their SQL 2005 box? [191] Warning [1]: Possible date calculation spin for...

SQL Server 2005 : Backups

Backup scripts vs. Maint Plans - Hi all, What are advantages and disadvantages of Maintenence Plans for backups vs backup scripts ? Our version is 2008. Thanks

SQL Server 2005 : Business Intelligence

Extend Timeout on SSRS/BIDS Tooltip - Hi, Fairly new to this so bear with me please. I have a report built in SSRS / BIDS. I have the...

SQL Server 2005 : Data Corruption

BACKUP DATABASE is terminating abnormally - Dear All, Could someone please advice on this. Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. Msg 845, Level...

SQL Server 2005 : SQL Server 2005 General Discussion

SQL Server 2005 64 bit vs 32 bit - Hello to all. I have installed SQL Server 2005 Developer Edition and Visual Studio 2005 Standard Edition on my Windows...

SQL Server Tools - Recomendations On Stress Testing and I/O Testing On New SQL Server - Need to test/stress a new SQL Server box to see what she can do. I went to get a copy...

SQL Server 2005 : SS2K5 Replication

Want to monitor Replication using a Job. - Actually, Transactional Replication is running between Sql server 2005 to SQL Server 2000. But, Replication Status is running fine. all agents...

Distribution database is too big; - Distribution database is 28 GB and growing 1GB per day. I have changed the retention period from subscription never expires to...

SQL Server 2005 : SQL Server 2005 Integration Services

How to convert varchar(20090303084115) to datetime - Hi, While loading the data from Excel file to staging table using SSIS 2005 contains one column "StartDate" Some of the values...

Transfer Data from one database table to Another database table - Hi, I have two databases – DBSample1,DBSample2 Both databases contains same table names(TabSample1 and TabSample11) and same design Need to copy the...

GENERATE IDENTITY VALUE IN THE DATAFLOWTASK PIPLINES - Is there a way to generate identity value for rows that are passing in the dataflow task pipelines?. There are two...

SQL Server 2005 : T-SQL (SS2K5)

trailing space in len() problem - hello all shortly, this [code="plain"] declare @sqlStr varchar(max) set @sqlStr = 'aa, ' print len(@sqlStr) [/code] returns 3 on one server and 4 on another... if i try...

trigger update and compare string !? - Hi, i'm a beginner with t sql => sql server 2005. i have this table : T_courses with these fields : idCourse (int), Libelle...

SQL Server 2005 : SQL Server Newbies

Quickest Way to Find an Index - Hi Folks can comeone tell me the quickest way to find an index in some 100 plus databases ? is there a...

SQL Server 7,2000 : Administration

backup and restore - hi where can i find how to do backup and restore in database article step by step. and also how to do...

SQL Server 7,2000 : Security

Anonymous access in SQL RS 2008 - Hi In SSRS 2008 their is no IIS required. So anonymous access is not possible by configuring IIS. I find the...

SQL Server 7,2000 : T-SQL

Getting the last entry based on a previous entry. - Hello, We are still on SQL 2000 and I need help with a query. We have a table named Reporthistory that records...

SQL Server 7,2000 : SQL Server Agent

sql server maintenace jobs schedule at 10 am not executing - HI to all can any one guide me in this sql server maintenace backup jobs schedule at 10 am not...

SQL Server 2008 : SQL Server 2008 - General

Multiple columns IN support - Hi All, Currently I am working on oracle to MS SQL server migration project. SQL select query in Oracle: Select ID, NAME...

Date conversion issue - Hi All, Using below conversion i am getting date only like 20120926 but my requirement is 201209261015 like hour & time convert(nvarchar(50),(GetDate()),112)) Please...

Unable to stop a job running "sql server analysis services command" - Hello, From time to time, a SQL Server job with a step type "sql server analysis services command" is running for...

SQL Transactional Replication 2008 problems - Hi, i need help urgently on this one. I have transactional replication in place between two servers. These db's are rather...

Unable to connect to SQL Server 2008 Clinet system from Windows7 - hi all, I try login to my client system sql server using Sql Server Client Management Studio 2008 wit Windows 7....

Fiscal year week - Hi all, I'm here with another question may be it is simple.but i searched in web i didn't get the...

how to write insert query for this two table with only one parameter? - here iam having a parameter @questionIdandchoiceid varchar(8000)='1-2,2-3,3-5' from this parameter i just want o insert into two table DECLARE @questionIdandchoiceid varchar(8000)='1-2,2-3,3-5'...

Stored Procedure Insert Locking Until End of Processing - Hi, I have been trying to get the stored proc below to not lock dbo.some_table during the waitfor delay portion....

Selecting a substring up to a specific (special) character - OK, so I would like to write a query that pulls info after a certain number of underscores. For example: I...

Preventing from "create database" with data and log file on C: drive. - Hello everyone, Thanks for looking in to my question. I have a SQL Server 2008R2 dev environment. I have set the default...

MCITP Certification - Question: At this point is it worth to take the MCITP certification? Exams: 70-488 and 70-452? I'm studying to take these test...

Query result in SQL Server 2000 return correct and SQL Server 2008 R2 SP2 return wrong - Hello, We have a problem in the query within the attached file. When I run in sql server 2000 it...

asynchronous_file_target access denied error - I am working thru the extended events and I am trying to set the output to a file using asynchronous_file_target....

check constraint opinion? - What's your opinion on check constraints in SS? Check constraints seem good for enforcing constraints at the table level. Putting validation...

Fetching data from an SQL Server in to an Access database - Hi Everybody, I am trying to fetch some data from an SQL Server Database. I have a query, but it keeps prompting...

cannot get sql intellisense working after vs2010 update - from scouring the net, this seems to be a big problem, but i've yet to be able to cure it...

excel help...in ssis... - Hi all, i have to move invalid records(data type mismatch) from excel into error log and have to process only...

DatabaseMail problem - I have 2 instances set up on a cluster. On one of the instances database mail is working correctly on...

"Dynamic" view question - We have a existing system full of accounts. There is a requirement that any new accounts are to be divided...

Lean operation project - I work in healthcare and I am pretty fresh in my career and would like to ask some advise. As...

Understanding the difference between Join and Where filters - SQL 2008R2 - I'm new to the TSQL world (coming from visual basic) and am working with large tables (125+ million rows) and...

sql service restarted unexpectedly - hello experts, in my production server sql service restarted unexpectedly, the concern teams are asking for root cause analysis doc for...

Replicating/Update Remote Server - Hi everyone I was wondering what the best solution/practice would be for the following scenario: We have a complex data structure with...

new to DBA an dtechnology - Hi all, I am new to technology world, been in customer support, planing to move to technology, been thinking of doing...

Database Mirror Error Msg 1418 - Hi, I have setup many mirrored databases before but this time has been very frustrating. I did search this error...

SQL Server 2008 : T-SQL (SS2K8)

T-SQL query help - Hi.. I have data the data in the below temp table(SQL query) [code="sql"]IF object_id('TempDB..#tTemp1') IS NOT NULL DROP TABLE #tTemp1 CREATE TABLE #tTemp1(ID int,...

Tables most and least used in SQL Server - Hi, I wanted to know this just to increased my information as i am a newbie. I found out this on...

sys.tables.type - Hi, What does sys.tables.type meant.

coversion error while inserting value in datatype=Int column - Hi...I am getting this message Msg 245, Level 16, State 1, Line 7 Conversion failed when converting the varchar value 'd1867 ' to...

user_seeks Vs user_scan - Hi, I tried finding this (user_seeks Vs user_scan) out on internet but no proper explanation is present for this. I was...

CLR Funtion To split the cama separated string. - Hi All, I need a CLR function to split the "," separated string. Ex: string like 1,2,3,4, Result: Value ---------- 1 2 3 4 Thank’q

Restart runningtotal when predetermined value is reached - Hi, Im trying to create a running total based on FieldToSum that will restart everytime runningTotal <= 25 and count...

Need Help on User Tree Hierarchy with Addition/Deletion Option - Hello All, I have situation where if user pass the userid then their respective hierarchy display.Here is SP CREATE PROCEDURE TraverseUsersRecursive @UserId...

Consolidating records - TSQL problem - Good afternoon, I've been working on a project to eliminate duplicates from a record set containing contact and address details. (ChrisM@Work/Home...

FK referencing multiple tables - Hi, Is it possible to have a column on table A that references table B or table C depending on another...

How to transform this result? - Hi SQL Gurus, I have this default master data table below: [code="plain"] ITEM | CNT | DESCRIPTION ---------------------------------------- HMMEDA | | HYH Med Oil HMMEDA | G01 | HYH Med...

SQL Server 2008 : SQL Server Newbies

bcp a pipe delimited file - hi all, i want to import a flat file of type Employee ID|Employee Name; 134543543|asdfasdfdsfdf; inside a sql proc As bcp requires the destination table...

Difference between decimal and smallmoney - Hi smallmoney type takes up to 4 bytes. Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a...

Problem with Grouping and max() - Basically the data is access data to a building. I want to establish if the person is in or out...

CommVault Backup errors on SQL 2000 - i am getting the belwo event on windows event: Event in Windows: Error: 823, Severity: 24, State: 2 I/O error (torn page) detected...

How Do You Keep The "Pieces" Of A Complex Statement "Organized" - I just finished a complex SQL statement; and, yes, I know there are even more complex ones. When I am faced...

Logshipping is not working. - Hi, We have a log-shipping set up on the production server for reporting purpose. Unknowingly, I have set up a backup strategy...

Using Access to put data into SQL Server - Hello everyone. So I have a database in SQL Server 2008 that contains 5 tables. I have connected Microsoft Access...

SQL Server 2008 : Security (SS2K8)

Local group permissions issues - We restore Production backups to our UAT server nightly to keep data refreshed. The devs are given access through a...

Use Windows Logins rather than Windows Group to control access to SQL Server - after doing some researching online...found two different Microsoft sql server 2008 R2 security best practices docs.... one is recommending to use...

SQL Server 2008 : SQL Server 2008 High Availability

LOGSHIPPING RE-ESTABLISHMENT - Hi, I want to understand the Logshipping reestablishment Scenerio After a Failover.Below Mentioned are the details of the Environment for the...

Index !!!! - Now Daily at morning 6:00 am the server (Database) does not have any transactions.. i mean its on IDLE state...

Active /Active configuration - Hi All, I configured SQL cluster configuration successfully as Active /Passive but i am not understand how do i configure...

Replication - After setting up Replication (Transactional Publication with updatable Subscription) between two instances one instance being both Distributor and Publisher, with...

OS Error when configuring Log Shipping - I am attempting to set up Log Shipping, and after going through the configuration wizard, I continually get the following...

SQL Server 2008 : SQL Server 2008 Administration

data partitioning - Hi All, I am new sql server partitioning. I have tried implementing the range partiotioning. use master go create database demo go USE [master] GO ALTER...

Upgrading SQL Server 2008 Express to Service Pack 3 - I am investigating the most efficient way to acheive a SQL Server 2008 Express with Tools starting from a clean...

Need a Memory Utilization alert SQL Server 2008 r2 - I Need a Memory Utilization alert SQL Server 2008 r2. I am not sure which alert in sql server will...

Want to find transaction log size before taking backup - HI, I want to estimate the transaction log size before taking backup not the used space

collation issue raised by a system UNION query - Here is the bug demonstration in sql server 2008r2 a. Check the Server Collation : should be SQL_Latin1_General_CP1_CI_AS b. Create a login: abcd c....

SQL Disk to Memory - Hi all I have a quick question regarding pages moving from disk into memory If I select * from a table, are all...

can I measure how much memory is allocated to the buffer in SS2K8 on VMWare accurately - I have a 8GB SS2K8 database on a smallish VMware server with 5 other VMs running on the host. The...

trc to xls - i have a .trc trace file and i need to convert it in excel format, is there any tool

Career : Resumes and Job Hunters

MS SQL developer - Sorry for only Russian, however the job opportunity is only for RU. Thanks for understanding. ??????????? MS SQL ????????: ?? 40 000 ?? 50...

SQLServerCentral.com : Anything that is NOT about SQL!

The SQL Saturday Thread - I figured I'd start a thread (I don't think anyone's done so) for all things [url=http://www.sqlsaturday.com/]SQL Saturday[/url]! I'm heading down to...

Front End Dev - HI all, I'm very happy in my SQL world with all the tools MS give me. Just sometimes somebody wants a...

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Reporting Services : Reporting Services

SSRS SUbscriptions ! - Hello and a very good morning, yesterday i installed SSRS 2005 Standard edition on Windows 2008 64 bit machine. Everything seems...

Help with SSRS - I'm new to SSRS and T-SQL reporting. The below query works fine but I want the field R.FLDDATEDUE multiple times...

AutoRefresh fails after midnight on reports with date parameter - Hi We use some reports in our NOC which have date parameters with defaults set to the current day. These reports...

Page Break problem in reporting services when I use matrix - Hi, I have the blank page problem in my report. In the report I have a table with contains 50 rows so the...

Database Design : Disaster Recovery

need answer - hi i need to know ,what will you do for disaster recovery for your database. i need any article and anything you...

Data Warehousing : Integration Services

SSIS Package executes successfully in dev/test environment but fails in production - Hi, I have an ssis package that runs perfectly fine in test/dev environment but when deployed to production it fails. The...

Memory allocation for SSIS variable - I'm having a problem with an SSIS variable. The variable is defined as data type object. I'm writing an ADO record...

dynamic columns in flat file destination connection manager? - Hello, In a large database I wish to extract text files with the data from individual tables as well generate the...

Data Warehousing : Strategies and Ideas

Show Right Column to Right User - If I have three different user with different occupation (manager, salesman, accounting) The main question is to how display right column...