SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

The Internet of Things

The Internet of things is upon us. I've heard this term more recently, and certainly there's been nor shortage of new hardware items that have been connected to the Internet. The last ten years have seen all sorts of devices get IP addresses: mobile phones and cars, along with washing machines and refrigerators. Some have been silly attempts to make a connection without a practical problem being solved.

However I think we will truly start to see more and more devices created in the next decade. The advent of cheap hardware (Raspberry Pis and Arduinos as two examples) and the ability to construct new cases and programs will lead to many individuals, as well as companies, starting to build their own devices to capture data as sensors, or perform small tasks that might produce logs or other status information.

Should we care? Yes. Much of this data will be stored in databases. Perhaps in relational platforms like SQL Server, perhaps sampled and queried as a stream with much of the data discarded (using StreamInsight and SQL Server), perhaps stored in some NoSQL type platform (HDInsight/Hadoop, anyone?). For many of us that means more data to manage, new information to develop software against, new patterns to discover with creative queries.

More work, probably more employment, and hopefully, more money.

Steve Jones from SQLServerCentral.com

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


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 1.9MB) podcast or subscribe to the feed at iTunes and Mevio . feed

The Voice of the DBA 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.

Everyday Jones

Follow Steve Jones on Twitter to find links and database related items and announcements.


ADVERTISEMENT
Learning .NET Memory Management Ricky Leeks on Learning .NET Memory Management

Pick up all six articles in one free download. Find out what Ricky can teach you about garbage collection, memory management gotchas, and more. Download the article pack free.

ADVERTISEMENT
Person with baby, in awe

SQL Developer Bundle

Begin a new chapter of your career equipped with everything you need.

sqldbabundle

SQL Compare

sqldbabundle

SQL Source Control

sqldbabundle

SQL Test

sqldbabundle

SQL Prompt

sqldbabundle

SQL Doc

Get all these and more with a free trial of the SQL Developer Bundle.

Find out more

Featured Contents

 

SQL Server 2012 AlwaysOn Groups and FCIs Part 3

Perry Whittle from SQLServerCentral.com

Part 3 of the AO and FCI integration series. This article gets SQL Server configured on the nodes and ready for work. More »


 

How to recover a suspect msdb database in SQL Server

Additional Articles from MSSQLTips.com

A SQL Server MSDB database is flagged as "suspect"... what can you do? John Grover has some suggestions More »


 

Data Science Laboratory System - Distributed File Databases

Additional Articles from SimpleTalk

Distributed File Databases manage large amounts of unstructured or semi-structured data. Buck Woody shows how to install Hadoop in your Data Science lab to experiment with an example of the breed. More »


 

From the SQLServerCentral Blogs - 5 Tools Every DBA Should Know About

Thomas LeBlanc from SQLServerCentral Blogs

After monitoring enterprise SQL Server instances for over 10 years now, there are 5 tools that are used every day... More »

Question of the Day

Today's Question (by Anoo S Pillai):

How many rows would be outputted from the select statement with 'tablesample' clause given below? 


USE TempDB
go 
CREATE TABLE #Emp
    (
      empID INT
    , eName VARCHAR(100)
    )
go 
INSERT INTO #Emp
        ( empID, eName )
    VALUES ( 1, 'eName1' ) 
INSERT INTO #Emp
        ( empID, eName )
    VALUES ( 2, 'eName2' ) 
INSERT INTO #Emp
        ( empID, eName )
    VALUES ( 3, 'eName3' ) 
INSERT INTO #Emp
        ( empID, eName )
    VALUES ( 4, 'eName4' ) 
INSERT INTO #Emp
        ( empID, eName )
    VALUES ( 5, 'eName5' ) 
go 
SELECT empid
      , ename
    FROM #Emp TABLESAMPLE (4 ROWS)
go

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


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

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

ADVERTISEMENT

Expert Performance Indexing for SQL Server 2012

Expert Performance Indexing for SQL Server 2012 is a deep dive into perhaps the single-most important facet of good performance: indexes, and how to best use them. The book begins in the shallow waters with explanations of the types of indexes and how they are stored in databases. Moving deeper into the topic, and further into the book, you will look at the statistics that are accumulated both by indexes and on indexes. All of this will help you progress towards properly achieving your database performance goals.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Andy Warren):

You arrive at work to find an urgent request in your queue: Create a table for Questions that will contain the question title, date it was last changed, and some kind of approval column. Need it before the 9 am stand up so we can discuss with the team.

Springing into action, you start by running the following:


CREATE TABLE Questions
    (
      QuestionID INT
    , QuestionTitle VARCHAR(100)
    , datechanged DATETIME
    , IsApproved BIT DEFAULT 0
    )
go
CREATE TRIGGER updateQuestions ON dbo.Questions
    FOR UPDATE
AS
    UPDATE Q
        SET Q.datechanged = GETUTCDATE()
        FROM inserted i
            INNER JOIN dbo.Questions Q
            ON I.QuestionID = q.QuestionID
go
INSERT INTO Questions
        ( QuestionID
        , QuestionTitle
        , datechanged
        )
    VALUES
        ( 1
        , 'Select me!'
        , GETUTCDATE()
        )
go
INSERT INTO Questions
        ( QuestionID
        , QuestionTitle
        , datechanged
        )
    VALUES
        ( 2
        , 'Tables and Columns, Oh My'
        , GETUTCDATE()
        )
go

You're about to test updates and work on performance (you haven't added any indexes yet) when the gong rings for the stand up.

You attend the meeting, everyone admires your table design, and you let them know you'll email when you've completed your testing a few minutes after the meeting ends. You return to your cube and pick up where you left off by writing and executing this statement:


UPDATE Questions
    SET IsApproved = 0
    WHERE QuestionID IN ( 1, 2 )

The query appears to affect a total of three rows and returns an error message. You are able to fix it two different ways. You immediately think "self, this would be a great question of the day!" and add it to your todo list to write it up.

Which of the following is true? (select 5)

Answer:

  • The table does not have a primary key
  • There are 2 rows in the table
  • The trigger does not contain a logic error
  • The query plan for the update shows a table scan
  • A resultset is being returned when the update runs

Explanation:

The first answer, The table does not have a primary key, is correct, something we can easily see from the create table statement.

The inserts succeed, so There are 2 rows in the table is correct and There are 0 rows in the table is false.

The trigger does not contain a logic error is correct, it's a vanilla trigger, which means we can rule out The trigger contains a logic error.

The query plan for the update shows a table scan is correct. There only two rows in the table and the table has no indexes, so brute force is the approach.

The update is returning a resultset is also correct, but it's not at all obvious why - it's because the query plan is being returned. Remember the part about performance testing (so you would be looking at the plan) and also the answer about the table scan should have gotten you thinking about it too.

The update can be fixed by adding SET NOCOUNT ON won't fix the problem, but if you picked this you were thinking in the right direction.

So what's really going on here? Here is what was returned from the update:

(2 row(s) affected)

(1 row(s) affected)

Msg 524, Level 16, State 1, Procedure updateQuestions, Line 6

A trigger returned a resultset and the server option 'disallow results from triggers' is true.

The default for that setting is false, but it in this case it was enabled (and it's never a good idea to return results from a trigger).

To fix the update, one option is to change the disallow results option to false:

sp_configure 'disallow results from triggers', 0

reconfigure

The other option is to just turn off the query plan (Control-M) viewing. It's interesting that something we do as a matter of course could cause otherwise valid code to fail!

You might also note that I said a "total" of three rows to throw you off and give you a hint at the same time - 2 rows from the update, 1 row for the query plan.

Read about the disallow results option here: http://technet.microsoft.com/en-us/library/ms186337.aspx and a credit to http://stackoverflow.com/questions/5505832/a-trigger-returned-a-resultset-and-the-server-option-disallow-results-from-trig that I found when I ran into this issue and it had me scratching my head!


» Discuss this question and answer on the forums

Featured Script

IndexInformation

m mcdonald from SQLServerCentral.com

Retrieve Index Information for all tables.

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 2014 : Administration - SQL Server 2014

Ideal table for Memory Optimization ? - I have a simple id, name table with six million records, that i converted to a "memory optimized' table. It now...

SQL Server 2014 Developer Edition - availability for download ? - Does anyone know when SQL Server 2014 Developer Edition will be availability for download ? I thought SQL Server 2014 was...

SQL DBA course / Books - Hello, I have been working on MS SQL since SQL 2005 however just the basics like installing, changing the ports, running...


SQL Server 2014 : Development - SQL Server 2014

The new 3D Joins in SQL Server 2014 - Has anyone tried out the new 3D joins in 2014?

How to update the year an employee has been in a specific TITLE - --===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable --===== Create the test table...

Database and its Objects Naming Standards - I am trying to establish the standards for naming convention in my new project. Can you please provide me the...


SQL Server 2012 : SQL 2012 - General

Convert - What actually happens when I convert date to float? Select convert(float,getdate(),112)

Need link for SQL Server 2012 Developer Edition TRIAL / EVALUATION copy - Need link for SQL Server 2012 Developer Edition [b]TRIAL / EVALUATION[/b] copy (eg. 180 days) - we are licensing 20 copies of...

SQL Statement not displaying results with zero value - Hello, I am currently working on a project to display the pricing of our products, as mentioned above though the discount...

Intellisense Issue - Let's say I have a table called Customer with a field called cname. In SQL 2008 R2, if I type "select...

SQL Server Certification - Is there an alternative to Prometric? - From searching the forums, it seems my experience with Prometric lives up to that experience by many others, so there...

Question about SQL2012 availability groups & security - How should application/users security be set up? If server #1 fails, availability group #1 fails over to server #2 but...

IDENTITY(1,1) doesn't work, from 17 jumped to 1001 - Hi, I have a table with PK column defined like IDENTITY(1,1), it has 16 entries, now after series of migration and...

Could having SSMS open for long periods of time, cause locking? - About an hour or so ago I had all of my users come to me, complaining that their application had...

installing Sql 2012 Evaluation version on Windows 7 Pro - I have spent the past 3 days trying to get a clean version of SQL 2012 installed on my laptop. I...

Like operator - Query performance issue - Dear All Have a table with 15 million rows. And Almost 15 columns. I am using following query. which is very...

Procedure to upgrade and rollback Sql Server 2012 to Sql Server 2012 SP1 - Hello, I am planning to upgrade my database from Sql Server 2012 standard edition to SP1. What is the step by...


SQL Server 2012 : SQL Server 2012 - T-SQL

Invalid Sql returns all rows - I discovered this today and was hoping someone had an explanation. I forgot that a table I was attempting to...

Migration from 2008R2 to 2012, linked servers error - Hi! We are preparing to migration from 2008R to 2012 and we encountered with query errors to Oracle linked servers. This query...

SQL Transpose / Pivot - Help - PS: The attachment has a screenshot of the tables described below which is easier to read Hi All, I am stuck on...

Query Governor 2012 - working when switched off ?!? - I am testing 11.0.3128 on a 12 core machine - this is a default SQL2012 installation on WindowsServer2008R2. I created 12 T-SQL...


SQL Server 2008 : SQL Server 2008 - General

98% Memory usage SQL Server 2008 R2 - Been monitoring SQL server 2008 for a bit now and seems the memory usages sits between 95-95% memory usage. After...

Can't drop index due to FK enforcement - Hi All, I came across a curious problem that I've never encountered before and thought I'd ask the SQLServerCentral Brain Trust...

Potential Issues Modifying Compatability Level? - I would like to change the compatability level of some of my databases from 80 to 100. Do you know...

Best way to expose msdb information - One client application needs to know the run time and run status of a specific job. What is the best...

SSRS Error in Even viewer and SSRS server log. - Hi All, I am running SSRS 2008 SP1 on Windows server 2008R2 standard SP1 and when I try to load the...

Execution Plan what to look for - Hi, I have two sql queries both looking at the same table but the difference between the two is the where...

Blocking Session Information - Hi I know it isn't possible to retrospectively get information on sessions that were being blocked, is there a way to...

update teakes more time - how long will a update statement will take to inish its job on some 5 million records. I am having table...

Blocking and WRITELOG wait type - Hi We had an event whereby the app went slow slow for about 1 minute. I checked on the wait stats on...

SQL Server JDBC issue - Hi All, AM trying to export a dataset of 1 million records from Hadoop to sql server using sqoop which internally...

logical consistency-based I/O error - SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 4:1738920; actual 256:10100480). It occurred during a read of...

which one to go with (normal index vs Composite index) ? - Hi there ... Which one you will prefer & why you prefer it ? [code="sql"] create index idx on TableName (col1,col2,col3) or create index...

Excessive log growth. - Hi Team, We are using SQL 2008 R2 database for SAP Application. With in this We are maintaining DR server for...

Need query to sum up the previous values - Hi There... need query for the following ... [img]http://www.sqlservercentral.com/Forums/Attachment15287.aspx[/img] Sample table script [code="sql"]create table #sample (id int identity ,value int) insert into #sample values (3) insert...

SQL User -> Domain - Good Morning Ladies and Gents, this is more of an enquiry that a problem. I have several users who in a...

SQL SERVICE WON'T START - While trying to move the MSDB and Model DB, I mistakenly ran the following ALTER DATABASE model MODIFY FILE (NAME = modelData, FILENAME...

DBCC FORCEGHOSTCLEANUP - Hello Does anyone know what this undocumented SP does? the ghost cleanup task under normal circumstances runs every 5 seconds and cleans...

Get the hierarchy orders(top to bottom) of the table - Can we get the hierarchy orders of table Eg: 10 tables Table1 is master to table2 and so on till table10 Just...

Shopping Cart Status Change - Using a self developed a highly specialized shopping cart in (VB.Net) with SQL Server 2008. “Cart” data ([CartID],[CartNumber], [Status], [StatusDate]) is...

Auto Growth Settings - I'm just looking for some guidelines on how everyone determines what to use as their auto growth settings for system...

Issue Access Named Instance - Hi - I have recently updated my memory allocation using SQLCMD. I increased it to 4036 which should be enough memory...

Flexible Recursive Level Splitting - One column per level - Hi All, see code below, using a small sample of data. I have a table that is formed like Id/ParentId/Name, but I...

GUID Pirmary key - Hi, Can we have a GUID Column to primary key with nonclusterindex? Is nonclusterindex is needed while we run the GUID column...

Need to migrate a databases from DB2 to SQL server - Hi, Need a help in Migrating a database of 100 GB from DB2 to Sql server. Client would extract the data from...

How to get my PDFs from Table (datatype Image) - I have a lot of PDFs stored in a table, and it works fine when extracting (and displaying) them using...

Database lost after server unexpected shut down - We have a cloud server at an ISP. Yesterday the server unexpectedly shut down (I can see that in the...

SQL 2008 Mirroring - First Time - Hey guys, I'm attempting to setup a mirror between two sql 2008 servers for the very first time. On my principal...


SQL Server 2008 : T-SQL (SS2K8)

How do I select all or multiple rows from a typed xml variable? - I am using xml schema that is like this: [code="xml"]<DetailRows> <DetailRow> <MonthNumber></MonthNumber> <Amount></Amount> </DetailRow> </DetailRows>[/code] If my variable contains following xml document as...

Help need in Avoiding Loop - Hi, Here is my table structure: [code="sql"] ;with Users as ( select 1 as UserId, 2 as PendingAmount,10 as AvailableAmount union all select 2 as...

INSERT INTO with HierarchyID - Hi all, I feel like this should be an easy question to find the answer to, but google searching yielded no...

sp_send_dbmail has formatting off on datetime when directing output to csv file - I am running this sql which creates a csv file and sends the output via email. One of the columns...

selecting top 1 from multiple ranges... - So I have data like the following: [code] ID COUNTER DATA 1 10 BLAH 1 20 BLAH 2 10 BLAH 3 10 BLAH 2 20 BLAH 2 30...


SQL Server 2008 : SQL Server Newbies

Compair between string into Store Procedure - Hello friends please if you can support me as I'm trying to make a comparison of strings but it seems...

Exporting Column Headers to Excel - Hi Guys, I have to complie a list of column names and their data types in excel, is there anyway to...


SQL Server 2008 : Security (SS2K8)

Restroring Encrypted Production database to Encrypted Dev database - I have a production instance of SQL Server 2008 R2 running Master Data Services encrypted using using a certificate named...


SQL Server 2008 : SQL Server 2008 Administration

CHECKTABLE - Hi Experts, What exactly happens when we run a CHECKTABLE? What happens if we stop the same in between? TIA

Weird problem in database backups - Got a bit of an odd one here. A client has SQL 2008 R2 Express installed to support our product--since...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Consolidating Indexes - Hi Guys, I have an index consolidating issue which I have not been able to find a definitive answer to. I have...

LINQ vs SQL Questions - I've run into issues in the past dealing with extremely poorly written queries with LINQ. I've been under the impression...

SQL INSERTS are significanlty slower on newer server - We currently use SQL Server 2005 that is on a Windows 2003 32 bit machine with max RAM (4 GB...


SQL Server 2005 : Administering

The SQL Server service terminated - The SQL Server service terminated with the following error: Ran out of memory OS details: windows 2008 enterprise SQL Vesrion: sqlserver 2005...

SQL Server Agent : Job Failure - Hello, I wish to put the result of a query in Excel then send it to a number of persons at...


SQL Server 2005 : Backups

Backups - Is it possible to have SQL1Server backup the databases on SQL2Server? If so I guess it have to be scripted...

Will changing the recovery model on the fly prevent ability to restore properly - We have a very large import job on a critical database. At the start of the job we set the...

Backup failure - Need your help to resolve below error. Error:- Unable to start execution of step 1 (reason: The SSIS subsystem failed to...


SQL Server 2005 : Development

Triggers Examples - Hi, Can any one send me the simple examples of using Instead of Trigger and After Trigger with Northwind database...


SQL Server 2005 : SQL Server 2005 General Discussion

Tempb db size identification - Hi, There is version store size counter in transaction category of performance monitor , and there is version_store_reserved_page_count col in dm_db_file_space_usage Q1)...


SQL Server 2005 : SQL Server 2005 Strategies

Alter A Partitioned Table Column DataType. - The BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/5b21c53a-b4f4-4988-89a2-801f512126e4.htm [b]Partitioned Tables[/b] In addition to performing SWITCH operations that involve partitioned tables, ALTER TABLE can be used to change the...


SQL Server 2005 : SQL Server 2005 Integration Services

Derived Column Transformation not filtering as expected in SSIS 2008 Data Flow - Hi All, I have the following Expression working on a Derived Column in an SSIS Data Flow Transformation [quote][Date_Start] == " 0 0- 0-...


SQL Server 2005 : T-SQL (SS2K5)

Convert Row Data in 1 Field to Many Columns - Hello this is my first post and my TSQL is average at best so any help will be much appreciated I...

Nesting xml - Hello, I am working on xml from SS2k12 results. I can get it to work with for xml auto; elements but...


Reporting Services : Reporting Services

Stored procedure returns proper results but report preview won't if... - I have a stored procedure that accepts two float parameter values used to filter results. I want to allow the...

Why do available fields have Sum function applied? - Hello, I am working on a report project in Visual Studio 2005. (I'm modifying the form and it has a lot...

Where do I add the "Dataset" Name in Expression - Hi, I have following expression in SSRS expression builder for a calculated field within a report. The report has several...

Column grouping weird behavior - Hi, I have report with row and column groupings for dataset like below, have CustID, AMT as row group YYYY, QUARTER as...


Reporting Services : Reporting Services 2008 Administration

HeatMap report problem SSRS2008 - Hi, I have an issue setting up a SSRS report to display a matrix using dynamic fill colour (Heat Map). I'm using...


Reporting Services : Reporting Services 2008 Development

Expression for execution time in report - Hello I am new to SSRS can anyone tell me how can I write the below format using expression? Apr 01,...

Adding too Many Blank Rows - I am working on a report that has a table with 8 rows. When the dataset is returned, I need...

Nested Aggregate Error - I need to get a sum of a column, but it needs to be the sum of only the visible...


Programming : General

Are cursors hard on servers? - I was reviewing a process with a colleague and mentioned cursors. He said, "Please don't use cursors as it is...


Data Warehousing : Integration Services

source code control for dtsx - Guys, We are about to embark on a large scale data warehousing project which will be run mainly on SSIS 2012....


Data Warehousing : Strategies and Ideas

Using surrogate keys for - I'm working on my first data warehouse project and was wondering what is the industry norm when it comes to...


Data Warehousing : Analysis Services

Trying to create Hierarchy in BIDS from DimTable - Hi - I am trying to create a hiearchy from a Dim Table linked to the SQL DB and I'm having...


Database Design : Disaster Recovery

How to sync linked server, jobs, alerts, logins, etc to a secondary serveur - hi everyone! I guest that this question as been asked many times in the past but i havent been able to...


Database Design : Hardware

Placement of SQL Server Data and TLog files in a SAN Infrastructure and Monolith Storage Environment - Hi Everyone, The reason for my post is to get others opinions, thoughts, and any facts regarding the placement of SQL...

My first proper server... - Hi, I'm looking to improve my Windows Server know how by getting my own server at home and installing SQL...


SQLServerCentral.com : Anything that is NOT about SQL!

SharePoint 2013 - I find this site extremely useful and I really like the email updates I get with tips and techniques, questions,...

DB2 DBA - I'm the resident SQL Server DBA for my company. They've asked me to self-study to become the resident DB2 DBA...

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

clicking 'Active THreads' link gives differnet results between Google Chrome and IE - I've just downloaded Chrome and was testing it out and noticed that when I clicked 'Active Threads' it said there...


SQLServerCentral.com : Articles Requested

XML Import - Looking for a basic article that imports a series of XML files in a folder into a table. There should...

Intro to Hadoop - basics, what is it, what's it used for.


SQL Server 7,2000 : Replication

Start SnapShot agent in T-SQL - SQL2005   I need to automate the starting of the snapshot agent.  At the moment i have to go into replication monitor right click...


Career : Certification

20467B Textbook (70-467 Prep course) - Does anybody know what book they're using for the 20467B course and where I can get it? This is a...

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


Career : Job Postings

SR SQL Server DBA - Springfield PA - Direct hire Employee - Sr SQL DBA Need a strong agile DBA to be involved in the build out/ re-engineering of and enterprise portal environment...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com