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

Can SQL Server do ‘Big’?

Today we have a guest editorial as Steve is on vacation.

Like many others, I  read the page on MSDN Maximum Capacity Specifications for SQL Server with a low whistle of amazement. 524,272 terabytes?  253 foreign key references per table?  2,100 parameters for a stored procedure?  Could anyone be getting anywhere near those limits?

I get to hear about, and sometimes come across, some enormous SQL Server databases. I’ve worked on, and occasionally designed, a few myself.  It has left me with a deep respect for the way that SQL Server copes, and the amount of work that is required to create and maintain a database system that can handle large amounts of data. I hope, therefore I can be forgiven a smile when a hopeful startup announces a new database system to rival the behemoths of the database industry, or when someone refers to a database as ‘Big Data’ when it would even fit in a spreadsheet

The databases I’ve dealt with are dwarfed by the titans that I’ve heard about through talking to colleagues at PASS or reading about in forums. Sometimes, I come across them by accident.  I once wrote a routine that automatically converted the deprecated Rules and Defaults into constraints. I tested it out on what I thought were some fairly large databases. I then got a complaint from someone who used it that the routine was much too slow. I blinked in wonderment. I hadn’t even bothered to optimise it since it ran in a twinkling of an eye. I should have guessed that it wouldn’t scale well when faced with a database with 60,000 tables. Fortunately, he fixed it to work fine with that sort of size of database.  I couldn’t help wondering how SSMS would cope with a database that size!

For various reasons, we don’t get to hear about all those huge SQL Server databases that work well. Which industry has the biggest systems, in terms of sheer volume, or in processing power? What are the types of database that scale easily, and what special techniques are required? I’d be fascinated to hear from people running really big databases successfully with SQL Server.

Phil Factor from SQLServerCentral.com

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

ADVERTISEMENT
deploymentmanager

Deployment Manager 2 is now free!

The new version includes tons of new features and we've launched a completely free Starter Edition! Get Deployment Manager here.

sqldbabundle

‘10 Tips for Efficient Disaster Recovery’

Steve Jones gives the final lesson in the ‘Top 5 Hard-earned Lessons of a DBA’. Read now and learn from the best.

SQL Source Control

Get your SQL Server database under version control now!

Version control is standard for applications, but databases haven’t caught up. So how can you bring database development up to speed? Why should you start? Find out…

Featured Contents

 

Automate Your Backup and Restore Tasks

Philip Horan from SQLServerCentral.com

A new article that shows how you can automate a basic function that many environments need: the backup of a production database and the restore on a development system. More »


 

SQL Server Reporting Services Report Manager Tips and Tricks

Additional Articles from MSSQLTips.com

Dattatrey Sindol shares his tips and tricks on SQL Server Reporting Services Report Manager. He provides guidance on Customizing Report Manager Header/Title, Uploading Custom File Types to Report Manager, and Enable My Reports Feature. More »


 

From the SQLServerCentral Blogs - Selecting an Appropriate Intel Xeon E5-2600 v2 Family Processor for SQL Server 2012

Intel has finally released the 22nm Xeon E5-2600 v2 Family (Ivy Bridge-EP) of processors that will be used in two-socket... More »


 

From the SQLServerCentral Blogs - Finding Ad Hoc Queries with Query Hash

Grant Fritchey from SQLServerCentral.com

I was presenting a session on how to read execution plans when I received a question: Do you have a... More »

Question of the Day

Today's Question (by sqlnaive):

What will be the output of the last Select statement:

CREATE TABLE dbo.T_TEST(Col1 DECIMAL(20,2), Col2 INT)
GO
INSERT INTO dbo.T_TEST SELECT 5000.50, 65
INSERT INTO dbo.T_TEST SELECT NULL, 100
INSERT INTO dbo.T_TEST SELECT 6666.25, NULL
GO
SELECT COALESCE(Col1, Col2) AS 'FirstNotNull' FROM dbo.T_TEST
GO
DROP TABLE dbo.T_TEST; 

Note: Code is tested in SQL 2008 version.

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

We'd love to give you credit for your own question and answer.
To submit a QOD, 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 Paul Cauchon):

What is returned by the select statements?

if 1=0 
 begin 
    declare @word varchar(100) = 'apple' 
    declare @table table(id int)
    select @word 
 end
else
 begin 
    select @word 
    set @word = 'pear' 
    select @word 
    select COUNT(*) from @table
 end

Answer: NULL, pear, 0

Explanation: Immediately after parsing, objects are validated by the query optimizer without respect to the logical TSQL constructs in the code. After all objects are bound, the algebrized tree (with all of its objects) is used to create candidate execution plans, now considering the TSQL logic.

Order of operations for the query optimizer: https://www.simple-talk.com/sql/sql-training/the-sql-server-query-optimizer/


» Discuss this question and answer on the forums

Featured Script

Display Databases with X days without backup

Gonzalo Moles from SQLServerCentral.com

This script helps you to know wich databases have not been backed up within the last X days. You can also see wich databases have never been backed up.

You must run like:

EXEC msdb.dbo.get_backup_info -X

where X are the days ago (0 gives all the information).

This is what is shown:

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 2012 : SQL 2012 - General

Disaster Recovery - SQL vs VM Disk Replication - We are evaluating approaches to DR. For SQL Server we have the usual log shipping, replication, and various 'AlwaysOn' methods....

SSRS 3.0 average - Hi all, I am trying to do an average of a calculated collumn in SSRS (matrix table), the column in...

Extended Events - Dear All I am trying to find out why some procedures are getting recomiled. For this when i run following select, SELECT...

Question on certificate and asymmetric logins - Hello all, I have a classic question (probably for most of you), as I am new to dba. Why do we...

FileStream - Hello. I have a database with 2 filestreams and i create tables with a column varbinary(max) to a filestream and others...

SSRS reportsolution file - Hi, I have a report URL and I can see some of the reports on the URL. But where can I...


SQL Server 2012 : SQL Server 2012 - T-SQL

Want to add Text data - Hi All, I have table called T1 where the data is populated like below LineNumber errortext Fileid 1 a 10 1 b 10 2 a ...

Pivot EDI EAV type table - Hi all- I'm trying to flatten this table into something like the second table. Any suggestions would be greatly appreciated. Nick See the...

Can't we rely on execution plan for performance !!! - Hi all, I have a heap table [b]USER_COUNT_MONITOR[/b] with a single column [b]ID[/b] and DISTINCT values from 1 to 100000...


SQL Server 2008 : SQL Server 2008 - General

Uninstall SQL Server 2008 R2 but KEEP Native Client - hello all, I want to remove (thru Add/Remove Programs) SQL Server 2008 R2 - but KEEP the 2008 R2 Native Client. Does the...

SSMS closes without warning during query run - Does anyone know why SSMS would close without warning during the middle of a query run? It's done this to me...

Windows XP Conversion - Hi, I am new to SQL Server but am looking to make the switch from Oracle 11g. Is it possible...

Sql server sorting issues - Hi Guys.I need some help I'm doing a sellers report for my boss. I have done a top sellers and it worked...

Any *easy* way to compare database schemas, without a 3rd party tool? - I've been asked to compare an older copy of a DB to a current copy, to look for any schema...

Access rights - How to provide only rights to execute sp_who2 & killing the blocking SPID if any without giving sysadmin rights, is this...

Backup SQL agent Job - Can I setup a single agent job to handle Full(Every Sunday at 00:00), Differential(Everyday at 00:00) and log backups(every 30...

Script to find all filestream databases with list of Tables (contain filestream column) - Hello, Do you have a script to find all filestream databases with list of tables containing Filestream Column ? I would like...

Execution Plans Question - I'm playing with a stored proc that takes 2 days to run. I am pretty certain the issue was the...

Form showDialog tried to set an ineligible form as its owner - Hi, I have SQL Server 2008 R2 Standard x64 installed on my Windows 7 SP1 workstation. I applied SP2 a couple...

MSDTC config in cluster.. - Hi All, Windows server 2008 r2 I have to install sql 2008 r2 cluster for i need a help for msdtc config? Steps...

Script to check the progress of rebuild Index? - Hi, Is there any good script available to check the progress of Rebuild Index? Would be helpful.

trying to send database mail of sql server using gmail account - Hi i am trying to send database mail and using gmail as an smtp server.i have used my emailid,and password...

dynamic filename in SSIS flat file source - How to specify for dynamic filename in SSIS flat file source for file 20110818_abc_def.csv as the name abc_def does not changes only...

Error message when changing location of share drive in SSIS package - Hi all, At work we have a job that does the backup of 3 tables to a sharedrive using a SSIS...

html file to sql server table - How can I get data out of this HTML table into a new SQL server table. This is a sample HTML...

Stored Procedure Execution Error Message - We have this stored procedure create that runs when i add any of the parameters to the section of the...

Adding time intervals in minutes and display as csv - i have a table with three columns as col1 col2 col3 11:30 13:30 15 00:10 01:40 5 the out put should be as follows 11.30,11.45,12.00,12.15,12.30,12.45,13.00,13.15,13.30 00.10,00.15,00.20,00.25.....01.40 earlier...

Unable to remove old transaction log backups. - Hi All, We have a DR server configured with logshipping. The transaction logs are taken from Production and copied to the...

Problem in query - Hello, i have a problem regarding query, my data is like this EID------checktime-------------status----- 123-----7/1/2013 08:00:00 PM-----I--------- 123-----7/2/2013 02:00:00 AM-----O--------- I means in and O means...

Moving a database log file question - SQL Service was down, our storage engineer created a new LUN and then moved the log file to the new...

Msg 468, Level 16, State 9, Procedure "procedurename", Line 129 Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. - Dear All, I have a stored procedure and I keep getting the following error message: Msg 468, Level 16, State 9, Procedure...


SQL Server 2008 : T-SQL (SS2K8)

WAITFOR DELAY - Shortest Delay Possible? - This is baffling me! I have a proc that will call another proc to clear a record (if one exists) before...

how to get the desired output correctly - i have a table emp with records as follows: Create table testemp ( empname varchar(50), highprtjobs varchar(50) ) insert into testemp values ('tsaliki','h1') insert into testemp values...

How to retrieve integer and decimals from string - Hi, I would like to know how to retrieve integer,decimals from string in table format. Input string: < 2% annual < 0.16%...

Get the Maximum Value from a related table based on date - Hi Folks, Can someone help with the following on SQL Server 2008... I have a table Customer and an Orders Table... I want...

how to write a stored procedure for this - i have a table emp with columns empname and highprority jobs. for example i have 3 emp and 10 high priority...

Help to get my desired output using stored procedure - i have a table emp with columns empname and highprority jobs. for example i have 3 emp and 10 high priority...


SQL Server 2008 : SQL Server Newbies

SQL Server 2000 upgrade issue... - I'm attempting to migrate SQL Server 2000 database to SQL Server 2008. After reviewing the results of the upgrade advisor...

Relationship Question - Hey guys - I have a relationship question....for SQL Server that is. ;) I'm in the early stages of my first large...

How to set values to a field in database using sql server 2005 - Hello World:-) How should i set a values from my database like... I have 3 tables...namely Bio Table, Sex Table, Status Table *Bio...

How to insert in a 3 table - I Have 3 tables....namely Bio, Sex, Status *Bio BioID FirsName MiddleName LastName SexID StatusID *Sex SexID Sex(Male or Female) *Status StatusID Status(Single, In Relationship or Married) here is my question... How can i insert in...

Inserting into two tables - little help needed - Hello all. First of all, I wanted to [b]thank [/b]everyone for participating in these forums and helping those who need help....

SQL Report Help Pls - Hi I've got 2 tables in my db ( Property and Contact ) which I need to report on ( extracts below ) : Property : Prop...

Last executed queries - Dear All I am using foloing query to get list of last executed queries SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.* FROM...

Adding User to SQL Server - Hi, I got a scenario where I am using [b]Windows Application[/b]. I want to create a SQL Server User satisfying the password...

Run a query against registered servers and insert results into table - Hi all, Is it possible to run a query against multiple registered servers and have the results inserted into a...


SQL Server 2008 : SQL Server 2008 High Availability

Moving log file after configured Log Shipping - Hi experts, I have configured Log shipping on a database on production server. The database's data and log files are on...

Best practice to setup a test server to restore backups from production automatically? - I was finally given leeway in my budget to get a proper test environment and have two instances of Server...

Transact log shipping not working - I am able to create and successfully run the 1st transact log shipping wizard and it finsihes and creates the...


SQL Server 2008 : SQL Server 2008 Administration

DB Size - Why is the database size different than the physical file size? The general page of the database show 12000MB. If...

how to insert powershell values into sql server table - Hi, I am looking to find way to hold values in my sql table. actually i have powershell script it is...

Login Failed for user - Password did not match - Hi, So i am migrating an application database from an SQL Server 2000 instance to an SQL Server 2005 instance. When i...

Kerberos Authentication stopped working - Hi guys, i´ve only been a DBA for 2 years and still i feel like a rookie at this, need...

Looking for powershell script - Hi, i am looking to find powershell expert, so that can help on my query.below script does return sql installed...

SQL Server Browser - Hi, I am working with a cluster with 2 nodes, active-active. The nodes have several instances and in total 3 different...

wait stats precentage? - Hi, [code="sql"]WITH Waits AS (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s, 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn FROM sys.dm_os_wait_stats...


SQL Server 2008 : Data Corruption (SS2K8 / SS2K8 R2)

Possible Corrupted Index - Strange Issue - Hi, I'm getting a Corrupted Index error message when running UPDATE STATS ... "Possible index corruption detected. Run DBCC CHECKDB." I've tracked...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Sub partitions - Hello there, Does SQL Server 2008 R2 support Sub partitions? Thanks

Primary Key and Clustered Index: how to set in this example? - hi there, hope it's the right forum ;-) I have an example where I am not sure about setting my primary key...


SQL Server 2005 : Administering

Update Statistics job failed - I have a SQL Server 2005 server (32-bit, Standard edition and PS3) running on Windows Server 2003 I have 4GB...

Not able to start SSRS service in SQL Server 2005 - Hi, We have recently installed SQL Server security update SQLServer2005-KB948109-x86-ENU on our SQL server 2005 SP2 instance(-32bit) after that we started getting...

Insert and indexes - Good morning INSERT query is suddenly taking too long to run which used to run under 30 minutes is taking 5...

Locks - Blocked Processes - Hi, We are using Spotlight for monitoring and we are getting Blocked processes alarms. I would like to know where can...

How to Create Linked Server for a MySQL database using SQL Server Management Studio - It took me about a day to figure this out, so I thought I'd try to save someone else the...


SQL Server 2005 : Backups

litespeed backup consumes high cpu - is litespeed one of the causes of high cpu??

Standby Database Reverting to Suspect - I have a server containing 25 databases with log shipping set up to a secondary site and scheduled to run...


SQL Server 2005 : Business Intelligence

Reading files and sending data to web service - Hi, I need to be able to open up fixed length data files, read the records from the file, and...

SSAS - How do I handle dimensions with valid duplicates? - I'm new to SSAS and I got a problem I'm not able to resolve. Let's start with two tables, this...

Parameter being disabled even though it's not a cascading parameter - Hi All I'm getting a very odd problem with a parameter on a report and just want to know if anyone...


SQL Server 2005 : SQL Server 2005 General Discussion

How to Write Query for This - i want to write a query to get data of two different columns with two different conditions from a single...

query - i have a query UPDATE dbo.cheeck SET CheckTime = DATEADD(DAY, -1, CheckTime) WHERE Status = 'O' and datepart(hh,CheckTime) BETWEEN 0 AND 11 i want to update...


SQL Server 2005 : SQL Server 2005 Performance Tuning

Lightly fragmented indexes causing a huge difference? - Hello all. I am having an ongoing issue in a production vendor application, and I don’t know what to look at...


SQL Server 2005 : SQL Server 2005 Integration Services

How to import Multiple data files (cvs files) into a single Table - [b]NOTE:[/b] Due to a NDA I can't disclose any actual DDL for our DB but I hope that will not...


SQL Server 2005 : SQL Server Newbies

modify sql statement from *= to left outer join - I am trying to convert to left outer join syntax a sql statement that works as expected using *= syntax. current...

How to query same table with 2 WHERE clause onto 1 row - I have the following two queries but how do I combine them to return the results into one row as...


Reporting Services : Reporting Services

SSRS: Use of Sparkline - I am currently trying to Show the monthly Customer income rate by customer. I would really like to use sparklines...

Not able to start SSRS service - Hi, We have recently installed SQL Server security update SQLServer2005-KB948109-x86-ENU on our SQL server 2005 SP2 instance(-32bit) after that we started getting...

Calculated Member only gives out null Values, Why ? - I Need to do the following calculation in mdx (i hope the code is straightforward, if clarification is needed please...

SSRS 2008 R2 - Page Break Issue - Hi All, I have a SSRS report that exports data to PDF. The report displays survey data so the display format...

Data Driven Subscriptions Render Formats - Hi I've created a few data-driven subscriptions and one of the requirements is that the subscription sends an emailed csv file....

Passing parameter from Table View from Oracle returns empty result - Hi All, I have spent few hours looking into the issue and still cannot figure this one out. I have created parameter...

ABOUT REPORT IN REPORT BUILDER - HOW TO GET parameter value as null when we keep as null and show the text what we enter in...


Reporting Services : Reporting Services 2005 Development

BIDS not allowing me to change "Font Name" or "Font Size" from the "report Formating" toolbar dropdowns after update. - The dropdown for those two items are greyed out and unavailable. On a backup server using SP1 I do not...


Reporting Services : Reporting Services 2008 Development

Matrix Tool - I am trying to get the result from one data set in a pivot style.... Input Salesid, CustId,region ,7-2013 6-2013....................(Past12 months) 1 1...

Drop Down Boxes in Finished Report - I have been asked to replicate an Excel worksheet which includes drop down lists. This form is completed partially by a...

SSRS: Diagram: Cumulate Values into new field - Hi there I'm currently using this Diagramm to Show percentages of Invoice Amount by Customers for my Company. (Picture below...

Add : "All " in Cascading parameters - I am trying to add " All" in the parameter field and so wrote the following query for the 1st parameter SELECT...


Data Warehousing : Integration Services

How to automate the running of a script task, so that it runs hourly? - Problem/Background: Source database has a table that contains snapshot information about the statuses of a bunch of machines. When statuses change...

SSIS mysql to mssql - I make same package ssis in visual studio 2010. I load data from mysql to mssql 2012. I created ADO.NET source(connect...


Data Warehousing : Analysis Services

Training kits for MCTS 70-466 and MCTS 70-467 - I want to appear in test below. MCTS 70-466 (Implementing Data Models and Reports with Microsoft SQL Server 2012) MCTS 70-467...

Filter Dimension Members that don't have any Values in selected Measures - Hi there, I have currently been put up on a Task at work to filter out Members of every Dimension used...

Cube performance issue - I am a new one in SSAS. I have cube of almost 800MB size without any partition and aggregation defined....

a calculated member cannot be used as an operand of a range operator - hi All in mdx land, I have two queries which would do the same thing...doing a rolling 5 day average at...


Database Design : Design Ideas and Questions

Struggling with this Data Mart - I'm doing dimensional modeling and i'm struggling with this data mart. Can someone help? I'm building a data warehouse for a...


SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2013 - I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...

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

Is there a problem with the 'Add to Briefcase feature'? - Tried adding several articles to my Briefcase this morning but I just get a 'loading' msg appear?


SQL Server 7,2000 : General

Having Problem in restoring transaction log backup - Hi, While restoring a database, which was damaged due to hardware failure. I Got the following Error Message, "The log in this...


Career : Certification

70-461 - Done, but more difficult than expected - Just passed the 70-461 SQL Developer Exam Today. This exam is much harder than I expected. Its quite syntax heavy, and asks...

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 ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com