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

Working with SQL Server Recovery Models

Prett Sons from SQLServerCentral.com

The recovery models of SQL Server define how different backup and restore tasks will be performed on your database. When choosing a recovery model,  More »

SQL Server Central Webinar Series #22 – What counts for a DBA (Wednesday, March 13 2013 5:00pm - 6:00pm GMT)

Press Release from Red-Gate

In this session, Louis Davidson, Microsoft MVP, will discuss how being observant of the environment you work in can help you make sure that you are aware of the health of your database systems, as well as your career. More »

An Incremental Database Development and Deployment Framework

Additional Articles from SimpleTalk

Often, an existing database application must evolve rapidly by incremental steps. Alex describes a tried and tested system to provide an automated approach to deploying both new and existing database systems, whilst dealing with common security and configuration issues. More »

From the SQLServerCentral Blogs - DROP and CREATE vs ALTER

Kenneth Fisher from SQLServerCentral.com

When writing a script to change a piece of code, say a stored procedure or view, there are 3 basic... More »

Don't visit the FillDisk.com site, which I ran across a link to from an Arts Technica article that talks about a flaw in web browsers. It's possible a security flaw, possibly an availability flaw as well. Apparently the new HTML specification allows for sites to use the Web Storage Standard to keep data on your hard disk. There is a limit in most browsers for how much data you can store per domain, but the FillDisk site uses sub domains to put random junk on your drive. The author of the site built this as a proof of concept and was able to add 1GB of data to an SSD on a laptop every 16 seconds.

That's a denial of service type attack that I hadn't expected, but it is an interesting attack vector. I wouldn't expect this to impact servers, but if servers are consuming web services, and using controls based on browsers, there is the possibility this type of attack might affect them. I'd hope this were limited to web servers and not impact database servers, but it's certainly a concern if you have processes running on your database server that might retrieve data from a remote source.

This makes me want to re-architect the way we build data driven application in the future, to prevent this type of vandalism. Maybe building an application level firewall that proxies all access to a database server. The idea of application servers was very popular a decade ago, but it seems few systems actually implemented this type of architecture. Perhaps this is because the web server/database server pairing is such an easy paradigm to build for most developers.

Frameworks that allowed separation of the application through a middle layer could allow for caching of data in addition to more security. That could increase performance and scalability as the database wouldn't be the single bottleneck for all requests.

Steve Jones from SQLServerCentral.com

» 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.

• Watch the Windows Media Podcast - 17.6MB WMV
• Watch the iPod Video Podcast - 21.0MB MP4
• Listen to the MP3 Audio Podcast - 4.2MB MP3

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

Today's Question:

--QUERY # 1 
CREATE TABLE CLUSTERED_TABLE(a int , b int , c  varchar(50)) 
CREATE CLUSTERED INDEX CK_CLUSTERED_TABLE ON CLUSTERED_TABLE (a,b)

--QUERY # 2 
CREATE TABLE CLUSTERED_TABLE_PRIMARY(a int , b int , c  varchar(50),CONSTRAINT CLUSTERED_TABLE_PRIMARY_PK PRIMARY KEY(a,b))

--QUERY # 3 
ALTER INDEX CK_CLUSTERED_TABLE ON CLUSTERED_TABLE DISABLE;

--QUERY # 4 
ALTER INDEX CK_CLUSTERED_TABLE  ON CLUSTERED_TABLE REBUILD;

--QUERY # 5 
INSERT INTO CLUSTERED_TABLE VALUES (1,2,'ABC') SELECT a,b,c FROM CLUSTERED_TABLE

Mark the correct set of resultsets based on the numbering below after executing all the queries sequentially:

(i) Query 1 creates a table with structure (a int null, b int null, c varchar(50) null)

(ii) Query 2 creates a table with structure (a int not null, b int not null, c varchar(50) null)

(iii) Query 3 disables the index and data access to the table

(iv) Query 4 rebuilds the index, and removes the fragmentation

(v)Query 5 Inserts one record in the table ; but select fails , as data access is blocked.

(vi) Query 5 , both select and insert fails as data access is blocked

Note : All queries execute without error, please ignore syntax error, if any; I am sure there is none.

By demonfox

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.

Microsoft® SQL Server® 2012 Step by Step

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Get your copy from Amazon today.

I execute the following T-SQL

SET ANSI_WARNINGS OFF

CREATE TABLE #T(Id INT,S VARCHAR(5))

DECLARE @I INT, @S VARCHAR(8)

SET @I = 987.65

SET @S = 'abcdefghij' 

INSERT INTO #T(Id,S)
 VALUES(@I,@S)

SELECT Id,S FROM #T

The question is: what values are returned by SELECT STATEMENT. SELECT TWO (2)

By bitbucket

Answer:

• Id value of 987
• S value of abcde

Explanation: http://msdn.microsoft.com/en-us/library/ms190368.aspx
ANSI_WARNINGS is not honored when passing parameters in a stored procedure , user-defined function, or when declaring and setting variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

» Discuss this question and answer on the forums

Microsoft® SQL Server® 2012 Step by Step

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Get your copy from Amazon today.

SQL 2012 Always On Status Report

Rudy Panigas from SQLServerCentral.com

This script will produce a report on the status of the Always On replication. More »

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

SQL Server 2005 : Administering

how to execute timestamp querry in sql server 2008 ? - I am T.E. I.T. student & these sem we have dbms subject but i am so confussed how to write date...

Retrieve a substring - Dear, I want to find the time portion from the following string. "Feb 18 2013 3:35PM" please tell me the T-SQL to retrieve...

does index also change the output? - I was in an assumption that index would only change the execution plan and not the actual output of the...

Adding an extra file to TEMPDB - We have a sever with 2 physical processors and 8 logical ones. We are seeing quite a lot of blocking...

Statistics - ' Auto Update Statistics ' - 'Auto Update Statistics Asynchronously' - I have option [b]' Auto Update Statistics ' set to true[/b] and option [b]''Auto Update Statistics Asynchronously' set to False [/b]....

SQL Server 2005 : Backups

send data in small size by backup is possible - I have 2 branches database A,B same structure and objects different only in data i must take backup every day.but...

Backup Failed but backup file is created - Hey guys, I have an issue with backing up a 80GB database over the network, Here is the error:A nonrecoverable I/O...

SQL Server 2005 : Business Intelligence

Access tasks in Data Flow Task dynamically - Hi friends, I have a package where the number of columns in DB tables could be changed. I want to change...

SQL Server 2005 : Data Corruption

Database Corrupt - Hi , Last week we have a server crash in the datacenter . one of our customers use a SQL Database. When I...

SQL Server 2005 : Development

SSRS 2005 Parameter validation - Hi there, I am writing a simple report using SSRS 2005 where user selects the start date and end date , and...

SQL Server 2005 : SQL Server 2005 General Discussion

i cannot connect to sql server 2005 and 2008 - [size="4"]when i want connect it ,it takes this error,,please help[/size] [img]http://www.kurdupload.com/images/obAZS1362407690/b1.JPG[/img] [img]http://www.kurdupload.com/images/0LIAMQ1362407574/i2.JPG[/img]

Issue with OSQL - Hi Friends, Requirement : i have a table which contains around 40 columns of which name is a column which i use...

SQL Server 2005 : SQL Server 2005 Performance Tuning

High work_queue_count and THREADPOOL WAITS - Hi I am tuning the DB activity for an application that sends a high number of transactions in a very short...

SQL Server 2005 : SQL Server 2005 Integration Services

how to stop the package based on a condition? - Hi, Based on the value of a variabale getting set, I want to stop my SSIS package (successfully) without going to...

SQL Server 2005 : T-SQL (SS2K5)

More help wit Sum, Pivot and so on... - Hi! First thanks for the help I got a couple of weeks ago! :-) Now to my new proble that is to...

SQL Server 2005 : SQL Server Newbies

SQL error Connection. - http://imageshack.us/photo/my-images/339/89149644.jpg http://imageshack.us/photo/my-images/4/36524814.jpg When i Click start at "Service manager" they be like this . Im using SQL 2000 =Sory my bad english

SQL Server 7,2000 : T-SQL

DTS - result to excel - Hi Guys I have created several DTS packages, and the data gets stored in csv flat file and get mailed automatically. Now...

using locks in sql server 2005 - i have one table, concurrently multiple users are hiting the table through insert action. i have placed trigger on the table...

SQL Server 2008 : SQL Server 2008 - General

Question on Replication.. - Can I setup replications between two disconnected networks..?? For example..my company is a Web Application Development company and they sell...

database metric - Is it possible to produce any sort of database level performance metric in SQL Server. Let's say how many queries...

Huge Transaction on Database with Merge Replication, Simple Recovery Model - We have a database which normally has a 100GB transaction log. This database is a publisher in a merge replication with...

SQL 2008 enterprise to standard degradation - Hi Team, I have request from client to degrade few servers due to cost of enterprise edition. Can you suggest me the...

How to uncommit the transaction and execute the next statement in the cursor? - Hi Friends, [code="sql"] DECLARE @JurisID int, @CodedID int, @SrcCodeValueRef varchar(100), @SrcCodeDescr varchar(100), @SrcCodeValue varchar(100), @DecNumber bigint SET @DecNumber = @Number declare @result varchar(50), @chars...

SELECT list block with CASE? - is something like this possible? SELECT CASE WHEN (@X = 1) THEN COLUMN1, COLUMN2, COLUMN3 ELSE COLUMN4, COLUMN5, COLUMN6, END FROM TABLE1

An issue with my Execution plan - I have a view. I made some changes (included few CASEs in SELECT list for a business requirement) i did a...

how to do this? - create table temp(id integer, comments varchar(20), custom_id integer); insert into temp values(1,'CEO comments', 98) insert into temp values(2,'SEO comments', 99) insert into temp...

confused With View , Function and Store Procedure - i get confused where to use function , store procedure and view.plz help me ;-)

Identifying performance issues using Profiler trace - Hi All, I have been told that I would be given a production profiler trace to figure out the performance issues...

Just accepted a Sr. DBA job. Time for a gut check? - Hi SQLSC! Buckle up, I apologize in advance for the long post. In many ways, typing it out is as much...

Use parameter value in .csv output filename - Table_1 [code="sql"] ESTAB_ID ESTAB_CODE FORENAME SURNAME 1 9009001 John Jones 1 9009001 Mike Smith 1 9009001 Mary Yates 2 9009005 Ann Ennis 2 9009005 Kelly Homes 3 9009014 Harry Brand 3 9009014 James Casey 3 9009014 Chris Balls [/code] Table_1 contains a list of people belonging to different establishments. The ESTAB_ID is a unique sequential ide

partition_id VS. hobt_id - Hello! There is 2 columns in [url=http://msdn.microsoft.com/en-us/library/ms175012.aspx]sys.partitions[/url] view - [b]partition_id[/b] and [b]hobt_id[/b]. From my point of view/experience for any given row from...

SSRS. Parameters mdx script doesn't work with multi-value - Hi ALL, I need your your help!! I want to create a report with multi-value parameters with SSRS. So, I wrote this mdx...

mirroring - how to find the synchronisation of data of the previous month when the database is configured in mirroring ?

DateTime Rows to Columns - Hi I have something like a table of users and other table with dates and working time for the users, i...

Denormalising report from normalised nested source data - Hi I have an application that stores normalised and nested data. Each top level instance is identified by an instance Id....

GRANT VIEW DEFINITION - Hi all I am running the following on one of my DBs - I am using SSMS 2012 against SQL 2008R2 GRANT VIEW...

Searching for Solution - Hello All, I have one query (it may sound stupid though). We have one SQL Server 2008 (Microsoft SQL Server...

Getting minimum of top n rows without using subquery - Hi, We use SELECT min([date]) FROM table WHERE [date] IN (select top(100) [date] from table order by [date]) query for selecting...

get the first and last day of any Year/Month - Hi, i have a view with two columns, lets say SpecYear and SpecMonth, both are integer. How can I build two...

error in bulk copy - Hi I want to test some changes in the structure of a table and see the plan,I exported the data in...

SQL Server Error 233 Do i need to enable named pipes, now it is disabled - Hi Expersts, we are not able to connect to sql server with service account(SQL login) . This is SQL Server 2005. Any...

A network-related or instance-specific error occurred while establishing a connection to SQL Server - Hi, The below error is logged into application logs intermittently.But, when I review SQL Server errorlog, event viewer application log, system...

How to maintain two digit values in sp? - Hi friends, IF OBJECT_ID('Sp_Coded') IS NOT NULL BEGIN Drop procedure Sp_Coded End go CREATE PROCEDURE [dbo].[Sp_Coded] (@ParentTableName varchar(50), @ColumnName varchar(40), @CodeID int, @Number...

(Could not find stored procedure 'msdb.dbo.sp_dts_getfolder'. - sql 2005 - > 2008 R2 - I have a package on 2008r2 and job on 2005 pointing to the package on 2008r2. When i run the...

SQL Server 2008 : T-SQL (SS2K8)

MaxDOP Query Hint - Is it possible to pass a variable for MAXDop as a query hint? My query below works when I set...

Query optimisation - Hi Can anyone help improving a query i have I have a table with a list of phone numbers Table1 Telephone 01212234567 01212234568 01212234569 01212234544 01212234517 01212234527 01212234537 01212234547 01212234557 Iam given 5 phone...

Group by help - Hi all This seems like a simple thing to be able to do but I am really struggling with it Some...

Query Help - below is the code [code="sql"]DROP TABLE Class,Amount create table Class ( code int, CName varchar(10) ) create table Amount ( code int, Currency char(3), Amount float ) insert into Class select 1 , 'ASD'...

DB Normalisation Help required - I have the data as below. I need to normalise the table with this data.. can someone help me how...

FOR XML and trying to avoid nested cursors - Alright, history first. I've got a bit of a mess I've inherited. Groups inheriting groups inheriting... you get the drift....

Custom order in SELECT - Hi, How could we change the order in SELECT command based on a custom semicolon delimited phrase. We have : [Letters] ---------- a b c d e And based on...

Deletes taking long time - I have a situation where I created I ran a query that generated about 32,000 individual deletes that I am...

XML issue converting table to XML - Hi, Thanks for your help in advance. I am trying to get the following data [code="sql"]CREATE TABLE [dbo].[TABLE_XML1]( [ID] [int] IDENTITY(1,1) NOT...

Select full month number (with 0) - Hi, I use [code="sql"]DATEPART(month, myDate)[/code] or [code="sql"]MONTH(myDate)[/code] the resut is: 2 (if myDate is 2013.02.03). I would like return: 02 Do you have any solution for...

Guidance on table valued types. - We have 1 table valued parameter (tvp) coming to GetXXX stored procedures. It contains only 1 column of type uniqueidentifier....

Grouping records by time interval - Hi everyone, I have been searching for a few days now to try to solve a problem, but without success. What...

UDF Help - Hi Guys, I want to create UDF. Below is my logic. Please guide me where i am wrong. Alter Function [dbo].[udf_GPList](@EID int,@PID...

Time - Adding minutes and seconds - I have the 2 columns with time datatypes. select top 5 [Connect Time2],[totalTime2] from dbo.verizonDetails Connect Time2 totalTime2 08:05:44.0000000 00:13:00.0000000 08:05:57.0000000 00:01:00.0000000 09:07:42.0000000 00:03:00.0000000 09:07:46.0000000 00:09:00.0000000 09:08:08.0000000 00:01:00.0000000 I want to add the...

SQL Server 2008 : SQL Server Newbies

Case Statement - DateDiff and DatePart - I have been asked to come up with a case statement that gives me the date of birth, no I...

can this update statement be made easier - I am running an update statement as follows UPDATE dbo.BigTable SET BigTable.Software_Version_Raw = ( CASE WHEN dbo.BigTable.Software_Version_Raw LIKE '1.%' THEN '1.x' WHEN dbo.BigTable.Software_Version_Raw LIKE...

Group by and Where clause - Hi, Please see below, Table name : Log_table Columns: Logid int, databasename varchar(30), tablename varchar(30), logdate datetime Query 1: Select max(logdate) from log_table where...

Select.. where column1 in (@var) - What am I missing? Why would this work SELECT * FROM #MainQuery WHERE column1 IN ('Value1','Value2') and this not? declare @Var varchar(100) set...

Sql Server 2005 doesn't connect for windows xp SP3 - I have installed sql server 2005 in Windows XP SP3. Installed successfully but [b]a) server didn't connect gave error message...

SQL Server 2008 : SQL Server 2008 High Availability

Replication Newbie - Hi all I have set up snapshot replication between 2 instances on the same server (For testing) The snapshot job runs...

dbmirrroring - can two mirroring sessions on single production server between diferrent sql instances use same endpoint and same port numbers eg: DBprincipal1....DBMirroring1 endpoint...

Installation Error: Volume does not belong to the cluster group - I am installing SQL 2008 R2 cluster in windows 2008, I got the following error: The volumne contains SQL server data...

SQL Server 2008 : SQL Server 2008 Administration

resource governor calculation for min and max utilization - Can anyone explain how to calculate the resources for different resource pools gone through BOL bit not actually getting the nderstnding?what...

SQL Server doesn't accept remote connections. - Hello, I am trying to use my SQL Server in my Perl application. I am hosting my Perl code on an...

Problem with Database mail - We have SQL SERVER 2008 R2,which Database mail does not work properly.When I try to send test mail,it says it...

CPU considerations for multiple instances - I've been researching this and haven't found much info so I'm not sure what most people do. Consider a server...

setup SQL alert notify DBA about windows event log error - Is there a simpler way to setup SQL alert in SSMS to notify DBA when an error log is written...

CDC and LSNs - Hi peeps, bit of a departure from my norm I am looking at using CDC from source systems to populate a...

Does the adrenaline rush ever subside when rebooting a DB server. - I work in a small shop as the accidental DBA and for the first time rebooted our production DB server...

Can't Kill SPID “Transaction Rollback in Progress” - I'm running into frequent Blocking on a Development Server. I have been correctly the code or making recommendations to prevent this...

Database Backup Issue - Hello All, I'm running SQL 2008 R2. I have an intermediate issue with my backup for this one database. Some days...

Can not connect to SQLSERVER - Hi, i've installed SQL Server 2008 R2 in my system and after that i've updated the 'sa' account using the...

Career : Certification

70-466 - Can anyone give me any guidance on training kits for 70-466 exam?Thanks in advance for your help.

Career : Employers and Employees

BI Specialist vs SQL Developer - Hi all. I was recently offered for SQL developer position and BI specialist position, and I would like to get...

Programming : Connecting

Execute a sql job from AS400 command line - I am trying to use this command to execute a sql job from an AS400 command line. I am having...

An error has occurred while establishing a connection to the server - [i]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may...

Programming : XML

OPENXML error converting nvarchar to numeric. - Please take a look a the following.I do a select for OPENXML but it returns with Server: Msg 8114, Level 16,...

Programming : Service Broker

Service Broker between database on different instance but same domain. - Can SSB be enable between databases on different instances on same domain/network without the use of Certificates, Keys etc ?

Programming : Powershell

Risks/Best practices on execution policy - While it would be easy to set the execution policy to unrestricted, or possibly set it to unrestricted in an...

SQLServerCentral.com : Anything that is NOT about SQL!

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...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

SQLServerCentral.com : SQLServerCentral.com Website Issues

"My Recent Posts" - Can not find - I used "My Recent Posts" to check if I replied to and others replied to me at the topics I...

Reporting Services : Reporting Services

SSRS. Parametres mdx script doesn't work with multi-value - Hi ALL, I need your your help!! I want to create a report with multi-value parameters with SSRS. So, I wrote this mdx...

Report Errors for a view employees on Report Server, Not in VS2008 - I have a report that prints earnings statements for over 4,000 employees. Of those 4,000, 110 are aborting with the...

Reporting Services Error - There is not enough space on the disk. - Anyone have an idea why I get this error?  It occurs several places such as when I try to click...

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

Database describing databases and references to tables - Hello, I asked a question at dba.stackexchange.com [url=http://dba.stackexchange.com/questions/35855/database-describing-databases-and-references-to-tables]here[/url], that's not getting a lot of interest. Now that it has fallen...

Doubth in using Default Constraint... - Hi, I am creating a new Database. In this i will have a column for to store current Date. I can specify...

Data Warehousing : Integration Services

Twice number of reows loading from sql table to Excel. - Hi, I am having sqltbl which contains empid,Lname,Fname,Sal records. I am simply fetch all records from table to excel using ssis.While execting...

How to automate validation process - Hi, We have the following scenario: We receive CSV files every month for which SSIS packages were built to process the...

Data Warehousing : Strategies and Ideas

SSIS package for DWH Load - Hi All, Please suggest the best option for designing SSIS package for DWH load. Here is the scenario. I have two star schema...

Data Warehousing : Analysis Services

How to attach/install an analysis services database - Hi guys, I need some help with attaching an AdventureWorks analysis services database. I am trying to attach it but am...