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

Encrypt Everything

Security is a problem with so many of our applications and systems. There are numerous ways that we handle access and protection of data, often with access rights or encryption (or both) being applied to data in order to limit who can access the data. However neither of these systems is perfect for a variety of reasons and no matter how we configure our security, it seems there are always issues.

Yahoo announced recently that they are trying to improve their security by encrypting all data that moves between their data centers. Other large internet companies do this, though not all. However, this doesn't necessarily mean that your data is much more secure than it was before. This should make it more difficult to access data while it is transiting networks outside of Yahoo's control, but there are still potential issues. Just as with TDE, any legitimate user inside a data center that has access to the LAN or systems inside the data center can still potentially read the data.

I'm not picking on Yahoo here as the same issues might exist with Microsoft, Google, or any service provider that encrypts data between its facilities. This system also suffers from the potential compromise of the keys used to encrypt traffic if any employee were to sell, disclose, or lose a copy of them on laptop.

However this is a good start, and it does mean that the NSA or any other organization that looks to read data in transit must work harder to access your data if it's encrypted. I think it's such a good idea that I think we ought to start encrypting all traffic by default. LAN, WAN, whatever. We've had tremendous advances in hardware and I'd argue that most of us have more powerful hardware than we need. If we decided t take the hit to encrypt all traffic now, we'd become used to the overhead and we'd have better security overall.

I'd love to encrypt all data on disk, but I know people get nervous about losing data. A good start, however, would be to ensure all data in transit is protected.

Steve Jones from SQLServerCentral.com

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

Video and Audio versions

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.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Steve Jones

Windows Media Video ( 19.2MB) feed

MP4 iPod Video ( 22.4MB) feed

MP3 Audio ( 4.6MB) feed

Feeds are available at iTunes and Mevio

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

SQL Monitor

What does normal look like on your servers?

New benchmarks in SQL Monitor instantly show you if a performance spike is a problem, or within normal SQL Server behavior. Try it live.

SQL Prompt

5 ways to code effortlessly

Discover the different ways you can make writing, exploring, and refactoring SQL code refreshingly effortless with SQL Prompt 6. Download a free trial.

SQL DBA Bundle

Save 45% on our top SQL Server database administration tools.

Together they make up the SQL DBA Bundle, which supports your core tasks and helps your day run smoothly. Download a free trial now.

Featured Contents


The Zero to N Parameter Problem (Sql Server 2005 and Up -- Update)

Sloan Holliday from SQLServerCentral.com

This article uses XML to deal with optional parameters along with multiple parameter passed into a stored procedure. (Note: We are re-running this as we had a newsletter issue last week) More »


Import UTF-8 Unicode Special Characters with SQL Server Integration Services

Additional Articles from MSSQLTips.com

If you work with large scale SSIS ETL processes and sequences, you are bound to have to work with UTF-8 encoded text files. Without proper handling, UTF-8 / Unicode characters can cause havoc with your SSIS load tasks. Here are some ideas for handling the UTF-8 file formats with SSIS. More »


Webinar: T-SQL Tips and Tricks

Press Release from PASS

Nigel Sammy will be presenting this PASS Data Architecture VC webinar on December 19 at 12PM CST. The webinar will share insight on how basic query structure and logic works so you can avoid wasting too much time on trial and error when writing queries. More »


Congratulations to your Apology Contest Winners

Steve Jones from SQLServerCentral.com

A congratulations to the winners of our contest last week. More »


From the SQLServerCentral Blogs - DBA JumpStart – A SQL Community Ebook for DBAs

John Sansom from SQLServerCentral Blogs

If you could give a DBA just one piece of advice, what would it be? I asked 20 successful and experienced... More »


From the SQLServerCentral Blogs - Collecting Historical Wait Statistics

Patrick Keisler from SQLServerCentral Blogs

As a DBA, I'm sure you've heard many times to always check the sys.dm_os_wait_stats DMV to help diagnose performance issues... More »

Question of the Day

Today's Question (by Steve Jacobs):

Considering the query below, what are the 13th "terms" for the arithmetic sequence and geometric sequence?


1.  Developed and tested using SQL Server 2008 R2 SP2

2.  "term" = series sequence value

3.  The process does not consider negative values.

declare @start as float
declare @commonDiff as float
declare @counter as float

set @start = 1
set @commonDiff = 3

PRINT 'Begin';
set @counter = 0 
WHILE @counter < 200
  set @counter = @counter + 1
  declare @currVal as float
  set @currVal = 0
  --Perform conversion here for base sequence "series" value
  --for arithmetic sequence
  set @currVal = Case When Abs((@start + (@commonDiff * (@counter -1)))
    - Round((@start + (@commonDiff * (@counter -1))), 1, 1)) * Power(10, 1) = 5 
    Then Ceiling((@start + (@commonDiff * (@counter -1))) * Power(10,1))/Power(10, 1
    Else Round((@start + (@commonDiff * (@counter -1))), 1)

  Print 'Current counter: ' + convert(char,@counter)

  --Arithemtic Sequence
  Print 'Arithemtic Sequence: ' + 
  --Geometric Sequence
  Print 'Geometric Sequence: ' + 
    convert(char,convert(bigint,(@start * (POWER(@commonDiff,@counter-1))))) 

  Print CHAR(13) + Char(10)

PRINT 'Done';

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 2 points in this category: T-SQL.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.


Microsoft SQL Server 2012

Teach yourself SQL Server Code Name Denali—one step at a time. Get the practical guidance you need to build database solutions that solve real-world business problems. Learn to integrate SQL Server data in your applications, write queries, and develop reports. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I have an AlwaysOn availability group for my Sales and Finance databases set up on the SQLProd01 instance. I decided that I also want to mirror the CRM database on the same instance to another server. Do I need to create another endpoint?

Answer: No


All of the Availability Groups and Database Mirroring sessions use a single endpoint on an instance.

Ref: The Database Mirroring Endpoint - http://msdn.microsoft.com/en-us/library/ms179511.aspx

» Discuss this question and answer on the forums

Featured Script

Figure out the Blocking issue

Rajib Kundu from SQLServerCentral.com

Please run the query which is showing the Host IP , BlockingSessionId, BlockingUser, BlockingSQL as a text , WhyBlocked, BlockedSQL and Database name. Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance. The below query will help out to get the root cause why it is Blocked


More »

Database Pros Who Need Your Help

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

SQL Server 2014 : Development - SQL Server 2014

Will SQL Server 2014 Support to Read BLOB (Byte Array) - Hi, Will SQL Server 2014 Support to Read BLOB. As we see in oracle we have some concept called [b]DBMS_LOB[/b]. Where the byte...

SQL Server 2012 : SQL 2012 - General

Always On Secondary Replica licensing - Hi, I am hoping someone might be able to help me: Under a SQL 2012 Standard edition: 1) Can you configure Always...

two databases, identical schema, migration question - I have a client who is thinking of combining two separate databases into one larger one. The schema are identical...

Need ideas on how to handle the inserting of millions of rows per day - I'm looking for ideas and curious how you handle at your company the inserting of large numbers of rows. Our scenario...

notfication service - CAN sql 2005 notification service be install on 2012 sql server? Where do I find 2005 notification service?

SQL Server 2012 : SQL Server 2012 - T-SQL

select house_no order - i have table coloum like below [b]House_no[/b] 3-4-53 . HYDERABAD 3-4-199/1.ASVV 3-4-vizag 3-4-22,nagar 3-4-45.old colony 3-4-66/99 3-4- 2/4 2-4 3-5-hasd 3-6van 4-5-123asvr select house no order i want output like below format [b]House_no[/b] 2/4 2-4 3-4- 3-4-vizag 3-4-22,nagar 3-4-45.old colony 3-4-53.HYDERABAD 3-4-66/99 3-4-199/1.ASVV 3-5-hasd 3-6van 4-5-123asvr

select quarie between houseno .......... - i have table like below House_No 4-12-1000 4-12-55/b 4-12-1456/b/c 4-12-12 4-12-1398 4-12-23 4-12-98 4-12-1499 1-4-33 3-9-55 2-5-89/3 i want out put like select only in between houseno ' 4-12-1000' to '4-12-1500' EX- House_No 4-12-1000 4-12-1398 4-12-1456/b/c 4-12-1499 plz write sql quarie...........

select only up to first '-' only - I Have table like this...... House_no 6-1 6-1-2 6-1/2 6-1/78 6-12/5 6-3-124 ,hyderabad 6-3/2 6-3-19876 ,yuyu select only first '-' only............. I WANT OUTPUT LIKE THIS order ........... 6-1 6-1/2 6-1/78 6-3/2 6-12/5

display order by houseno - i have table house no like this below coloum......... house_no 3-13-10 3-13-200/a 3-13-450/a 3-13-1345/b 3-13-12 3-13-4 3-13-567/c 3-13-832/a 3-13-943 i want table like this.... 3-13-4 3-13-10 3-13-12 3-13-200/a 3-13-450/a 3-13-567/c 3-13-832/a 3-13-943 3-13-1345/b pl write the quarie..................

display order by like 1,2,3,4,5...............plz write quarie - Examples of values I want to put in order houseno 3-13-1 3-13-3/a 3-13-3/b 3-13-2/a 3-13-4 3-13-6 3-13-5/a 3-13-4/c i want output like this below houseno 3-13-1 3-13-2/a 3-13-3/a 3-13-3/b 3-13-4 3-13-4/c 3-13-5/a 3-13-6

SQL Server 2008 : SQL Server 2008 - General

sqlsrv 2008 replication issues .help needed urgently - I have to replicate 14 tables and out of 14 only one table is refusing to replicate. Its transaction replication...

steps to move existing MSDTC from MSDTC group to sql sql group? - Hi, OS.: Windows 2008 sp1 Can any one provide me steps that need to be follow for move existing MSDTC from MSDTC...

database attached to sql server not found - i hve sql208 r2 on windows 7 i had a database attached on the regestered sql server but few days back when...

Import many column CSV into 2-column sql table - Hi there, I have a source file that has many columns (25) and want to find the most efficient way to...

Help with this CLR stored proc - Hi, please somebody help me with this store procedure. I am very confused. This is a CLR stored proc. I...

DatabaseMail - I am trying to configure database mail through my gmail. Before it works fine to me but now when I...

substitution variables to temporarily store value - I am looking for "substitution variables to temporarily store value" with sqlcmd in sql server . I know the query in...

Table join / pivoting question - I have a table that contains 96 status columns, one for every 15-minute period of the data. I also have...

Entering Value by the User - I am trying to write a query which user be able to enter data after getting message for example: "Enter...

Select is Slow - Having a table with 100 columns and 1 million records. Select Count(*) from Mytable; COUNT(*) ---------- 1001920 Time taken to return is 15...

Select from table and alter result - How do I select from a SQL Table and change the result? For example.. declare FindFragment cursor for SELECT object_id, name FROM #IndexFrag...

SQL Server 2008 : T-SQL (SS2K8)

Performance Opinion - Hi all, I have created a SP in which frequentylr transaction will occur.. Can you please look at it and tell me...

SQL Server 2008 : SQL Server 2008 High Availability

How to trick Mirror? - This weekend we are moving standalone sql server (SS) (2008R2 64 bit Ent) to 2 node cluster (CS). Current server...

SQL 2008 cluster - Hi all, We have a single node sql 2008 cluster, now we want to add a new node to have a...

SQL Server 2008 : SQL Server 2008 Administration

How to capture DNS alias info - Hi, Is there a way in Sql server to capture the "DNS alias" being used in the connection string? Applications are...

SQL Server 2008 : SQL Server 2008 Performance Tuning

Stored procedure excution plan reuse - I have a very complex stored procedure that it takes about 45 seconds to run the first time, and after...

SQL Server 2005 : Administering

Online table partitioning - Dear All, To improve performance for some tables having huge amount of historical data we have considered using the file partitioning...

sp_send_dbmail attachments - Very strange one which I cant see a definitive answer for, apologies if its around here already btw I have a...

SQL Server 2005 : Development

COM class error - Hi, In a page, I written a code to generate Excel file using Com+ Component. On server, when my source code...

how to create unique nonclustered index on partitioned table without including partition column - I have a table 'fddb' with 4 million rows. For better performance we thought of partitioning the table.It has clustered...

SQL Server 2005 : SQL Server 2005 Integration Services

Put data directly into DataTable or dot net object ? - Currently, I just create an OLEDB connection, run a query on it and load the result set into a SSIS...

SQL Server 2005 : T-SQL (SS2K5)

Time issues - Hello, I have a sql query(using 2005). Please see the grid below. I need to only show time on the hour...

Reporting Services : Reporting Services

What are the Pros and Cons of SSRS subscriptions? - Where I work, we've been living with SSRS 2005 for quite some time. (Who knows if we'll ever upgrade....) For all...

Reporting Services : Reporting Services 2008 Development

ssrs pass values between reports - In a ssrs 2008 R2 report, I need to pass parameters to existing subreports that are currently called. Basically there...

ssrs 2008 export to excel and csv only - In an SSRS 2008 r2, I am going to have some selected colunmns set as invisibile while the ssrs 2008...

Data Warehousing : Integration Services

ssis for each loop into files help - Hi, I have the following table [code="other"] CREATE TABLE [dbo].[Orders]( [orderNo] [varchar](10) NOT NULL, [orderLineNo] [int] NOT NULL, [product] [varchar](10) NULL, [orderQty] [int] NULL, [receivedQty] [int] NULL, [orderStatus] [varchar](10)...

Data Warehousing : Strategies and Ideas

Updating Historical Information - I have to create a slowly changing dimension with the caveat that any of the data attributes can change for...

SQL Server 7,2000 : General

Bizarre issue while using datetime variable instead of harcoded value - Hi, I am facing a bizarre issue while testing a query, When i test with hardcoded date value, the query...

OT - Learning Oracle 8i - Hi all, We now have a new digital phone system that using an Oracle 8i database. I've used Microsoft SQL for...

Career : Certification

Certifications on SQL server 2008 or 2012 ? - Hi Expertz, So far i have took any cerifications , now I need to take certification as they are going to expire....

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com