|
|||||||||||||||||
In this issue |
|||||||||||||||||
|
|||||||||||||||||
In This Issue |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
Editorial - Statistical Protection |
|||||||||||||||||
The things people can do with data is amazing. I remember reading about the anonymous data set released by Netflix and how some of the people were identified based on other, related actions on the Internet. This de-anonymization, while scary, was amazing to me. There have been other, related reports of similar "attacks" taken against other data sets. These reports worry me that we will have more and more data security issues in the future, not less. I ran across an article that talked about protecting data in statistical databases. These are the databases that contain data from multiple sources, and are used to analyze the information from these sources. The security of these databases becomes important when the data contains information about individuals that we consider sensitive. Interestingly enough, it seems that the security protections being used are query restrictions. However these restrictions are the reverse of what we might expect. There might be minimum restrictions on the number of rows returned, to try and prevent information about a specific individual from being returned. There are also limitations on the types of queries that can be run, usually requiring aggregate functions in the query, and restricting which aggregates are allowed. This is definitely an area of our industry that needs more work and research. Lots of organizations, especially government organizations are being called on to open their databases up to the public, and many of them are doing so right now, allowing queries of their statistical databases. This might improve the use of this information by the public, but there are plenty of ways in which this data could be potentially misused. If your companies wants to open some of your data to clients or customers, you might raise the concerns with possible abuses of the database and ask that time and effort be included to try and secure the data, possibly by implementing query restrictions. Steve Jones The Voice of the DBA PodcastsWe publish three versions of the podcast each day for you to enjoy.
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: 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: » 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 |
|||||||||||||||||
|
|
|||||||||||||||||
Question of the Day |
|||||||||||||||||
|
Today's Question: Here are some declarations: set nocount on declare @i int, @n numeric(10,4), @d datetime, @v varchar(12); These declarations are followed by a single select statement in the same batch; the declarations work, but the select statement fails. Which of the following statements could be the failing select statement? (choose 3)
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. |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
Yesterday's Question of the Day |
|||||||||||||||||
|
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:
Answer: Rank Explanation: For Dense_Rank: http://msdn.microsoft.com/en-us/library/ms173825.aspx/a>. If two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks. For Rank: http://msdn.microsoft.com/en-us/library/ms176102.aspx If two or more rows tie for a rank, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
Featured Script |
|||||||||||||||||
AUTOGENERATE INSERT UPDATE SELECT STATEMENTS UPDATEDI have made an update of this script http://www.sqlservercentral.com/scripts/Metadata/64603/. the new version includes can generate 4 stored procedures for every table in a database 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 : Administeringcreate report with date suffix - I need to create a report & run weekly to output to file on server with filename_date×uffix.txt. The original report (which is... Error 26073 - Hello, We are also getting similar error on our SQL server in cluster environment Error 26073 TCP connection closed but a child process... 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... SSIS Problem With Checkpoint Settings - What can be the cause for the following scenario? SSIS package executed by a job fails with the following error "The package... sql auditing triggers - I have to write DDL trigger in sql 2005, using CLR feature. Can anyone provides help in this regard SQL Server 2005 : BackupsBackup Log Incorrect Function Message (nonrecoverable I/O error). - We started receiving this message mid-last week: [b]Msg 3271, Level 16, State 1, Line 4 A nonrecoverable I/O error occurred on file... SQL Server 2005 : DevelopmentQry - In my table i have following result sets sno 101 102 103 105 106 107 i want following structures 101,102,103,105,106,107 SQL Server 2005 : SQL Server 2005 General DiscussionDynamic 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... using sys.dm_exec_sessions , where to find database name/id - im using sys.dm_exec_sessions to show me all current sessions ( instead of using sp_who2). the problem is that sys.dm_exec_sessions doesnt have... Copy tables from one database to another - Anyone know how I can easily copy selected tables from one database to another? (different servers). In Enterprise Manager it was... SQL Server 2005 : SQL Server ExpressSQL 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 Performance TuningMultiple Optional Parameters - I am in the process of creating a stored procedure which will be used for searching database tables. The user can... SQL Server 2005 : SQL Server 2005 Integration ServicesSSIS XML Destination - Hi guys, Using SSIS pacakge i want to get the XML format dataset output from the OLEDB soure data Is it possible?... Why Use SSIS? - Basically the issue is- If i run t-sql srcipts, to pull data from the staging area, remove duplicates, validate , data and... SQL Server 7,2000 : Administrationxp_sendmail not sending @message text - xp_sendmail will send out the email with correct to address, correct subject line but the message text is blank. This... Loading Trace Files To A Table - Hello, We run profiler traces for auditing logins and i am using server side tracing and everything works great. I just want... SQL Server 7,2000 : SQL Server NewbiesAnalyst 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 7,2000 : Performance TuningTransactions Per Second - Hi I'm doing some performance monitoring and was wondering if someone could shed some light on this counter? In particular... SQL Server 7,2000 : T-SQLsp_MSForeachdb then sp_MSforeachtable - Last posting of the week for me. Trying to get this to work and it's giving a syntax error. I'm... BCP error -Unable to open BCP host data-file - SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file NULL I am getitng the above error when i... SQL Server 2008 : SQL Server 2008 - GeneralExcel Database Tasks - Hi, just thought I would post an update on EDT - Excel Database Tasks. After so many hours of development Version 2.3 now... Unidirectional Merge Replication Pub to Sub - I have a 2008 R2 Standard table that I want to merge into a master copy on another server. The... Index usage and RID lookup. - Hello All, An index with three fields, A,B,C ( A selection with SELECT * FROM A_TABLE WHERE A = 6 AND C =7 performance question: [b]Does this... sql server 2008 r2 trial problem - I have some problem, last admin was installed 2008 r2 enterprise trial. Today SQL has been stopped, because trial is... Restoring database from DPM snapshot - Hi there, We are currently reviewing our backup routines/recovery plans as we found that we were making a number of duplicate... different reales of sql server 2008 - I want to know the actual difference between sql 2008 R2 and Sql server 2008 .which one is preferable to use. also... Generate Months from unique records with different date ranges - Hello I have a database that records contracts and their amounts (each contract has a start and end date); each contract... configuring Loghsipping in 2k8 - Hi, If anyone knows configuring the logshipping using scripts instead of GUI in slq server 2008.. Provide scripts.. Phd Suggestions - Hi, Planning to go ahead with Phd in Database Admin, Kindly provide some suggestions on it... More efficient topic for research... 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... 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... Index fregment doesn't come to 0 - Hi, I have just shifted my database of sql 2000 to sql 2008 64 bit after running Upgrade adviser. then i run... Need help using the most efficient aggregation method in SQL Server 2008 R2 to sum up child rows in same table - I need to find a way to do an INSERT into the same table (named ResultsHistory) by summing up the... Function taking long time - Hi, I have given a function which takes about 3 minutes to executes, can u plz suggest how to improve... Table Variable Parameters - Odd design choices or am I just not finding the answer - So, I'm currently looking over some 2k5 to 2k8 upgrades we're doing (yeah, I know) and I'm reviewing using table... Shrink database - I received this from a vendor as part of recommended maintenance for sql 2008R2 databases. I always thought it was... I need help with my query. I found part of this, but doesn't work. Please help. - I need to get the emails from the n.Note field, which is a nVarchar(max) column. [b] I have the following, but I... 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... Google Analytics SQL Import - Hi All I was just wondering if anyone has ever done a Google Analytics import to SQL via SSIS? A quick google... Unable to shrink transaction log - could not locate files - Hello folks, OK so I have done as much digging as I can on the net and haven't found a solution... Database Diagram printing problems - Greetings all; I have seen this issue/problem for a number of releases now and I have not been able to... SQL Server 2008 : T-SQL (SS2K8)Tally Calendars and 'Week 1' - Hey guys, I know there's been a few discussions on here about tally calendars and their use, I've got one on... help me remove key lookups from the query below - Hi, I have a stupid written query. I saw that 99% of the cost is going into key look ups... i... top 10 product in each month each year - hi i have two tables table A main_table id fill_date 1 09/04/2003 2 12/31/2005 3 01/05/1985 product id name 1 oxygen 2 detox 3 carbo what i want is top 10 product... Measure Transaction size - All, I have two queries like below. declare cursor c1... open c1 fetch c1 into ... while @@sql_status .. begin BEGIN TRAN insert t1 insert t2 insert t3 update t4 COMMIT fetch c1... 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... 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... return zero for months where no data exsits - Hi, I have a table (Users) with rows containing data about registered user, Table Columns: UserId, RegisteredDate I want to know how many... Connecting to multiple sql servers - Hi, I'd like to connect to multiple databases on different servers all from the same query. So, is it possible to... Dynamic SQL - which would you use? - When I need to execute code depending upon the values of parameters I often implement one of 2 methods: In this... SQL Server 2008 : Working with OracleSQL transactional replication to Oracle 11g server taking slower than normal - Hi, I have a push transactional replication set up that pushes to a Oracle 11g db. What used to take... SQL Server 2008 : SQL Server NewbiesSelect records from current month - Hi, I use a very basic select query [code] SELECT * FROM Client_Trades.DBO.Trades WHERE MONTH(Trade_Date) = '01' [/code] To extract records from a particular month. Is there... Decimal separator missed when pasting into excel from sql query - I have an sql query which returns some numeric values that sql server is representing with a decimal point. Since... Help regarding Performance Statistics Script - Hi all, I am a newbie in SQL Server. Today i came across the PerformanceStatisticsScript. I found it in SQLNexus.codeplex.com and... Server build design for BI servers - Hi all, Looking to try and confirm to a standard build for all our servers. We have a lot of... 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... 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... Renameing a column in all tables in a sql 2008 database - I have to rename a column and add columns to all tables ( could be up to 300 tables ) in a... SQL Server 2008 : Security (SS2K8)assigning a special user some special privileges - Hello all, I need some help assigning a special user some special privileges (Role). I need to give the user reading rights... SQL Server 2008 : SQL Server 2008 High Availabilitydifference between SQL Service pack patching in 2005 and 2008 Cluster evn - HI. Just wanted to know if there is any difference betwen Sql patching in 2005 and 2008 Cluster evn SQL Server 2008 : SQL Server 2008 AdministrationBackupExec SQL Agent or SQL Server Native backups???? - I'm looking after a whole bunch of SQL Servers, both 2005 and 2008, some full and some simple recovery databases... adding space to tempdb - In order to avoid any failure please add some space to tempDB database on xyz server. I am a new... Error 26073 - Hello, We are also getting similar error on our SQL server in cluster environment Error 26073 TCP connection closed but a child process... Backup and Transaction Log Maintenance - Good Morning Everyone, I hope everyone is having a nice Monday morning :crying: I came into work today and I noticed... Partition including refernce tables and multiple keys - Hi All, We are planning to partition our table which have many reference tables. Is it possible to have multiple keys... SQL Server 2008 R2 TEMPDB massive autogrowth suddenly. - Today while i was at lunch, TEMPDB had massive autogrows until it filled the disk it sits on. When i... 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... Are these SQLIO results ok? Need to ensure that new SAN IO latency is topnotch - Hi, Just run SQLIO on my new Cluster and got very interesting results that I would like to share and/or get... Programming : GeneralTheoretical: 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:... Big Data - I am hearing this term "big term" very loud these days. Everybody is talking about it. Does any one know... SQLServerCentral.com : Anything that is NOT about SQL!PolyServe replacement options - While it seems HP still has not made the official announcement, some of us have had conversations with HP representatives... 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 ServicesSSRS Background colors (flashing colors every 2 seconds) - Hi, i have a table in SSRS which currently displays customer information. I currently have it alternating colors on a... 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 Administrationaccess to report server url - Using SQL SERVER 2008r2 Reporting Services. I accessed my report server url using my admin credentials. I used New Role... Reporting Services : Reporting Services 2005 DevelopmentSSAS group anything outside top N in SSRS report. - Group, I am working on a series of reports for a sales dashboard. All of the reporting services charts I am... Database Design : Design Ideas and QuestionsUrgent normalisation help please! - :-) Data Warehousing : Integration ServicesHow to get the data in destination database immediately when source table updates? - We have 2 databases Source Database and Destination Database, We want to transfer data from source table records to destination... Need to export excel sheet with SPECIAL formats - Hi, I need to export an Excel sheet with special formats (mandatory) where SQL is the source. I have achieved it... SSIS2005: DataFlow Task from OLE DB Source to Excel File Destination - Hi guys, Once again it looks like Excel is the poor relation in SSIS. I am trying to create a DataFlow task... Data Warehousing : Analysis ServicesProcessing of an Cube kills the service - Hi, Im very new to OLAP, so be nice :-) Version 9.00.1399.06 Windows 2003 We have a cube the for some reseaon refuses to... MDX FLOW - Hi friends, I'm new to mdx . anyone please explain the flow of this or any simple mdx expression.so that i... 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... Data Warehousing : Data Transformation Services (DTS)Getting the name of a file in a location when the file name changes using DTS - To all, Using SQL 2000 DTS. I have an Excel connection named "FileToProcess". I have a global variable named "gvSourceFilePath" and I have... |
|||||||||||||||||
|
|
|||||||||||||||||
|
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. All rights reserved. Contact: webmaster@sqlservercentral.com |
|||||||||||||||||
Using the Script Task in SSIS to Process Data Files When They Arrive
Free Webinar: Statistics in Oracle and SQL Server
Partitioning in SQL Server - Part 2
Day 5 of 31 Days of Disaster Recovery: Dealing With Corruption in a Nonclustered Index
