Double Counting and Sign Reversals

Dwain Camps from SQLServerCentral.com

Make the ad-hoc analysis of identifying potentially double-counted or sign-reversed Accounting transactions simple and painless. More »

How Come the Hourglass? Why Database Applications Slow Down

Additional Articles from SimpleTalk

It is frustrating when you hit 'submit' and you get the hourglass big-time. Is it the database to blame? It could be, but there are other suspects that should be considered. Feodor Georgiev describes where, in the long route, to and fro', that a data request makes before returning, that things can get snarled up. More »

From the SQLServerCentral Blogs - Full-Text Search – Stoplists in SQL Server

Steve Jones from SQLServerCentral.com

Full-text search is an interesting subsystem in SQL Server. It allows you to implement searches through a variety of text... More »

From the SQLServerCentral Blogs - I Need Your Vote!

Tim Mitchell from SQLServerCentral.com

Do your civic duty, and vote today!  Voting is now open for SQLBits, to be held in Nottingham, UK this... More »

From the SQLServerCentral Blogs - SQLBits Public Sessions

Grant Fritchey from SQLServerCentral.com

If you’re over in the UK, or close to it, you’ve probably heard of SQL Bits even if you haven’t... More »

I wrote a long time ago about the value of the data on a laptop being worth more than the hardware. That's certainly true for me, and I very much worry more about losing the data on my devices than the any of the devices themselves. I use sync services to keep a backup of most things, but I still worry about losing any of my bits. 

There was a large study competed recently, called The Billion Dollar Lost Laptop Program, which examined 329 organizations. The idea was to find out the economic costs of lost laptops related to various public and private entities. The conclusion? The average value of a lost laptop, just one laptop, is $49,000. The conclusions say that least expensive part of losing a laptop of replacing the hardware.

As we would expect, most losses occurred away from the office, however it's not known how many of these losses might be targeted thefts. Many of the losses did occur through theft, which is disturbing when most of these disks did not have encryption in place. This was true even when confidential data was contained on the laptop.  It is nice to see that companies that realize they were targets of theft, as opposed to losses, typically do use encryption.

As the study shows, there are a lot of costs that go into replacing a laptop. Many people don't think of all of these costs, and even if the costs are double what they should be, these are still substantial costs for companies to absorb. The costs will only go up in the future, especially as more and more people move from desktop workstations to laptops.

The one positive note? Encrypting the laptop almost cuts the loss in half. A good reason to require encryption on all laptops.

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 - 21.0MB WMV
• Watch the iPod Video Podcast - 19.5MB MP4
• Listen to the MP3 Audio Podcast - 3.9MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

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 submit an article, rant or editorial, log in to the Contribution Center

Today's Question:

I execute the following T-SQL statement (Statement #1)

USE AdventureWorks2012

CREATE TABLE #r([Rank] INT,TotCnt INT)

I then execute the following T-SQL statement (Statement #2)

INSERT INTO #r 

SELECT RANK() OVER(ORDER BY TotCnt DESC),TotCnt

FROM (SELECT SalesOrderID, Count(*) AS TotCnt

FROM SALES.SalesOrderDetail GROUP BY SalesOrderID) AS Cust

I then execute the following T-SQL statement (Statement #3)

SELECT TOP(10) [Rank],TotCnt FROM #r ORDER BY [Rank]

I then execute the following T-SQL statement (Statement #4)

DROP TABLE #r

I then execute the following T-SQL statement (Statement #5)

CREATE TABLE #r([Rank] INT,TotCnt INT)

I then execute the following T-SQL statement (Statement #6)

INSERT INTO #r 
 SELECT DENSE_RANK() OVER(ORDER BY TotCnt DESC),TotCnt
  FROM (SELECT SalesOrderID, Count(*) AS TotCnt
        FROM SALES.SalesOrderDetail GROUP BY SalesOrderID
        ) AS Cust

I then execute the following T-SQL statement (Statement #7)

SELECT TOP(10) [Rank],TotCnt FROM #r ORDER BY [Rank]

DROP TABLE #r

The question is:  What select (Rank or Dense_Rank) returned the following:

Rank   TotCnt

1      72

1      72

3      71

3      71

5      68

6      67

6      67

8      66

8      66

8      66

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: Ranking function. 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 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today

List all the differences between a Primary Key and a Unique Key? (Choose 4)

By Chirag Patel

Answer:

• by default a Primary key creates a Clustered Index
• by default a Unique key creates a Non Clustered Index
• They are not the same, but they both enforce uniqueness of the column on which they are defined
• The Primary key does not allow NULL values as an enforcement on column

Explanation: See the references for the question.

http://msdn.microsoft.com/en-us/library/ms191236%28v=SQL.105%29.aspx
http://msdn.microsoft.com/en-us/library/ms191166(v=sql.105).aspx

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

The Ultimate Missing Index Finder

Jesse Roberge from SQLServerCentral.com

Reports stats on what the query optimizer records in the DMVs as missing indexes and what it says the cost savings will be if they were present. Can limit by table / schema name patterns. More »

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

SQL Server 2005 : Administering

Create Tables on Remote Server ? - Hi, what I would like to do is run a job on Server_A that will drop and repopulate tables on...

Creating Trigger on View - Dear, I have a view that is comprised of multiple tables containing only today's data. I used only select statement in...

SQL Server 2005 : SQL Server 2005 General Discussion

Dynamic Where Clause for Multiple paramaters with AND Operator - Hi I am Trying to Build Dynamic Where Clause for Multiple Parameters and they could be Null Also .Please find...

SQL Server 2005 : SQL Server Express

SQL Express 2005 stoppped running on win xp sp3 - Hi, we have ms sql express installed on a computer which we use as server and it is on LAN. Before...

SQL Server 2005 : SQL Server 2005 Integration Services

Weird SSIS recordset issue - Hi all, I have a weird SSIS recordset issue. In my first data flow, I am summing and outputing some data...

SQL Server 2005 : SQL Server Newbies

Barcodes Code128 generator function - I'm looking for a [b]barcode generating Function[/b] (in SQL 2005), that uses the standard [u]Code128[/u]. The result of calling the...

SQL Server 7,2000 : SQL Server Newbies

Analyst seeking wisdoms while learning the platform - Im a business/financial/data analyst who wishes to make a transition to a BI solutions development/BI information delivery role specializing in...

SQL Server 2008 : SQL Server 2008 - General

NULL VALUES - My client "My 1st" has a sql database with many, many null values in each table. Its because they do...

I NEED HELP WITH SQL EXPRESS 2008 R2 - I am a newbie just been doing this for about 3 mo's. I installed sql and all went well. During setup...

Column Name Case Sentive - Hi, im Matias from Argentina, i need to know if exist a way to change the collation of the TABLE column...

Sequential YYYYMM calc problem - First the code... [code="sql"] DECLARE @Current INT DECLARE @Prev1 INT DECLARE @Prev2 INT DECLARE @Prev3 INT DECLARE @Prev4 INT DECLARE @Prev5 INT DECLARE @Prev6 INT DECLARE @Prev7 INT DECLARE @Prev8...

DB Block Size - What is the recommended block size of operating system on dss systems? Also what is the recommended block size of oltp...

Event ID for drop db user event - Hi all, is there an Event ID that gets written into Windows log when the db user is deleted? The only...

Descending Index - Hi I want to create an index for a table(340G). The data is required in descending order,and I have read that...

Designing Complex Query - Dear, I have a table Student(Section, Roll, Subjects, Marks). Now I want to display the results like the following. That means...

Index rebuild fragmentation criteria confusion - There is tons of info regarding selective index rebuild/reorganize (with magic numbers 10-30%) depending on output of DMV sys.dm_db_index_physical_stats. But...

SQL Server 2008 : T-SQL (SS2K8)

Inline transaction not committing properly - Hello I'm getting the following error when trying to run the attached script Msg 207, Level 16, State 1, Line 76 Invalid column...

Can someone help me with the following TSQL search string extract? - I would like to extract Tow (Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat|) information from the string below: 8Am...

t-sql 2008 r2 pass paramters - In need to pass 5 parameters to a stored procedure in sql server 2008 r2 standard edition database. The 5...

Need help in putting the correct WHERE Clause - I'm Using the below script to get the results set accordingly. [code="sql"]SELECT Min(wq.WorkQueueStartWorkDate) BatchStartTime, max(wq.WorkQueueEndWorkDate) BatchEndTime, convert(varchar(5),DateDiff(s, min(WQ.WorkQueueStartWorkDate),Max(WQ.WorkQueueEndWorkDate))/3600)+ ' Hrs' +' : ' + convert(varchar(5),DateDiff(s,...

Puzzle: vertex covers in SQL - I have to do a book chapter on graph database. I already did “The Kevin Bacon” problem in SQL for...

Compare one row with another in a Table ! - Hi , House_Acc Accountid repcode 123 1 J978A 123 2 J978A 123 3 J978A 123 4 EG567 456 21 BR5TG 456 22 BR5TG 678 66 ZHR06 678 45 ZHR06 678 34 NH678 How...

Using Results from one column to calculate another column - Doing this in SQL 2008. Is it possible to use the results of one column to calculate another column? Example: Select .. VERY...

t-sql parse out data - My goal is to parse out a company name and contact name that is the actual filename of the excel...

SQL Server 2008 : SQL Server Newbies

SSIS Division - How do I evenly divide $10752.00 into 7 In SSIS ? (even division of a decimal) For example:$102.10 / 2 Result:- 51.05 51.05

Error handling in while loop...skip if failure - Hello, I have the following stored procedure that runs as a job: [code="sql"] CREATE PROCEDURE [dbo].[proc_Master_Schedule] AS set XACT_ABORT ON DECLARE @Count INT DECLARE @LoopCount...

Find Fields that are True - Hi, Folks! Need help. I have a simple table with 29 fields(unique id, 14 True/False, 14 text) I want to find all...

Taking a vertical list and transposing it to horizontal and then grouping - Hello, Is this possible? I have several columns, such as: Coulmn A red green yellow black Column B shirt jumper blouse coat Column C for women for men for girls extra large I have permutated the columns in...

Create Table and Bulk Insert - Hi guys, I have a .csv file wich has the following information (exactly like this): List Name: User Format Version:1.2.5.0 Date:12/11/2012 12:00:34...

Translate rows in columns - [font="Courier New"] Hello, I have the following TitleDetailsRequest table: see TitleDetailsRequest.jpg attachment. I need to get an output of the EN_No and FR_No...

SQL Server 2008 : SQL Server 2008 Administration

Database Mail e-mail notifications not being sent on completion of backup jobs - Hello -- I configured our backup jobs to send notifications to the administrators on their respective completion. However, the notifications have...

(Restoring...) question - Hi everyone. Could anyone help with this one. A database has incorrectly been restored with (RESTORE WITH NORECOVERY) configured, therefore database is...

Upgrade SQL server 05 to 08R2 - [b]Hi All, I have upgrade the sql server 2005 to 2008R2 successfully but still it showing the Management studio in...

Performance issue due to high memory usage - All, We have some performance issue..SQL server is running very slow due to high memory usage..this is a 2 node active/passive...

Programming : General

Theoretical: Checking variable positions in a date-ordered queue - I have a theoretical question that sprang from, of all things, looking at my public library holds this morning. :cool:...

SQLServerCentral.com : Anything that is NOT about SQL!

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

SQL Report with multiple matrix - I have a matrix reporting data by week by location. The matrix works great for this. The challenge, however, is...

Timeout expired in reporting services log - We are getting the Time out expired error message in reporting service log file while executing the one report which...

Workaround - LookupFunction in 2008 - Hi geniuses! I have to edit some reports in SSRS 2008 and figure that I'm going to need to use data...

Reporting Services : Reporting Services 2005 Administration

access to report server url - Using SQL SERVER 2008r2 Reporting Services. I accessed my report server url using my admin credentials. I used New Role...

Data Warehousing : Analysis Services

Dynamic Security for dimensional members? - Ok so here's the problem. I have a corporate BFC (big fat cube) which some 20 different services access and within...