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

Some tips for working with big data models

MauroPichiliani from SQLServerCentral.com

The main goal of this article is to present some tips to help professionals that need to work with complex, big, and hard to understand database models that anyone may came across some day. More »

Setting up a Data Science Laboratory

Additional Articles from SimpleTalk

There is no better way of understanding new data processing, retrieval, analysis or visualising techniques than actually trying things out. In order to do this, it is best to use a server that acts as data science lab, with all the basic tools and sample data in place. Buck Woody discusses his system, and the configuration he chose. More »

From the SQLServerCentral Blogs - The Principle of Least Privilege

Steve Jones from SQLServerCentral.com

One of the tenets of good security is that no person or process is granted more rights than it needs... More »

I was taking to someone recently and this person had a large transaction load on their SQL Server. At least, I thought it was a large load. This person said they experienced 1000tps most of the time and this made maintenance, DR planning, and more a challenge. That's a lot lower than the TPCe performance system that has 4,614tps, but it's nothing to sneer at. That feels like a significant load to me.

However it's been awhile since I worked on a variety of systems as a consultant. I often see the forums at SQLServerCentral showing only tens of transactions/sec, based on the performance data that you can see at monitor.red-gate.com. This is the actual data from our servers and it seems like a low number to me.

This week I was trying to decide what 1000tps really means. Is this a busy system, maybe even an edge case if this is the average for your system over a week or month? Is it a representative system for many companies? I wanted to see what others report from their systems this week:

What are your average and peak tps for most of the systems you manage?

I'd like to hear about 1 or 2 systems that you consider to representative of the loads in your environment. Taken across your business time, whether that's 9-5, 24x7 or something else, can you get the average and peak values for your transactions/sec and disclose them? You don't need to let us know about your application or company, though industry would be nice.

I'm curious how busy systems are for some of you and whether this presents you with challenges for migration, upgrades, virtualization, DR, maintenance or more.

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 - 24.2MB WMV
• Watch the iPod Video Podcast - 18.0MB MP4
• Listen to the MP3 Audio Podcast - 3.7MB MP3

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

Today's Question:

I have a table named #testAggregates

with column col1 of datatype int. Below are the values in the table:

What are the results from these individual select queries?

SELECT SUM(distinct col1) as sum_col1
     , COUNT(DISTINCT col1) as cnt_col1
  FROM #testAggregates

SELECT SUM(distinct col1) as sum_col1
     , COUNT(DISTINCT col1) as cnt_col1
 FROM #testAggregates
 WHERE col1 IS NULL

SELECT ISNULL(SUM(NULL),0) as sum_col1
     , ISNULL(COUNT(NULL),0) as cnt_col1
 FROM #testAggregates

By P. Praveena

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 Wait Stats Joes 2 Pros

You learn a lot when thrown into a crisis situation, where everyone is looking to you for answers. The system is down, the CEO is pleading to you for answers as the each minute cuts the company’s bottom line. When you are in a hurry, you need some new ideas right away. This book is your helpful tool. When used correctly, you can determine solutions and even related solutions to simple (yet lesser known) areas of SQL Server. Learn what causes SQL tasks to wait or site in queues. Even if your system is not in trouble right now, you can use what you will learn in this book to view the history of wait statistics.

Pick up your copy of this great book from Amazon today.

Is it possible to create a table with unique constraint that allows multiple NULL values from SQL Server 2008 onwards?

By Gopinath S

Answer: Yes

Explanation: By applying a filtered index, we can create a table with a unique constraint that allows multiple NULL values. The code below shows this:

USE [TEST]
GO

CREATE TABLE [dbo].[tbl_test](
	[id] [int] 
) ON [PRIMARY]

GO

CREATE UNIQUE NONCLUSTERED INDEX UNCI_test
ON [dbo].[tbl_test](id)
WHERE id IS NOT NULL
GO
--=======================================
insert into [dbo].[tbl_test] values(1)--success
insert into [dbo].[tbl_test] values(2)--success
insert into [dbo].[tbl_test] values(NULL)--success
insert into [dbo].[tbl_test] values(NULL)--success
insert into [dbo].[tbl_test] values(2)--error [Cannot insert duplicate key row in object 'dbo.tbl_test' with unique index 'NCI_test'.]

GO
select * from [dbo].[tbl_test]

Ref: CREATE INDEX - http://msdn.microsoft.com/en-us/library/ms188783%28v=sql.100%29.aspx

» Discuss this question and answer on the forums

The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset

As the most influential thought leaders in the data warehousing and business intelligence industry, the Kimball Group has developed pioneering techniques that serve as industry standards for DW/BI system design, development, and management. With this new edition of their bestseller, veteran experts from the Kimball Group get you up to speed with using the new Business Intelligence release of SQL Server: SQL Server 2008 R2. Covering the complete suite of data warehousing and BI tools that are part of SQL Server 2008 R2, the authors follow the full project lifecycle, including design, development, deployment, and maintenance. Get your copy today from Amazon.

Query all objects created or modified after specified date

Eric Russell from SQLServerCentral.com

This script will query across all databases, returning a result of objects recently created or modified. More »

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

SQL Server 2005 : Administering

Log file moving - Hello friends, I have a question that I have a database "Deparment" in my SQL Server 2008r2 environment It has Data...

Permissions required to run SQl serevr profilier - Hi All, One the user want's run profilier, so I grant view server state access. Now they can open profilier but...

SQL Server Performance Problems - Hi Guys Occasionally at peak times of the day I get I/O errors on my SQL server (v 2005, 16 core...

Ntfs error - Hi, We have 3 node a/a/p cluster setup for SQL Servr 2005 and getting the below error daily at same time....

How to see one week before job history - Hello: I have a agent job running. When I right click this job to view history, only show me current day...

How to determine if SQL Server and O/S are 32-Bit or 64-Bit? - We are running SQL Server 2000 (SP4) 8.00.2055 on Microsoft Windows Server 2003 R2 Standard Edition SP2 on NT Intel...

SQL Server 2005 : Business Intelligence

Using SSIS to Archive access databases - Hi I have a lot of .mdb (ms access) files that I need to compact and archive and I'm strugling...

conver numeric data to string data in ssis - The source is a relational table and the data is shown below: Code: value -------- 65020 78250 58264 Convert the numbers into string format and load into...

SQL Server 2005 : Data Corruption

CHKDSK on a SAN Volume - I need a little direction. I have a clustered SQL db (Sql 2000 / Win2k). I have errors in the log for corruption...

SQL Server 2005 : SQL Server 2005 General Discussion

Database Connector Error: 'Cannot obtain error message from server.' - When I attempt to add a new SP to a report I get the message stated in the subject. I...

SQL Server 2005 : SQL Server 2005 Strategies

How to archive a database - Hey, Each of my customers has his own database. Sometimes a customer quits. When that happens, I want to archive that...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Need help with query tuning - Hi folks, I'm struggling to come up with ideas of how to improve the performance of the following query: [code="sql"]SELECT ...

Use of Parallelism. - How can we use the parallelism in sql server. For the same query the exectuin plan at QA server the...

SQL Server 2005 : SQL Server 2005 Integration Services

cannot open datafile - Hi, i'm creating a ssis package to access a flat file. i created a batch script to obtain the filename of the...

Permissions needed to connnect to integration servies - Hi I got user1 and user2. They both got same level acccess on one server, including DB's. But user1 can...

multiple conditional splits in ssis - Hi, I have a flatfile which contains 100 diff locations, i want split the data based on the name of city...

How to store the Count(*) value fro datareader to int. SSIS 2005 - Hi all, I have been stucked with this for quite a while. I have a script: [quote]Dim sqlConnStr As String = "Data...

Argument "Server1" for option "connection" is not valid. The command line parameters are invalid. The step failed. - I have a SSIS package run fine in BID, but when run as a sql agent job, it failed at...

SQL Server 2005 : T-SQL (SS2K5)

Problem querying column containing an asterisk - Hi I have a table with a column which is defined as a varchar that contains asterisks. If I query...

SQL Server 2005 : SQL Server Newbies

Split input string into multicolumn - multirows - Hello all - I am having hard time to split an input string into multicolumn - multirows. Task - Create a stored procedure that...

SQL Server 7,2000 : Administration

BCP packet size not working - I have a Window 2008 R2 server 64-bit, SQL 2008 Enterprise 64-bit RTM I am doing large (hundred of millions...

SQL Server 7,2000 : T-SQL

Trimming special characters - One of our developers created this UDF to trim out special characters...is there a better way to do this? ALTER FUNCTION...

Help !! Calculate Rolling average for last 4 weeks - Hi All, I need your help. Database: SQL SERVER 2008R2 I want to calculte for a year and a week the average of...

Using the IN operator with two variable - I would like to explain my problem with a simple example. Here is a part of my database: Type Service ----- ----- Freshwater Habitat Saltwater...

SQL Server 2008 : SQL Server 2008 - General

When was an SSIS package imported? - Scenario: an SSIS package deployed to msdb is accidentally overwritten. The new package was "supposed" to be deployed to a...

Applying Patches - Hi , I have to apply patches on SQL server in cluster ACTIVE/ACTIVE. On 2008R2 (RTM) need to apply SP2 On 2008...

Table has no natural primary key and no clustered index - Hi all I am wondering what is best practice when a table has no natural primary key and no clustered index,...

QUERY HELP - Hi geniuses! I have this query [code="xml"]SELECT DISTINCT chapter, Sum(dbo.FGG.Value) AS FGG2010 FROM FGG WHERE (FGG.YEAR= 2010) and (dbo.FGG.valueType = '02 - FGG01l') Group by...

FLOAT vs DECIMAL.. - Hi, I've a table that stores currency amount values and is used in some calculations (vat, discounts, ....). FLOAT, REAL, MONEY and...

SQL-Weekly data - Hi All, I have 2 date parameters say : Calendar Start Date - 08/15/2012 Calendar End Date - 08/22/2012 I wanted to retrieve results of...

Remove blocking due to LCK_M_S and LCK_M_U - Hi, I am trying to remove a blocking in one stored procedure. We are encountering deadlock because - 1. This SP is being...

SQL Squery- Start day- Monday - Hi All I have a query to fetch the starting day as "Monday" between any 2 selected dates. For eg: Start Date is...

Tuning a query help - Hi all - I am trying to tune the query below, as it was causing an issue on our server. I...

Cant access SSIS packages in MSDB folder via SSMS - [quote]The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when...

Explicit Transactions Vs TransactionOption in SSIS - Hi Friends, I am going to implement Transaction in a package. I thought of using the Transaction feature available in SSIS....

Add Data files dynamically based no of cpu's - Here is the scenario: Step1: Verify number of CPU's SELECT cpu_count FROM sys.dm_os_sys_info Step2: Verify number of Datafiles select COUNT(*)-1 from tempdb.sys.sysfiles...

Configuring Line Graphs in SSRS for color blind? - Has anyone ever needed to make a chart (especially a line graph) in SQL Server Reporting Services user-friendly for color...

Deadlocks and metadata locks - I have a deadlock situation that I am having some difficulty understanding. Attached is the deadlock graph - I am trying...

Suggestions for importing data into a multi-table dataset - I've written plenty of imports over the years, but I'm trying to find the best and fastest way to update...

Report Server Backups - I have recently moved RS dbs to test servers using the detach and reaatach method. What I didn't realise is...

Unwanted parameter truncation - I'm about ready to blow my brains out. I just spent almost a day chasing a weird error in a...

Trigger to update existing records - Hi Team, Am having a trigger, which will update a column when insert / update occurs, for new inserting or updating records trigger...

HOW TO GET THE VALUE OF SQL TABLE ROW INDEX - How can you get the value of a table COLUMN INDEX.

ADD VALUES TO A NEW COLUMN - I have two tables (TRIP and TRIP_DETAIL both have the same primary key called TRIP. There is no FK relationship....

how to find name of a table which has maximum number of transactions for a large database - is there a way to find a table which has maximum no. of records among a table list of 100...

weird memory usage on sql server - Hi Folks, I got this weird issue on sql server. I am running window 2008 r2 enterprise 64 bit with 8 gb...

How to add features to a current SQL Server failover installation - How to add features to a current SQL Server failover installation? Which option do i have to select: New installation or add...

BULK INSERT AND FILE ACCESS DENIED ISSUE - Hello everyone, Little stumped with this problem trying to do a bulk insert into a table and it was working fine...

SQL Server 2008 : T-SQL (SS2K8)

Slow running query - I am a developer and not a DBA. My customers are complaining about how slow my application is running. My...

DBCC Page - Hi, I'm using DBCC Page to investigate data on specific pages. My question is: Does DBCC Page show pages that are...

Partitioning Error - [code="sql"]USE Master; GO --- Step 1 : Create New Test Database with two different filegroups. IF EXISTS (SELECT name FROM sys.databases WHERE name = N'PartitionDB') DROP...

Timeout Expired - I fire a trigger on tblAdmin as CREATE TRIGGER [dbo].[trg_mailupdation] ON [dbo].[tblAdmin] After UPDATE AS BEGIN -- SET NOCOUNT ON added to prevent extra...

combine stored procedures result - Hi all I have two stored procedures which return dynamic columns as result sets. is there any way to combine both resultsets??????? after...

help!!! calculate average rolling 4 last weeks - Hi All, I need your help. Database: SQL SERVER 2008R2 I want to calculte for a year and a week the average of...

CTE Documentation - I've always understood that CTEs were similar to derived tables in that they were simply logical constructs that the optimizer...

Finding a quote in a string - What can I use to find if a string contains a '? I know how to replace the quote with a...

Correlated Subquery - Stream Aggregate - Hi All Some DDL [code="sql"] --Table 1 CREATE TABLE [dbo].[Indexing2]( [Col1] [int] IDENTITY(1,1) NOT NULL, [Col2] [int] NULL, [Col3] [int] NULL, [Col4] [int] NULL ) --Indexes CREATE UNIQUE CLUSTERED INDEX...

If value is null, use previous records value - Hi, I have a table with exchange rates between 2 countries. There's 90 days of history for every exchange rate. I've...

Changing the table structure - Hi, The below table is my source Skill BU1 BU2 BU3 Skill1 0 0 0 Skill2 2 6 0 Skill3 0 0 7 Skill4 4 0 0 Skill5 0 7 8 I would like to convert it into the below table BU Skill Value BU1 Skill1 0 BU1 Skill2 2 BU1 Skill3 0 BU1 Skill4 4 BU1 Skill5 0 BU2 Skill1 0 BU2 Skill2 6 BU2 Skill3 0 BU2 Skill4 0 BU2 Skill5 7 BU3 Skill1 0 BU3 Skill2 0 BU3 Skill3 7 BU3 Skill4 0 BU3 Ski

DISTINCT COUNT based on conditions - I'm trying to get the count of only the Active Consumers for each ConsumerType, based on the ActivityDate. Below are my...

How to determine which items in one table do not appear in a second table - I have two tables, a table of actions and a table of items. They look like this: ActionID EmployeeResponsible 1 12345 2 67890 3...

generating a column for each value in foreign key - hello, i am providing these small example to make myself better understood. [code="sql"] CREATE TABLE #Groups ( group_id bigint NOT NULL, name varchar(100) NOT NULL, grouptype_id...

Append query results to existing table - Hi, I currently run a query daily and have to results going into a table using insert into.. select... The problem is,...

ORDER BY Should be same as my input in IN() - DECLARE @T TABLE(ID INT,NAME VARCHAR(10)) INSERT INTO @T VALUES(1,'PPP') INSERT INTO @T VALUES(2,'AAA') INSERT INTO @T VALUES(2,'ZZZ') INSERT INTO @T VALUES(3,'XYZ') INSERT INTO @T...

SQL Server 2008 : SQL Server Newbies

how to combine multiple stored procedures - Hi all I have two stored procedures which return dynamic columns as result sets. is there any way to combine both resultsets??????? after...

Huge Index growth - Hi All In the last month, I have noticed massive growth on one of the tables in my database. The total...

COUNT and compare - For some reason I've always gotten confuses when using COUNT! I have two tables, one for Orders and then an Assiged...

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

Suspending Database Mirroring - Hi All Does anybody know if suspending db mirroring prevents automatic failover should the primary fail ? I know that Suspending stops...

SQL Server HA with Automatic Failover - My question is wrt to SQL Server 2012 rather than 2008, but there does not seem to be a SQL...

Log Shipping Monitor - Hi, I'm setting up log shipping for a 2008 R2 database. I have my primary and secondary servers installed. For...

SQL Server 2008 : SQL Server 2008 Administration

Index reorg VS rebuild, round 1 - So, I read alot of people saying Rebuild over 30% fill factor 80%! reorg under 30% only! What is the...

advice on best design... - All, I have a database which contains a table for Raw data. Now the data in this table is mainly time....

Adding new data file to tempdb, leads to data incorrection - In one of the tempdb blog [url= http://www.brentozar.com/archive/2011/08/tempdb-multiplefiles-sort/] Here is link [/url], it is stated that sometimes when we add...

Replication Error: 14151, severity: 18, State 1. - Hi, I have like a orphaned publisher trying to push out a transactional request or something. I can't figure this out...

grant permission to view/edit/execute all stored procedure - Is there a way to grant a user to alter, view and execute all the stored procedure in a database? I...

Permission on reporting server - I would like to setup a windows group of people to view reports on a SSRS report server. They can navigate...

SQL Monitor - Is anyone using SQL Monitor to monitor their SQL instances? Per this thread [url=http://www.sqlservercentral.com/Forums/Topic1394075-1550-1.aspx][/url], I'm looking to monitor about 40...

How to find allocated space and used space - Hello Team, How to find allocated space and used space for all databases in sqlserver instance ? Is there any query. Thanks in...

Problem with Datetime Function - I have SP which contains function convert(varchar(12),[datetime],112) =convert(varchar(12),getdate(),112) this takes lots of time during the Market hours and it is very...

Installing SQL server 2008 Express from windows application - Hi I am trying to install SQL Server 2008 Express from Window Application I am using this command [code="other"] "/qs /ACTION=Install /SkipRules=VSShellInstalledRule RebootRequiredCheck /HIDECONSOLE...

SQLServerCentral.com : Anything that is NOT about SQL!

What are you planning to do in 2013 in the database domain? - 2013 is on the way. Warm greetings to all of you first. I am posting the questions here to collect and...

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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

Bar Chart in Table, 10% is longer than 40% and 25% is same length as 40% - So I have a simple table... Month, Budget, Actual, Varaince Percent I created a chart based on the same dataset with...

dynamic data source based off table - Hi, Id like to create a Data source that is dynamic and based on a table. (Ie user opens the...

Database Design : Hardware

Storage Subsystems and RAMDisks - We're setting up a new database server for our OLTP and smaller website databases, and disk performance is a big...

Data Warehousing : Integration Services

How to restrict to stop the package if one of the excel tab gives error - Hi, I have developed a SSIS package. Source : XLSX File Destination : SQL Server Table The XLSX files contains 4 tabs. I am loading 4...

SSIS Error Conversion Datatype Variable - Hi All, I need your help. i wann be crazy !! :) [u]Sql server 2008R2[/u] I Have a variable that I converted into float...

Best Approach For Iterating Through Staging Table - deleting/ignoring unwanted rows - I've attached a screenshot of a selection of records from a staging table I'm using in my SSIS package. I...