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

Free eBook: SQL Server Hardware

Press Release from SQLServerCentral.com

A free eBook from SQLServerCentral and Red Gate software can help you learn about the best hardware for your SQL Server instances. More »

Using SQL Server Concatenation Efficiently

Additional Articles from MSSQLTips.com

This article shares some tips on using concatenation efficiently for application development, pointing out some things that we must consider and look at when concatenating values or fields in our queries or stored procedures. More »

Tracking Database File AutoGrowth with Event Notifications

Jonathan Kehayias from SQLServerCentral.com

 More »

Today's editorial was originally released on Dec 4, 2007. It is being republished as Steve is at the PASS Summit.

This is absolutely amazing; over half a million database servers have no firewall. How can you put up a database server, SQL Server, Oracle, DB2, even MySQL, without a firewall?

How can you put any server on the Internet without a firewall? Even most home routers enable a NAT router and basic firewall these days, not allowing connections in by default. In the last 5-6 years, the technology has been widely available, even to uninformed home users, to not deploy any system on the Internet without protection.

So how do these servers get out there? Are these development systems? Are people opening 1433 so they can test an application or access their remote SQL Servers? That’s what I suspect. Many developers I know are optimists and they don’t expect people to be pinging their servers or accessing their systems in any way other than how it’s designed.

We’ve been hacked here at SQLServerCentral.com a few times over the years with SQL Injection techniques, but never to my knowledge with an attack directly against our SQL Server. For a long time we did have our SQL Server exposed, but not on 1433. It was on a high, random port that was unused by any other service and we had strong passwords on accounts. It was a convenience service, we had login tracking, and I never saw an unexpected attempt in our logs.

However if you run a corporate SQL Server and need to stick servers outside your firm’s firewall in some type of DMZ, at least close off port 1433 to anonymous access. Go spend the $100 out of your pocket for a small router that can at least protect your servers with basic NAT and prevent traffic from getting directly to your database server. It might not be the best solution, but it’s better than nothing.

There’s no excuse these days for putting a server out on the Internet without at least basic NAT protection. Some type of router or firewall should protect every server, and probably every computer, and only allow those services that are really needed. For most servers, this is port 80 and nothing else. Allowing access to SQL Server, RPCs, or any other port that’s not meant for anonymous access, is really stupid.

And if you can’t figure out a way to securely make your service available to partners or customers, then you should hire someone that can. There are plenty of networking professionals out there that can help you set things up correctly.

Know your limits, ask for help, and don’t jeopardize your company’s security because of ignorance, pride, or laziness.

Steve Jones

The Voice of the DBA

The podcast feeds are now available at sqlservercentral.podshow.comto get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted. You can get feeds from there.

• Video Podcast - 29.3MB, Quicktime
• Video Podcast - 12.6MB WMV
• Audio Podcast - 1MB, MP3

Today's podcast features music by Wakamojo, the Kansas band featuring our very own Adam Angelini, DBA from the heartland and SQLServerCentral.com community member.

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.

Steve Jones from SQLServerCentral.com

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

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

Today's Question:

In SQL Server 2008 R2 and earlier versions, the auto updating of statistics rules are:

• Minimum threshold of 500 rows in the table.
• Fixed rate of change with a 20 percent threshold.
• Uses a default dynamic sample rate based on the # of rows.

With the above rules a table containing 1,000,000 rows requires 200,000 changes before the auto updating of statistics is invoked. As a DBA you have determined that this is unacceptable in your situation.  Thus you created and schedued a job to determine the number of changes in a table and if they exceeded your defined limit you could/would invoke an update of the pertinent statistics.  Due to changes in business conditions, data entry staff attendance, etc., etc, you find yourself continually having to alter the schedule of when your job runs. 

In SQL Server 2008 R2 SP1, which one of these is true:

1. Microsoft has altered the 20% threshold for auto updating of statistics. 
2. Microsoft has NOT altered the 20% threshold for auto updating of statistics
3. Microsoft released a trace flag which will change the fixed rate of the 20% threshold for update statistics into a dynamic percentage rate.

By bitbucket

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

This question is worth 1 point in this category: statistics. 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.

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.

What is the output of the following code:

select 
 sum(num1) AS S1,
 sum(num2) AS S2,
 sum(num1) + sum(num2) AS S3,
 sum(num1 + num2) AS S4
 from
  (
   select cast('1' as int) as num1, cast('2' as int) as num2
   union all
   select '1' as num1, '' as num2
   union all
   select '1' as num1, NULL as num2
   union all
   select NULL as num1, 2 as num2
   ) as t

By iBar

Answer: 3,4,7,4

Explanation: Aggregate functions ignore nulls but mathematical operation with one of null in the equation, will result null.

Ref: SUM - http://msdn.microsoft.com/en-us/library/ms187810.aspx/a>

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.

Table Search Based on the Column

Bhargav Sesham from SQLServerCentral.com

To Find all the tables based on a particular column More »

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

SQL Server 2005 : Administering

Daily Differential Backup Job Failing - Good afternoon, I have a SQL Job which performs nightly differential backups of all databases on one of my production servers....

Primary Key datatype Bigint vs uniqueidentifier: - Hi Friends! Can any one suggest me for each tables primary key should be Bigint as PrimaryKey or [uniqueidentifier] as...

Query to long time running........ - [font="Arial"]Hello, I've got to troubleshoot the following query but i'm lost since they've got one select "*" in the beginning of the...

difference between session and task - sys.dm_db_session_space_usage/sys.dm_db_task_space_usage - can someone please tell me what is the difference between session and task ?

SQL Server 2005 : Business Intelligence

Why does a NULL aggregate warning in an SQL query causes the SSIS package to fail? - An SSIS Package was failing with an error message as below: Code: 0xC0202009 Source: DFT Populate ImageSummary OLE_SRC ProductImage [1]...

Cognos impromptu - Hello all, My first post - so be gentle! We use SQL Server 2008 R2 and I'm just beginning to explore BI for...

SSRS Language Report Properties - Where??? - Hi I have created a simple report which does not access any database or even display anything for that matter. I just...

SQL Server 2005 : Development

How to copy production DB into New Developmant Database? - Hi, We have Sql server 2008 R2 and i would like to set up my new test server and copy production...

SQL Server 2005 : SQL Server 2005 Security

ddl_admin - can ddladmin role create tables in a DB?

SQL Server 2005 : SQL Server 2005 Performance Tuning

SQL client performance - When i'm not at a computer which has sql server express installed I have used winsql My IT dept says that...

find % CPU from dm_os_performance_counters - Hi , Iam collecting a baseline report for one of the sql server instance, where i have only instance level access,...

Execution Plan -- Clustered Index Update - When looking at the execution plan of a query it shows a [b]'Clustered Index Update' [/b]@ a cost of 85%....

Performance Dashboard: Unique Compiles - In running the Missing Indexes report from the Performance Dashboard I get one which has a very high value in...

SQL Server 2005 : SQL Server 2005 Integration Services

free ssis video tutorials needed - can anyone give me a good site for free ssis vedios not microsoft site with a lot of example.

SQL Server 2005 : T-SQL (SS2K5)

Not generating run time - I have the SQL, listed below, what I would like to do is to get the fractions down to two...

how to insert image in sql server 2005 - how to insert image in sql server using sql query

List job name which didn't run today but supposed to run today. - Hi, I have one query.i face one issue almost every day. We have lot of scheduled jobs configured ,some of jobs can't...

Time and Attendance need help pls... - Please can someone help me.... I have a query which query the min and max of time in and out...the scenario...

SQL Server 2005 : SQL Server Newbies

Replication Setup in Test Area - Hi All Not used to posting on these things and also just getting used to SQL as a whole, so...

SQL Server 7,2000 : SQL Server Newbies

Some Help getting started with MDX - I have a measure circ that should roll up along some dimensions but there are three dimensions where I do not...

SQL Server 7,2000 : Performance Tuning

SQL2012 caching behaviour - I found out that it seems flush from memory very soon make it unable to use the cache data, results...

SQL Server 7,2000 : Replication

Replications not showing in monitor - I have a new issue that is completely confounding me. The IT guy prepared a VM image of our old sql...

SQL Server 7,2000 : T-SQL

Group by with MAX then other columns - how to control what is returned if there are multiple results - Hi all, I know this is a 'normal' issue that I see littered about the forums alot, trying to get unique...

SQL Server 2008 : SQL Server 2008 - General

delete top 1000 rows from table - Hello I need to delete 1000 rows from table and it should do it batch wise. means i need to loop delete statement...

Using Row_Number() as ID field to select the newest record - I am asking the question as I begin the research on how to do this because I am not sure...

Autonumber on a field that doesn' have increlent or identity - Hello all, Quick question I got a table with a varchar(20) field as primary key the result has to be W0001 W0002 .... The data is...

recovery model - this is always conmfusing me what are the differences between Bulk and simple recovery model ?

Monitor Table Grow - Hello I need to Monitor which table growing for Database. so please help me to do this. I have no idea how to...

Having a SQL 2008 R2 Database in Memory - Hi All, I have looked around and not found enough to answer my questions. And under some pressure to review this...

Retrieve Data with condition - Hi, I have one Table Userid Monthly Quaterly HalfYearly Yearly ---------------------------------------------------- 1 30.00 0.00 0.00 0.00 2 0.00 300.00 0.00 0.00 I want display the...

how to insert image in sql server 2005 - hi, i want to insert image into sql server using sql query. please help. thanks in advance sanjay

Detect Memory Pressure on a SQL Server Instance - Hi, Is there a way to detect from within SQL (i.e. not using perf mon counters) that my system is suffering...

Auto Shrink In SQL Server 2008 - Dear, 1. I like to auto shrink my database. But I dont know how to do it. 2. My current DB size...

windows server 2008R2 SQL SERVER-2008 64 bit - Hi, I have windows 2008 R2 64 bit OS and SQL Server 2008 64 bit. I need to create the Linked server...

ssis package taking ages to open - Hi An ssis package i have been working on has started to take ages to open. It also takes ages to...

problem of " ?" in sql - hello all. I want to search "?" in column of table in sql server 2008 but it dose not find "?" and find...

SQL 2008 R2 - DTS cannot be saved - In windows 2008, SQL 2008 R2 , changes made to open DTS packages. But only sysadmin can modify and save package....

Encrpted Columns in Table - When I run the following it only returns one character for the surname field. Any Ideas why I'm not geting the...

Point in time restore, restoring past the point in time - I've done a search and couldn't find any postings for this. I recently did a point in time restore to a...

Convert Existing Stored Procedure To CLR Stored Procedure - Good day all!!! I have just started learning about CLR Stored Procedures and I wanted to know how to convert my...

How to use ROW_NUMBER() for paging data in procedure - i'm starter in sql server, i write this Query [quote]ALTER PROCEDURE [dbo].[SPSelectReport3] (@StringWhereParameter nvarchar(4000)=null) AS BEGIN SET NOCOUNT ON; -- ? Begin Of Transaction begin tran declare...

passing CSV "array" to a stored proc - Hi, am trying to pass 'Orange,Guava' argurment to this stored Proc. dbo.TESCOS table has two columns, Fruit varchar(100) and Price...

How can I get join not duplicate? - I get join 2 table but use 3 table. i need not duplicate value. but i try join have duplicate. [b]Table...

Log Shipping Alerting - Hello - I have a basic SQL Server 2008 log shipping setup with a Primary Server alert and a Secondary Server...

Database restore - Hi Al, I want to restore a database from one server to another server. Can I restore a database with 8 datafiles...

Cannot open New SSIS Project in SQL Server 2008 R2 - I installed SQL Server 2008 R2 and Visual Studio 2008. However, after several installations and un-installations. I could no longer...

Add minus symbol in sql query - As SQL is not my main job what might seem easy to many here has me stumped.:blush: I have written some...

How many rows in a table before using a clustered index? - Hi All, I've just inherited a database and whilst doing a health check discovered about 300 tables that don't have clustered...

Error in max(substring) - can we add startid, endid SplitLayout startid, endid 1,1 2,1 3,1 4,2 6,3 9,1 10,5 15,4 19,3 22,4 Startid = will start from 1 and will add endpoint for second row 1+1...

Best practices to create databases for different applications - Hi, We are in a process of developing 4 different applications and they are all not related. All 4 application developers created...

SSIS in cluster setup - Since SSIS is not cluster-aware, what is the best approach to configure SSIS in SQL 2008 Cluster. When the active goes...

What would be the major concerns for running WCF services and xp_cmdshell on SQL Server? - All, The development group at my new company is using a WCF service running on the SQL Server to call a...

Money datatype and decimal in .net c# - I am returning a money datatype for sql to a .net app. When I retrieve the value from the output parameter...

SQL Server 2008 : T-SQL (SS2K8)

Top 20 count - Hi all, I was wondering if this is possible, I use the query below which is working great if be...

identity column - hi, my identity column should start with 1,1 . but when i insert data from package , it starts 1133. even if i drop...

How to convert to two decimal places - Hi I have this code here case when chargeType <> 'ADV_SWITCH_FEE' then CAST(isNull(sum(chargeAmount)/100.0, 0.0) AS VARCHAR) else '' end as chargeAmount How do I...

Information_Schema.Columns on All Database - Hi, When I run the following query: [code="sql"]Select * From INFORMATION_SCHEMA.COLUMNS [/code] This only returns results for the Databse it is on. Is there script...

Recursive Update - Help identifiying Where MAXRECURSION limit is blown - I have a website visit log. Unfortunately for a while, a version of the software was recording the date 1753...

Need to populate new column in the result set - Hi I have a table which has 3 unique values as below table. SNo. Date Cat Value 1 11/01 A 100 2 11/02...

Linked Server using WITH EXECUTE AS - I have a stored procedure on server A that uses EXECUTE AS and queries tables on server B. The user...

[MYSTERY] Execution-plan bug? “Schema changed after the target table was created. Rerun the Select Into query.” : Every 6th run! - Hiyah! Before I get to the problem I want to say that we've worked around it by splitting the SELECT INTO...

Give Varibale as column name - Hi , I want to give variable as column name. Example: DECLARE @COPYDATE DATETIME ,@COPYDATE_END_OF_CURR_MONTH DATETIME ,@COPYDATE_END_OF_1_MONTH_AGO DATETIME ,@COPYDATE_END_OF_2_MONTH_AGO DATETIME ,@COPYDATE_END_OF_3_MONTH_AGO DATETIME ,@TODAY DATETIME SET @COPYDATE = '10/31/2012' SET @COPYDATE_END_OF_CURR_MONTH = DATEADD(DAY,-1,DATEADD(MM, DATEDIFF(M,0,@C

New Function - Hey guys... I have this GET_PERSON_AGE Function AS CREATE FUNCTION [dbo].[GET_PERSON_AGE](@in_DOB AS datetime) returns int as begin DECLARE @age int IF cast(datepart(m,getDate()) as int) > cast(datepart(m,@in_DOB) as...

Needed Temp Table for View - I have the need to create a view for one of my applications to look at. The problem is, when...

SQL Server 2008 : SQL Server Newbies

Naming multiple results of one column different name. - Hi, I have a table that, among other columns, there are three in particular; qualifier, segment, and value. the combination of data...

Modifying a SQL 2000 maintenance plan -- and shrinking - I've got a SQL 2000 DB with a plan that was created long ago. On the Optimizations tab it's got...

Removing Null Columns - Hi, I'm trying to remove null columns. The columns that need to be removed are not always the same; I...

SQL Server 2008 : Security (SS2K8)

how to create a sql login and add to database user which already exists - hi everyone , how to create a sql login and add to database user which already exists 1. user "appp" is...

Table Permissioning - Is there a way to create a role having insert/update/delete permissions on each table in a database in such a...

SQL Server 2008 : SQL Server 2008 High Availability

Transactional replication with updatable subscription - Hi, when setup transactional replication with updatable subscription we can see the linked servers created automatically. What is the role of...

Need expert help to setup maintenance plan on multi instance cluster - I am new to cluster environment, wanted to setup some daily maintenance Plan on [b]Dynamics AX SQL Server 2008 cluster...

Trans. Replication issue - Greetings all, I'm having an issue with transactional replication between two SQL2008R2 servers. I keep getting login failed for the...

What credentials are used for mirroring connections between partners? - I know when I configure security for a new mirror, the wizard prompts me for credentials to connect to the...

SQL Server 2008 : SQL Server 2008 Administration

Security question - Is it possible to restrict from adding new members to db_datareader role?

database ownership issues - I have my databases user and default schema set for 'dbo' but if i import a new database , and look...

ATTACHING SQL SERVER_2000 DATABASE TO SQLSERVER 2008 - I TRIED TO ATTACH A DATABASE CREATED IN SQLSERVER_2000 INTO SQLSERVER_2008. BUT THE FOLLOWING ERROR MESSAGE APPEARED. ---------------------------------------------------------------- Msg 5120, Level 16,...

Backup size estimation - We are going to do an upgrade from 2005 to 2008. Is there a way to estimate the size of...

SQL Server Agent service failed to start, windows 7 - Hello, Please help me to fix the issue. Log file pasted below. 2012-10-20 20:46:17 - ? [100] Microsoft SQLServerAgent version 10.0.1442.32 (x86 unicode retail...

Career : Certification

70-461 - Hi, Id like to take this exam and looking for some good prep.. My T-SQL skills are basic at present...

Microsoft Certified Master - There's been a lot of debate recently about whether certifications are worth doing and whether they provide you or your...

Programming : General

Call DB2 stored procedure using Linked Server? - Hi Everyone, Is it possible to call a DB2 stored procedure using a linked server? I've read a bunch of conflicting...

Programming : Service Broker

Service broker filling up Tempdb - I’m new to SB. Never had issues with it previously. I have 2 service broker tasks/queues one works fine and I...

SQLServerCentral.com : Anything that is NOT about SQL!

How's this for a Job Description - So I recently got this job description in the mail and at first glance it looks pretty good, but at...

Not enough work? - Just curious, has anyone ever had to quit a job because they just can't keep you busy?:doze:

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

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

Database Design : Virtualization

VM and Performance Isolation - To what extent can performance be isolated on the same VM for different SQL Server servers? So if I have...

Data Warehousing : Strategies and Ideas

Unknown dimension values or - In your DW do you always create an "-1 Unknown" record for each dimension? For example in DimEmployee: [code="plain"]EmployeeKey -1 EmployeeName "Unknown" ect.[/code] Or...

Data Warehousing : Analysis Services

Best book to start off MSBI - I have some idea about Integration services and Reporting services. I couldnt get a chance to work on SSAS practically. I...