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

Big Data - Cost of acquiring skills

I was privileged to attend the QCON London 2014 conference in early March.  The value of attending such a conference is that you are exposed to ideas far beyond the world of SQL Server and those ideas shock you into a new way of thinking.

Some of the comments on Big Data by Matt Asay of MongoDB really struck home.  To paraphrase Matt, Big Data is all about exploration and discovery.  Inherent in an experimental approach is that you are going to find a lot of theories that do not pan out.  Matt included a quote from Thomas Edison on inventing the light bulb in his presentation  “I have not failed. I've just found 10,000 ways that won't work.”

Imagine investing a 6 figure sum in a high end analytics platform or data warehouse appliance only to disprove 10,000 theories!  I cannot imagine senior management enthusing about the return on investment.

Matt made the comment that open-source software allows you to dip your toe in the water with Big Data at very low cost.  Reducing the cost of experimentation is crucial to nuturing an environment with a decent probability of getting value from Big Data. Without the high cost imperative on achieving success and achieving it early there is no incentive to game the system.  There is no need for ugly blamestorming sessions or to try political spin to excuse failure and avoid getting fired!

Ultimately open source software is NOT free, after all companies and individuals who produce the software have to feed their kids, pay the bills just like their proprietary bretheren.  Chances are the free community editions of the products are feature reduced but what does that matter?  You can make a great deal of progress and then determine whether a big ticket spend is required.

And yet low cost experimentation is not just the preserve of the open-source community.  Yes SQL Express is a free download but at around £40 SQL Server Developer Edition is a throttled back version of the full blown enterprise edition.  To me £40 for the chance to learn about the full features of SQL Server represents good value for money.  My only constraint is time.

To wrap this up I should like to leave you with a closing quote from Thomas Edison.  “Many of life's failures are people who did not realize how close they were to success when they gave up.”

David Poole from SQLServerCentral.com

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

ADVERTISEMENT
Ricky Leeks on Learning .NET Memory Management

Ricky Leeks on Learning .NET Memory Management

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

SQL Monitor

An accidental DBA? Try SQL Monitor

Use the 30-day full product free trial for easy-to-understand insights into SQL Server, and get suggestions on how to solve the type of issues that are uncovered. Begin your free trial.

SQL Developer Bundle

12 must-have SQL Server tools

The award-winning SQL Developer Bundle contains 12 tools for faster, simpler SQL Server development. Download a free trial.

Featured Contents

 

SQL Server 2014 Key Features and Enhancements

Suresh Yaram from SQLServerCentral.com

SQL Server 2014 comes with a set of enhancements as compared to its processors that brings a lot of business benefits to the applications in OLTP and OLAP environments. This articles highlights a few key enhancements that are built into the product. More »


 

SQL Server Reporting Services Embedding .NET Code for Report Formatting and Error Handling

Additional Articles from MSSQLTips.com

I want to embed code into my SQL Server Reporting Servers (SSRS) to allow special formatting for report output. How do I implement and deploy this code and what functionality can use this embedded code? More »


 

From the SQLServerCentral Blogs - SSIS XML Task

MikeDavis from SQLServerCentral Blogs

The XML Task in SSIS allows you to parse through an XML file and read the nodes in the XML.... More »


 

From the SQLServerCentral Blogs - Using BMC Control-M to Automate Tasks

Tim Radney from SQLServerCentral Blogs

My largest client recently purchased BMC Control-M to use as our enterprise scheduler. Since this product went live in our... More »

Question of the Day

Today's Question (by Andy Warren):

Background: The XYZ corporate database team has decided to re-engineer their backup process by building a queue table in MSDB that will hold all of the databases that need to be backed up on each instance. They are running SQL 2012 Standard Edition with the latest patches applied. The table has been created and now the lead DBA is doing some validation.

She starts by running the following query to check the data in the table:

select * from backuplist

The query returns 40 rows. She joins backuplist to databases and still gets 40 rows returned. Looking at the design, the backuplist table has a primary key on name, but no foreign key on name. Before adding the foreign key she checks for non-matching rows by running this query:

select * from backuplist d left join databases b on d.name = b.name where b.name is null

This query returns 0 rows. Good! Now to add the foreign key she executes the following:

ALTER TABLE [dbo].[databases]  WITH CHECK ADD  CONSTRAINT [FK_backlist_backuplist] FOREIGN KEY([name]) REFERENCES [dbo].[backuplist] ([name])
The alter fails. She double checks the data check query and runs it again:
select * from backuplist d left join databases b on d.name = b.name where b.name is null
It returns 0 rows, confirming no one has changed the data in a way that would prevent adding the foreign key. She re-executes the alter statement to add the foreign key and it still fails. 
Why can't the foreign key be created?
Hints:
  • It's not a syntax error in the alter statement
  • If you could see the error message the problem would be obvious
  • There is nothing unusual about the instance
  • The primary key is a single column (name)
  • All objects discussed are part of the dbo schema

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 3 points in this category: Foreign Key.

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

Professional SQL Server 2012 Internals and Troubleshooting

The 2012 release of SQL Server is the most significant one since 2005 and introduces an abundance of new features. This critical book provides in-depth coverage of best practices for troubleshooting performance problems based on a solid understanding of both SQL Server and Windows internals and shows experienced DBAs how to ensure reliable performance. The team of authors shows you how to master the use of specific troubleshooting tools and how to interpret their output so you can quickly identify and resolve any performance issue on any server running SQL Server. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Lara Rasner):

The question refers to the following simplified stored procedure:

CREATE PROCEDURE dbo.TestProcedure
AS
BEGIN

 SET NOCOUNT ON;

 CREATE TABLE #table (Id INT NOT NULL);

 INSERT INTO #table (Id) VALUES (42), (21), (590);

 SELECT Id FROM #table;

END;

Select the two answer below are that are TRUE:

Answer:

  • The #table created inside a stored procedure is held in cache, even after the execution of the stored procedure.
  • Any auto-created statistics relating to #table are held in cache after the execution of the stored procedure.

Explanation:

Both 1 and 2 are false. Contrary to popular belief, once a temporary table is created inside a stored procedure, the table is renamed to an internal form and then renamed back to its user created name when CREATE TABLE #table happens during the next stored procedure execution. This table (and its statistics) are held internally and even if specifically dropped OR the connection is ended the table id AND statistics can be held in cache.

Both 3 and 4 are correct! You can verify this for yourself by following Paul White’s blog post here: Temporary Tables in Stored Procedures

Credit for this question of the day goes to Paul White. His blog post goes into detail about how you can verify the answers for yourself here:  Temporary Tables in Stored Procedures


» Discuss this question and answer on the forums

Featured Script

Insert Generator - Stored Procedure

Nick Greene from SQLServerCentral.com

SQL server management studio interface provides the ability to create insert scripts for tables, but I needed a way to automate this insert script building process. I ended up writing my own stored procedure to allow me to do this. It accepts 5 parameters:

  1. @schemaName - The schema the table belongs to
  2. @tableName - The name of the table
  3. @IncludePrimaryKeyIdentity - True/False flag to include a primary key that is an identity value.  Defaults to False because typically you would want this to self generate.
  4. @IncludeNonPrimaryKeyIdentity - True/False flag to include a primary key that is not an identity value.  Defaults to True because if not auto generating you would want to include this.
  5. @IncludeIfNotExists - True/False flag for whether to include IF NOT EXISTS() statement around each INSERT statement.

I have not built this to support ALL data types, but it will support, what I consider to be, the most common data types.    If you attempt to execute it on a table that contains an unsupported datatype it will error out and inform you it does not support the datatype.  I use it primarily in SQL 2008, but it should work in 2005.  I have not tested it in 2012, but I would expect it to work.

I have also posted this on my blog at sqlprosperity.com

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

What are pre requirements for starting replication in sql server 2008 - What are pre requirements for starting replication in sql server 2008

Snapshot Replication having problem - Hi All, Today I was configuring Snapshot replication just for my demo purpose. My snapshot folder is not creating because its...

Linked Servers to MySQL Login Timeout - Hi, I want to set up a Linked Server between my mssql 2014 and a MySQL. I set up the system dsn...

License SQL 2014 - Cores - Hi - Hypothetical situation: I have a 32 core server. SQL 2014 enterprise installed. The server licensed for 32 cores. Question raised - can...

High Memory is 70% & growing Fast - HI Experts, My database server memory utilisation is growing faster from past 1 week. it remained same for 1 week around...


SQL Server 2014 : Development - SQL Server 2014

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

Null instead of 0 - Friends, I have a excel sheet with some data and blank columns. I have a ssis package using to import...


SQL Server 2012 : SQL 2012 - General

Time out Errors - Hi, I want to find out the time out errors for a particular SP. Please let me know how to check...

How do i change a string key to an int - normal methods not working! - did VARCHAR(20) SELECT top 10 CAST(did AS INT) FROM table Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the...

To find the SQL Statement - Hi, Might be you feel like class room question but hoping some one will give answer. 1)What is the plan_handle, query_hash & query_plan_hash?...

Webservices Deployment - Hi, Please some one let me know how to deploy web services on local machine. I created self certificate in IIS...


SQL Server 2012 : SQL Server 2012 - T-SQL

Sending data from MS SQL Server do PostgreSQL server using CLR triggers - I need to send data from a MS SQL Server table to a PostgreSQL table on INSERT/UPDATE. I was thinking...


SQL Server 2008 : SQL Server 2008 - General

Total Server Memory Of SQLServer - Hi Our server has the total memory almost 25 G and the memory that sql server has in task manager is...


SQL Server 2008 : T-SQL (SS2K8)

Convert the stored procedure to "Standard SQL Select" - I have a very simple stored procedure to be used in Dundas Dashboard: [code="sql"] ALTER proc [dbo].[ddGetCurrentOpenAndClosed] AS declare @Open int declare @Closed int select @Open...


SQL Server 2008 : Working with Oracle

Oracle Failover from Primary to Secondary Server (SSIS) - I have some packages that have Oracle as their Data Source. I'm not clear as to what I do and\or not...


SQL Server 2005 : SQL Server 2005 Integration Services

Help with Derived Column To Split-Up An Individual's Name into Separate Parts - I'm working on an SSIS package for a client. They receive files exported from their clients and they don't put...

Need to check file name before it get process - Hi, I need help in checking the file name before i load it to staging tables. I have all the file stored...


SQL Server 2005 : T-SQL (SS2K5)

Multiple aggregates using PIVOT - Hi all, Am working on a reporting project which extensively uses the backend stored procedures. We're using SQL 2005 as database. I...


Data Warehousing : Integration Services

Expression to convert datetime to UTC. Is there a better way? - Hi, I have a SSIS 2008 package that is loading audit and logging information into a 2008 R2 sql server database. My...

Question: How do I pervent Chinese characters from turning into ? (SSIS 2008R2) - Ok, I've been confronted by this problem during the week where I'm the only guy not on break on our...


SQLServerCentral.com : Articles Requested

Truncate and Restores - I am looking for a basic article that shows how truncate affects restores. - a table - a backup with data in...

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