In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.
 
sqlbackup Gold Community Choice Award for SQL Backup Pro
Try award-winning SQL Backup Pro for faster, smaller, fully verified SQL Server backups. Download a free trial now.
 
deploymentmanager NEW! Automate your .NET deployments
Deploy ASP.NET applications and SQL Server changes fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.

In This Issue

Data Everywhere - Level 3 in the Stairway to Reporting Services

In this article, MVP Jessica Moss talks about data sources and how to connect them to your report. Learn how to add the reusable data sets and data sources for your reporting projects. More »


Working With Multi-Select Parameters for SSRS Reports

Multi-select parameters give your users control over their reports while reducing the number of reports they have to work with. This example demonstrates how to create a multi-select parameter list and pass the values to a stored procedure that will then populate the report. More »


From the SQLServerCentral Blogs - Example of SQL Server table partitioning

Recently, one of my favorite customers asked me some very thought-provoking questions about using database partitioning to move transactional data around. Their... More »


Editorial - The Growth of Storage

Over the holidays I had a chat with my son about computers. His laptop died, and he wanted a new one. It isn't needed for school and my wife and told him he'd need to earn the money for half of a machine and could take the rest from his savings. I'm happy to say that he has been working hard to earn money, out in the 10-20F temps caring for horses, and is getting close to making his first computer purchase.

In setting his target amount of money to earn, we talked about specifications for various machines, looking online and in local stores. We talked about the various CPU, RAM, and storage values, and what they might mean to him in terms of the performance of a laptop. While we were talking, he asked me some interesting questions about the growth of storage. I thought they were interesting, and decided to ask you this week what you think.

When will we get 1TB of RAM in a mainstream laptop, and when will we get 100TB of storage?

Those are large numbers, though I know some DBAs who have one, or even two, terabytes of RAM in large servers. My first thought was that we were a long time from getting those values in consumer machines, but as I look at my phone with 64GB of storage in flash, and this recent announcement of a 2TB SSD, perhaps they aren't as far away as I might have thought.

My first professional computer had 4MB of RAM and 200MB-ish hard drive in the early 90s. Today I have a desktop with 24GB of RAM and 2TB of storage. Extrapolating that and I'd expect that I'd have over 1TB of RAM and 20PB of disk in 20 more years.

What do you think? When will we have laptop machines with the specifications of the largest SQL Server instances today?

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

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

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


Question of the Day

Today's Question:

How many row(s) are affected by the following code in the estimated execution plan?

Begin Transaction
   SET QUOTED_IDENTIFIER ON;
   SET ARITHABORT ON;
   SET NUMERIC_ROUNDABORT OFF;
   SET CONCAT_NULL_YIELDS_NULL ON;
   SET ANSI_NULLS ON;
   SET ANSI_PADDING ON;
   SET ANSI_WARNINGS ON;
COMMIT

Note: I only tested this in SQL Server 2008 SP3

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.

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.


Yesterday's Question of the Day

What is the result of the SELECT COUNT statement at the end of this code?


CREATE TABLE TblA1
  (
   col1 INT ,
   col2 VARCHAR(10)
   );
GO
CREATE TABLE TblA2
  (
   col3 INT ,
   col2 VARCHAR(10)
   );
GO

INSERT INTO TblA1(col1, col2)
VALUES (1, 'A'),(2, 'B'),(3, 'C'),(4, 'D'),(5, 'E'),(6, 'F');
GO
INSERT INTO TblA2(col3, col2)
VALUES (1, 'A'),(2, 'B'),(3, 'C'),(4, 'D'),(5, 'E'),(6, 'F');
GO
select COUNT(1)
 from TblA1
 where col1 in (select col1 from TblA2 where TblA1.col2 = TblA2.col2)

Answer: 6

Explanation: As the column is not explicitly prefixed with the table alias, it uses the field as defined within the table declared in the inline SELECT statement. If the field does not exist there, it then defaults to the field within the outlying table definition.

» Discuss this question and answer on the forums

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.


Featured Script

Convert string Hex to Numeric with inline TSQL

A fast way to convert a string containing Hex values to numeric 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 2005 : Administering

SQL 2000 to 2005 - Upgrade - For SQL 2000 to 2005 Inplace Upgrade, after upgrade, what should we do as next steps. Some are run checkdb change compatility...

Installation issues during SQL 2005 SP4 - I am trying to upgrade SQL 2005 to SP4 from SP2. Few SQL components were successful. version numbers are below Products...

Compatibility level 80 Vs 90 - SQL Server 2005 - Hi, Some of the people that work with me on the company convert a database from SQL Server 2000 to...

Changed SQL Services Acct - "Cannot Generate SSPI Context" - We want to rollout a new account to use for SQL Services. On a Dev server (SQL 2005 SP3, Windows...

SQL Server 2005 : Backups

error 18272/16/1: during restore restart, an i/o error occurred ... *.CKP file not found. 2005 SP4. Log shipping. - I'm getting the above error. I know it has little impact & I can suppress the errors but, it's bugging me & I'd...

recover old data - Hi, can anyone help me how to recover the old data from SQL database? The data in the database only lasts...

Is it possible to recover deleted data from log file of sql server 2005 - in my database by mistake some of the data is deleted from tables,bcoz of wrong query fired, now I want...

SQL Server 2005 : Business Intelligence

Assign variable in derived column - Is it possible or any other way to assign the Derived column expression value in a column. for eg: [b]@[User::newcol]= FILE_PATH + "/" + FILE_NAME[/b]. Please...

same measure in 2 cubes, but not displaying data in one cube related reports only - Hi , I have a measure called "Stock Sales COGS Local Currency" in sales cube and monthly cube. product levels are 6...

SQL Server 2005 : SQL Server 2005 General Discussion

DB Shrinking Question - First off, I'm no DBA just a server sys admin. I run a maintenance plan on my Prod DBs every week...

SQL Server 2005 : SS2K5 Replication

Using oracle subscriber ---Questions - [b] Oracle treats both empty strings and NULL values as NULL. This is important if you define a SQL Server...

SQL Server 2005 : SQL Server 2005 Integration Services

How to load Date columns in consistent manner - Hi, I am loading the table from Excel source using SSIS 2005. Excel contains one of the columns as [b]Exp_Date[/b] and its...

SSIS Script task to change table name in data flow task - Hello, We need to change the table name of 'OLE DB Destination' task. I am not able to use variable due to...

SQL Server 2005 : T-SQL (SS2K5)

Stored Proc and performance - Is there any SQL that can be added to a Stored Proc to track if there was any blocking of...

Test results report - I am trying to generate a report showing which questions were missed on an online test for a given student....

Delete rows from the table using cursor - Hi All, I have used the cursor in the stored procedure to delete the data from the table. I'm deleting the...

SQL Server 7,2000 : Backups

SQL Express Backup - I'm new to SQL and we have an SQLExpress database that we need to backup on a nightly basis. Is...

SQL Server 7,2000 : T-SQL

Date Range between Start Date and EndDate - i have a table in which i have two coulmns of StartDate and EndDate. i want to create an another...

SQL Server 2008 : SQL Server 2008 - General

cheking MAXDOP is set to zero? - Query to check MAXDOP is set to zero ?

Get date not working - Hello, I am trying to create a query to get the results for yesterdays data only. So basically i need all...

Nested replaces ? - Hello all, At this moment I need some nested REPLACE actions. Some time ago I saw a set of routines for string...

Backup with checksum - The sql server has an option for checking checksum after creating backup(WITH CHECKSUM). I want to know whether sql server needs...

DBCC Checkdb Problems - Hi guys Hoping that someone can point me in the right direction on this Running DBCC CheckDB against a database, returns the...

find out data file is splits to multiple files? - Query to find out 'is data and log file splits to multiple files '?

Working with variables in SSIS - Hi pls assist. I have the following query inside a variable which I'm trying to pass a date as a value...

Difference between Money and (Float or Decimal) Datatype - What is the difference between Money and (Float or Decimal) Datatype. If we use Float or Decimal instead of Money,...

function erroring and reporting wrong name - Hi All, I have a weird one for you that I was hoping to get some help with. I created and deleted...

How to archive old data on top of existing table partition? - Hi Experts, Can someone give me some high-level direction on how to approach this? I'm given a task to do these 2...

How to match a UNICODE NULL character within an nVarchar(128) string? - Our content has been contaminated by UNICODE NULL characters in our SQL Server 2008 database that don't play nicely with...

Question on sp_delete_backuphistory - If I have the following: [quote]SET @deleteday= dateadd(dd,-3,getdate()) EXEC msdb.dbo.sp_delete_backuphistory @deleteday[/quote] In the folder, currently have backups for 01/13/2013, 01/14/2013, 01/15/2013 So when the...

database email - Hello Again...I am at my new intern place and the job my manager gave me is I have to run...

Subject: XML from SQL table (for xml explicit or namespaces?) - Hi Guys I’ve got bit of a problem with transforming SQL-table (SQL Server 2008 R2) based data into XML . XML...

datatype larger than 255? - I have to import some data that has text fields that can run from 300-400 characters... is there a datatype...

How to obtain Sum of count - The View obtains the first three columns , i need to add one more column(totalCount) to the view that obtains the...

Restoring file group error - I have created the file group for my database.First i took backup of individual file group(mdf and ndf) then I...

Unable to install sql server 2008 Management studio - Dear Sir/Madam, I downloaded Visual Studio 2010 from microsoft so its automatically installed sql server 2008 R2. In Sql server 2008...

How to get list of affected tables in an Query - Hi Guys, First post here. Have a problem trying to build a work around for SQL Azure not supporting SELECT INTO... As...

SQL Server usage in Financial Services - Hi, I am (still) often challenged about having SQL Server as the underlying DB for high volume applications (lots of transactions,...

Problem in executing distributed query - Dear Sir, I have configured a linked server using SSMS between two sql servers 2008 R2. Now I wrote a trigger...

Monthly Aggregation - I've created following code: [code="sql"] select DATEpart(Mm,(Convert(Date,semua_tkh_Daftar_txt,103))) as MonthNo, DATENAME(Year,(Convert(Date,semua_tkh_Daftar_txt,103))) as Year, DATENAME(Mm,(Convert(Date,semua_tkh_Daftar_txt,103))) as Month, COUNT(doclinkid) as Registered, sum(case when REPWPK.T_BMAL.Semua_Status_Kod like 'S%'...

pdf generation in sql - i have a simple table with 3 columns and 3 rows. is it possible to generate a pdf document by directly...

Openrowset problem - Hi all, Is that possible to select The data from excel 2007 on sql server 2008 r2(64 bit),os Windows 7...

how to find dependent objects on Default or Rule in sql 2008 - I am able to see the list of columns or table which is bound to my rule or default using...

Insert multiple lines in a single row - Dear, I want to insert multiple lines in a single row in SQL Server 2008 R2. I have tried. But it's...

Problem with using SQL Server linked server to MySQL database - I have a MS SQL Server that does a lot of data manipulation and staging and then I need to...

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

Microsoft assemblies failed on load due to a signature mismatch after service pack updates - Hi All, We upgraded SQL and windows on one of our servers last night, details are below: [b]MS 2008 Standard x64:[/b] Before...

Linked Server from 2008R2 to Sql Server 7? - Hello Everyone, I have done quite a bit of searching and have found several people asking the question but, I haven't...

Performance of ISNULL or the AND/OR method in the WHERE clause - Hi, Am running into a very weird performance issue, basically I have a bunch of reporting queries that uses the ISNULL...

Master Data Services - I'm investigating MDS and have a question regarding keys and MDS. First of all, is this the right forum to post...

SPATIAL SQL Server 2008 - How can I use STTOUCHES with a GEOMETRY field? Can it be converted? How? Regards

SQL Server 2008 : T-SQL (SS2K8)

Droping a temp table. - Hi all, I tried deleting a temp table using the below following code [code="sql"] IF EXISTS(SELECT * FROM sys.tables where name LIKE '#TEMP') DROP...

query help - In a table, I have value like this: 1A 2A I want data like this: 1C 1D 2C 2D that is for each value of A in table,...

Recursively create Tree Structure - Hello Friends, Following is the DDL, Sample Data for the requirement I have : [code="sql"] --DDL SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE...

Histogram chart in sql - Hi, Im hoping someone is able to check what Ive created for a histogram chart and can provide feedback, suggestions, improvements. (Im...

Query Help.. - Hi guys, I am working on task where i am gonna clean my DB. I would like to know is...

Vendor DB backup job help. - Hi, I have a vendor app that runs an SQL DB. Now the vendor provided all the agent jobs for this...

Something Most Likely Simple - Hello Everyone I have a rather odd query that I am trying to figure out the best way to code. I...

When can I use an alias? - Hello, It seems that DELETE statements don't support aliases. Is that correct in 2k8R2? Also, the query attached does not permit the...

Import from external database fails. - I am using SQL 2008 server and an external vendor changed the particular warehouse name for the IMPORT file that...

OPENXML or nodes().... - Hi, Again with same doubt about using OPENXML or nodes()... In previous tests I made with both methods, nodes() was faster mainly...

Wrong Execution Plan question - Hi, Our developer send me a query that takes long time to run. it has a query structure like this, select T1.*...

How to combine two tables when one table is in different database and this table will determine the value to be shown on the first table. - Dear members, Hello, a pleasant day to everyone. I would want to ask help about this query that I need for reporting. Let’s...

Need To Generate XML File - Hi, I Have Table Data For The Following Stucture.. [code="sql"]CREATE TABLE [dbo].[xmldata]( [FirstName] [varchar](110) NULL, [LastName] [varchar](210) NULL, [Email] [varchar](110) NULL, [Phone] [varchar](110) NULL, [Position] [varchar](110) NULL, [Branch]...

Question about rounding the points in a Geometry. - Here's a POLYGON from my MS SQL 2008 database that I pulled to use here as an example: POLYGON ((-64.8040519990027 18.366768000647426,...

SQL Server 2008 : Working with Oracle

"ORA-00936: missing expression" w/64bit R2 & OraOLEDB.Oracle provider - I have been working on migrating three of our old server to a new single instance. The new instance is...

SQL Server 2008 : SQL Server Newbies

Secondary indexes in SQL Server. - Hi all, What are secondary indexes in SQL Server and why are they needed.

HEAP entires in sys.Indexes - Indexes are either Clustered or Non-Clustered. Than why does the HEAP entries are shown in sys.indexes table?

How to set index for these select queries - In my table (large one) there is mainly three select queries: 1) select * from Product where productid=101 Here productid is primary key...

Inserting Multiple Records Using While Loop - I'm trying to insert multiple records in while loop but I keep getting cannot insert duplicate into primary key error....

Torn page issue - Hello Masters, Can torn page issue occurs due to lack of disk space ? I know torn page is the page that...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Cluster on VM hosts which are themselves clustered - All - What are your thoughts about: - SQL cluster (2005 or greater) built on two Hyper-V nodes, Win 2008R2 - The nodes...

SQL Server 2008 : SQL Server 2008 Administration

Who changed the recovery model? - Hi All I'm trying to establish who changed the recovery model. I've tried default trace, Sp_readerrorlog 0, 1, 'recovery', the logs...

Attach database file failed - Hi Experts, I tried moving one of our user DB using 1.mode database offline..moved files run alter database modify files and...

Urgent Help Needed - Hi Experts, We are in process of making one of our dev environment same as PROD. In prod we have drives...

Proactive sql dba activities for Production Databases - Hi, Please provide some of Proactive sql dba activities for Production Databases.

find errors in log file for multiple servers - I would like to see if any errors in SQL log. Is that possible using some query for all the servers...

Just Deleting data will reclaim space?? - Hi Experts, We have deleted 144101324 records from our WH database will it release the space to disk? Thanks in Advance.

central management servers - I setup a sql server instance to host central management servers. Then I added all the sql servers to diffrent group...

memory sql 2008 - Hi All, Recently, i am going through the memory concepts of sql server 2008 and i need some clarifications from expericenced...

SQL 2008R2 Express | SQL Agent help...Need to set up a backup scheduled task. - This is my first look at SQL Express 2008r2 and I have realised that SQL agent isn't part of the...

SQL SERVER 2008 Backup job/Script - Hi friends, i was i need of backup job which can backup my 2008 studio SQL Server. after browsing through lots...

Career : Certification

Querying Microsoft SQL Server 2012 Training Kit (Exam 70-461) publishing delay - I've been waiting patiently for this training kit coming out for months now, expectantly waiting for it's arrival end of...

Programming : General

How to prevent duplicate rows inserted by SP called simultaneously by different SPIDs (without unique constraint on table) - A stored procedure "AllocatePaymentToOrderItems" creates rows in a table "Transactions". The intention is that the SP never creates duplicates, i.e....

SQLServerCentral.com : Anything that is NOT about SQL!

Employer asking for Social Media login? - I've never experienced this but found it bizarre. If I was ever asked to provide access to my social media...

Something random to ponder - So I had a funny thing happen to me a few minutes ago and couldn't help but share... QA Tester 11:54...

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

Reporting Services : Reporting Services

Multiple parameter ODBC - Hi I have created a report in BIDS 2008 that connects via ODBC connection. I am having issue with the parameters for...

Parameters in subreport - I have main report and subreport that is called from the main report. Subreport can be called multiple times depending...

barcode generation problem in RDLC local report - I am trying to encode [url=http://www.aspper.com/barcode-rdlc/tutorial.html]barcodes in RDLC reports[/url], I am using vs 2008 and C# class library. It works well...

how to get the source code from .rdl file - Hi , i have a rdl file that is displaying report, i want the source code of that rdl file.(since i...

Data Warehousing : Integration Services

Changing Collation In SSIS - Good Morning, Afternoon or Evening where ever you may be. Ive taken the plunge into SSIS after studying the Stairway to...

Load Parent and child table. - Hi, Please help me on below scenario. My requirement is load two tables from single excel. I am having a parent...

Data Warehousing : Analysis Services

Measure between dates - I have a campaign dimension ( Its a marketing campaign for a product ) and i have to measure the sales that...

How do you service your Cubes if using dynamic partitioning - Hi there, I am thinking about using partitioning as a tool to reduce our cube processing time. While I was thinking about...