SQLServerCentral - www.sqlservercentral.com

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

The Voice of the DBA

Who's Got Your Data?

The headlines lately have been filled with the plight of Edward Snowden, along with his disclosures on how the US National Security Administration (NSA) has been gathering, cataloging, and examining all sorts of data about people in the world. It's not just terrorists, criminals, or anyone suspected of illicit activities, but also many ordinary people that may not feel they should have been subjected to this level of surveillance. Whether you think this was a proper way to disclose this information or not, there's a separate issue here.

We are producing a tremendous amount of data about ourselves all the time. There are so many ways in which companies can gather data points about us, often with logging activity that we might not find intrusive. Our houses produce logs of electrical activity along with the various types of services we might use (water, trash, etc.). We use cell phones, whose locations and usage are recorded, and we often use on line services for mail, research, entertainment, and more that all produce logs of our activities. Purchases on line are stored, and purchases off line might be stored if you use any type of loyalty card. I can imagine it not being long before any card purchase can be linked to the actual items themselves, regardless of whether you want this to occur.

We can add in any services we use that store data in the cloud, from physical activity to medical information to even your location from status updates. While much of this data is stored separately, and not necessarily aggregated, that might not be the case in the future. The government could potentially request this data, which is unnerving to me, but what is more disconcerting is the idea that businesses might engage in complex deals to share much, or all, of your data without you knowing about it. This could be under the guise of providing better services, which makes sense, but that's not what concerns me.

What concerns me is the lack of care that so many companies take with our data. It's lots constantly, and the more data that might be shared between companies, and need to be transformed and loaded into new data warehouses, the more people that will touch this data. And the more developers that will have copies of it on their laptops as they build new applications. It's scary to think about the lack of control we have, and now many mistakes will be made in the future.

I wish I had a good suggestion on how to improve the situation, but I don't. However I do think disk encryption, for all machines that touch data, is a good place to start.

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. Feed links are listed after each format.

Everyday Jones

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. You can also follow Steve Jones on Twitter   to find links and database related items and announcements.

ADVERTISEMENT
SQL DBA Bundle presents Hard Earned Lessons

‘10 Tips for Efficient Disaster Recovery’

Learn Steve Jones’ disaster recovery lessons, and be better prepared for future disasters, big or small. Read now.

SQL Compare

Compare and sync databases with SQL Compare

“SQL Compare is fast, extremely easy to use, full-featured and affordable. I wouldn't bother messing around with anything else.” Adam Machanic, SQL Server MVP. Download a 14-day free trial.

SQL in the City

SQL in the City is going on another US tour

SQL in the city is coming to the US, sign up to receive free SQL Server training from industry-leading experts and MVPs. Register for an event now: Pasadena -10/9, Atlanta - 10/11, Charlotte - 10/14.

Featured Contents

 

Stairway to Database Design Level 1: Data Elements

Joe Celko from SQLServerCentral.com

Before you start to think about your database schema or tables, you need to consider your data: the type of data it is, the scale you use for values. It needs to be unique, precise and unambiguous. Then you need to name it in such a way that it can be generally understood. Joe Celko explains... More »


 

Comparing Data Warehouse Design Methodologies for Microsoft SQL Server

Additional Articles from MSSQLTips.com

In this article, Arshad Ali goes intp detail about how a data warehouse is different from operational data store and the different design methodologies for a data warehouse. More »


 

SQL Server Central Webinar Series #24: Prepare for When Disaster Strikes

Press Release from SQLServerCentral.com

Our next webinar on July 23rd has Steve Jones presenting on some disaster stories with ideas on how you might prevent, or mitigate the effects, of those problem situations. More »


 

From the SQLServerCentral Blogs - SQL Server – How to connect to SQL Server when ‘sa’ account is disabled

Vishal.Gajjar from SQLServerCentral.com

If you have lost the password for sa account or does not have any administrative account and you are locked... More »

Question of the Day

Today's Question (by Kathiravan P):

What happens when this code is executed?

CREATE TABLE #MainTable
(
    ID INT,
    Val FLOAT
)

INSERT INTO #MainTable SELECT 1, 1.23
INSERT INTO #MainTable SELECT 2, 2.45
INSERT INTO #MainTable SELECT 3, 3
INSERT INTO #MainTable SELECT 4, 4.3

-- Statement 1
SELECT * FROM #MainTable WHERE Val LIKE '%'
GO

-- Statement 2
SELECT * FROM #MainTable WHERE Val = '%'
GO

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

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

SQL Server 2012 Query Performance Tuning

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Revenant):

DECLARE @i INT = 32767;

DECLARE @s TINYINT = 0;

PRINT @i & @s;

What will be printed?

Answer: 0

Explanation: If the left and right expressions have different integer data types (for example, the left expression is SMALLINT and the right expression is INT), the argument of the smaller data type is converted to the larger data type. In this case, the TINYINT expression is converted to an INT.

Ref: http://msdn.microsoft.com/en-us/library/ms174965.aspx


» Discuss this question and answer on the forums

ADVERTISEMENT

SQL Server Concurrency

If you've designed your SQL code intelligently, and implemented a sensible indexing strategy, there's a good chance your queries will "fly", when tested in isolation. In the real world, however, where multiple processes can access the same data at the same time, SQL Server often has to make one process wait, sacrificing concurrency and performance, in order that in order that all can succeed, without destroying data integrity. Transactions are at the heart of concurrency. I explain their ACID properties, the transaction isolation levels that dictate the acceptable behaviors when multiple transactions access the same data simultaneously, and SQL Server's optimistic and pessimistic models for mediating concurrent access. Pessimistic concurrency, SQL Server's default, uses locks to avoid concurrency problems.

Get your copy from Amazon today.

Featured Script

FileNameSplitter

William Talada from SQLServerCentral.com

Use this function to break a full file name into four parts: a drive, a path, a filename, and an extension.  It handles any missing parts.  The function returns a table with the four parts as columns so you can use "cross apply" if needed.

select *

from dbo.FileNameSplitter('D:\MyDir\MySubDir\MyFile.MyExt')

It returns

"D:" as drive

"\MyDir\MySubDir\" as the path

"MyFile" as filename

"MyExt" as extension

OR try this:

SELECT physical_name, x.*
FROM sys.database_files df
cross apply (select * from dbo.FileNameSplitter(df.physical_name)) as x;

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

Shrink Database Log files (Full recovery mode) - Hi; Team; we have arround 50 databases in our SQL server 2005, all databases having full recovery mode. Problem is the Log...

Slow Database - Hi all, We have two database one test and other live. Test database running fine but due to high transaction on live...

Drop All Users in the Database - Is there a way to drop all users in a particular database instead of deleting each.

SQL Server 2005 : Backups

Automate restore script - Hi, I'm using Ola's backup script (http://ola.hallengren.com/) to backup the database. The problem I'm facing the Restore process. It took 4 hours to...

Snapshot Backups and Transaction Logs - Here goes my first post in here...I have recently slipped into the "Accidental DBA" role and my first task is...

SQL Server 2005 : Business Intelligence

Need help regarding SSRS , SSIS and SSAS concept - I am working on SSRS reports.We have a OLTP database in which we have created stored procedure for each report.These...

Tables Refresh Solution - I have 2 database tables. 1) From Production 2) From Dev. The task is to move the data refresh from Production to Dev...

SQL Server 2005 : Data Corruption

Page Restore - Hi experts, whats the best / easiest way for me to actually corrupt a page in my database (on local test...

SQL Server 2005 : Development

How to retrieve the Binary Image from database with out affecting database performance - Hi, We have to retreive the Binary Images from SQL Server 2005 database with out affecting the database performance. Which...

SQL Server 2005 : SQL Server 2005 General Discussion

Can you please help me?? - I have a customer table select * from customer and i need some specific recid member details so i can write...

SQL Server freezes (because of application) need logging - We have an application running ontop of our SQL Server 2005 instance and a couple of times a week this...

Server log error - Hi, My company user complain to me about the web application getting error every day on certain time. So I check...

SQL Server 2005 : SQL Server 2005 Security

View and User-Rights (Application Role) - Hi all, I'm not sure how to explain my problem properly, so I'll start from the scratch: I have a database - call...

SQL Server 2005 : SS2K5 Replication

sp_AddMergePushSubscription_Agent fails with DBCC error - Hi, I have a SQL2005 publication that is merge replicated out to various subscribers (all SQL2005 Express/Workgroup). Recently, I have found...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Deadlock on single rows update and select statement - Does anyone have any suggestion on these deadlock ? It seem like the desp_status and RUN_NO is the cause of the...

Execution Plan being changed. - Hi, When I am creating the index on 1 table of the sp, it is executing faster. after creating the Index,...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS: Custom Logging Using Event Handlers - hi im trying to create an event lof for when thinks fail in my ssis package im using the following code...

SQL Server 2005 : T-SQL (SS2K5)

sp to move data between matching tables - I find myself having to move specific portions of data from a final staging table to a live table on...

SQL Server 2005 : SQL Server Newbies

Order of rows in a Table with Identity Column. - I have a table with an Identity Column as TransactionId and also it is a primary key. The table has around...

Linked tables in Acess from Sql server (call failed) error!!! - i have an access db which has appx 25 linked tables which read from a SQL server db, i have...

SQL Server 7,2000 : Administration

Database in suspected mode - Hello, Can some one please help My production database in suspected mode. How can I fix it? Thank you

SQL Server 7,2000 : In The Enterprise

Connection encryption between SQL Server and BackupExec - We are in the mists of securing all internal communications via encryption, a security requirement by law for us... We are...

SQL Server 2008 : SQL Server 2008 - General

CPU Flat lines and numerous Time Outs - Sorry for the long post... but here goes: We have recently migrated from a physical 2 node cluster environment to VM...

Effect of Clustered Index on Non-Clustered Index - Not remotely a DBA so forgive me if this is a novice question, but will having a clustered index in...

How to get total of months, like Year to Day in cross tab query? - I used code below to create cross tab query. It works fine. How to add code to get total of months,...

Space Consumed by table - Hi, A table has two VARCHAR(100) column. Number of Records 2100000, Disk Space Consumed 222MB. It is having Varchar data of...

SqlServer 2008 Processor Affinity - Hello All ... I am looking for some advice on CPU affinity settings in SqlServer 2008. I have a 2 node...

Log on Index Creation? - I am trying to determine when some indexes were added to a table. Is that logged anywhere? Thank you.

The system has rebooted from a Automatic Server Recovery (ASR) event. - We are facing a frequent restart of our production server once in a month or so. The event log is...

SQL 2008 express installation using command prompt - Hi I am trying to install sql server 2008 express using command prompt from my .net application This is the command [code="sql"] Arguments='/q /hideconsole...

summary - alter procedure [dbo].[pocc] ( @empid nvarchar(10), @department varchar(10) ) as begin create TABLE #TempEmployees ( date datetime, eid int, remarks varchar(50) ) -- Insert result from the SP to temp table INSERT INTO #TempEmployees EXEC...

Multiple SQL Standard Instances on 4 Processor/32-core Server - We have a large 4 processor/32-core server with 192GB of memory available in the data center and over twenty small...

Linked server from SQL to Progress? - Hi, What driver do I need to connect SQL via a linked server to a Progress database? This is so...

Hi we are getting errors and failed status for sqlserver r2 installation need help - HI, we are getting errors and failed status for sqlserver r2 installation need help message we got in display window at last...

SQL Server Replication - Good Day. We configured a pull Replication with the Distributor residing on the Publisher. I am able to see transactions...

Query execution plan - what is Query execution plan? how to trouble shoot the slow performane of queires ?

How to Store Data in Database using Data table - c# - Insert data from DataTable to database table

possible or not - how to use Table in Parameter for store procedure

Table Partition / Index Partition - Hi , What is main difference between Table Partition and Index partition. Thanks, RR

NULL datetime field. Is it possible? - When I query a datetime field, the result is a date and time. SELECT SentDate FROM MyTable. Result = '2013-07-01 12:32:47.000',...

Import multiple files into multiple tables - Dear DBAs, I have 200 flat, text files. Each one has different fields separated by one specific charactere (¶). The name of...

Massive slowdown in SELECT statement in Cursor from SQL 2000 to 2008 -- need help! - I have a SELECT statement in a cursor in a stored procedure that ran extremely fast in SQL Server 2000....

Partitioning on Existing table with non clustered index in date column. - Hi All, I have one table with 10 lacks records. I partitioned that table on CreatedDate column with non clustered index (...

tempdb keeps growing - I have configured my tempdb as follows: 8 data files with an initial size of 14GB each. This was based on...

Agent Job Links to Another SQL Server and Fails due to Service Account Privs - To my friends in the community, here is a stumper that I can't seem to find sufficient information on despite...

SQL Server 2008 : T-SQL (SS2K8)

Query - literal date vs local variable - I have a query that pulls invoice data. I'm looking for invoices with an invoice date > blah (where blah...

Product Name and Quantity with the maximum quantity sold per day. - Write a SQL statement from the above example tables that will return the Product Name and Quantity with the maximum...

Write a SQL statement that will return all the Sales Orders for the Salespersons’ with the name starting with ‘John’. - Write a SQL statement that will return all the Sales Orders for the Salespersons’ with the name starting with ‘John’. Below...

daily sum aggregation please help - Write an SQL statement that will perform a daily sum aggregation on the field cost. It must only use costs...

how to insert foreign key value in my table? - Hai friends, i ve two tables create table users ( users_id int primary key, username varchar(20), password varchar(30), department varchar(10), designation varchar(20) ) insert into users values('0001','Ram','ram@123','IT','programmer') insert into users...

substring comparison for last 2 characters - I have a field with data like below I need to join on patid 123453 124344ME 323390 3233MS I need to remove the last 2 charcters...

compare SQL syntax but need the total and %??? - Hello SQL GuRu's, A few weeks ago I asked the following (http://www.sqlservercentral.com/Forums/Topic1459631-150-1.aspx?Update=1). For this I had received a clear answer and...

Dynamic Vertical Rows to Horizontal - I read through the following topic already, and it has moved me MUCH closer to the solution that I'm seeking...

Extract first numeric value from a string - Brothers, I need to parse the first numeric value from a string that usually contains several numerics. This function works OK when...

SQL Server 2008 : Working with Oracle

is it possible to have Single instance(name or SID) on multiple database? - Hi Folks, I wanted to know that can we have a single instance on two different databases? I am till now aware...

SQL Server 2008 : SQL Server Newbies

How to compate cross column values - Hi, I am having requirement for to check cross column values, Below is the example table col1 col2 col3 col4 1 2 1/7/2013 5/7/2013 1 2 8/7/2013 9/7/2013 1 2 9/7/2013 10/7/2013 1 2 11/7/2013 12/7/2013 Out put should be as below col1 col2 col3 ...

Configuration Manager; nothing under SQL Server Services - Honestly, I give up. I can't connect to this database as sa ('a network-related or instance-specific error occurred error 40...

Filtering on SQL Views - Hi All Looking for a little help. I have a crystal report that is based on a View on my SQL Server...

Performance Tuning on Very Small Databases - When is it worth it - When does rebuilding an index make sense? When does Shrink Database make sense on a Very Small Database? Some say...

Connect SQL Server 2012 with Database Engine - Hi all, Installed SSMS 2012 Express addition with localDB. I don't know how to connect with Database engine and start using...

Explanation of LIKE '%[0-9]%'? - From time to time I need to check if a column is completely numeric (or usually, check for the row...

SQL Server 2008 : SQL Server 2008 High Availability

Windows active directory upgrade impact on Windows SQL cluster? - Hi, we are about to have an AD upgrade from our current Windows 2000 SP4 domain to Windows 2008 R2...

SQL 2008 - Remove cluster node - Apologies if this is a daft question (never a good start!) Had a thought earlier, we have a very simple active...

SQL Server 2008 : SQL Server 2008 Administration

change dbo login name - I have a database that I need to change the dbo owner from user A to user B. I'm getting...

Rebuilding / creating indexes as a bluk-logged operation and point-in-time recovery - So, skimming blogs this fine day, I came across a suggestion to switch a DB from Full Recovery to Bulk-Logged...

Snapshot agent failing: Cannot promote the transaction to a distributed transaction - Hi, I'm receiving the next error when i create my publication snapshot (my publication only have a table/article): " Error messages: Message: Cannot promote...

dbWarden questions - Hello -- I am trying out the dbWarden application by running it within the 2008 Standard R2 SQL Studio application. It...

How to troubleshoot error 3041? "BACKUP failed to complete the command BACKUP DATABASE XXXXX. Check the backup application log for detailed messages." - My backups have been failing and I noticed the following in the SQL ERRORLOG [quote] 2011-09-21 22:13:49.70 Backup Database backed up. Database:...

Programming : XML

Need Help getting baddly structured XML file from web into table - I have no clue what I'm doing, I use to be able to program > 20 years ago but all...

LOADING COMPLEX XML FILE - The file below came from a web request I want to load it into SQL Server but it is too...

Programming : Powershell

Update Table with get-date - Hi, i want to update my table Field Date_Import=get-date -f (without time) TBL_Import [Count_Import] int NULL, [Count_Fault] int NULL, [Date_Import] date NULL $Command = $Connection.CreateCommand() $Count_Fault...

SQLServerCentral.com : Anything that is NOT about SQL!

Forum Topics Order - Just kind of wanted to throw this out there and see what others think. Since SQL Server 2012 has been...

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

"Advanced Search" for Scripts - I would appreciate some kind of advanced search on "scripts", or maybe a "search within results" feature. We have a...

Reporting Services : Reporting Services

SSRS 2008 r2-Unable to connect to the remote server - We installed SSRS 2008 R2 standard edition on a test server (windows server 2008 R2). We added clients and assigned...

Setting same filter on Chart gives different results than setting on datset - HI I am using SSRS 2102 and trying to make a series of sales charts that first show regional data and...

Internet Explorer 10 and SSRS 2005 - I have Reporting Services 2005 running on Windows Server 2003 and IIS 6. Everything looks fine with Internet Explorer 9,...

Reporting Services : Reporting Services 2005 Development

how to set the 'Select All' option as default selected in multivalue parameter? - :(

Data Warehousing : Integration Services

For Each loop not enumerating recordset correctly - Guys, I am importing data from a flatfile source which has a header record, some data rows and a trailer record...

50+ tables need to be copied using Data Flow task. - Hi, I need to move data from one server to another server, there are 50+ tables to be copied. For...

Run package when email is received or based on database value? - Hi. I've seen a couple of questions/answers here describing how to use a script to check a folder and see...

Header row is different than detail row - Hello Folks, I have a file with a header row that is different than all the other detail rows. ABC 20130627 123 1|A|20120102| 1|A|20130301| 3|C|20030102| 5|S|20111111| It...

Best way to flag an already loaded record using SSIS - Hi there! Creating an alert dashboard that updates every 15 mins as a front end. As the database back end I have...

SSIS structure for Documentation - Hi, I am trying to extract some information from SSIS packages store on the server in [msdb].[dbo].[sysssispackages] For all packages, i need...

The column "Column 2" cannot be processed because more than one code page (65001 and 1252) are specified for it. - I get this error if i try to import a tab delimited flat file to a table in sql server. [i]The...

Microsoft Access : Microsoft Access

Access Query ported to SQL - Here is the "inner" part of a query in Access. UPDATE [Vehicle Detail] SET [Vehicle Detail].ReturnDate = #4/29/2013#, [Vehicle Detail].ReturnSource = 'ReturnProcess' WHERE ((([Vehicle...

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