|
|||||||||||||||||
In this issue |
|||||||||||||||||
|
|||||||||||||||||
In This Issue |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
Editorial - Privacy |
|||||||||||||||||
If you gamble online, you might be gambling in more than one way. It's not surprising to me, but many online gambling businesses are located in places where the legal protections are more relaxed than in other places. However it's not just the laws around the games themselves, but also the laws around data protections. Whether or not you have any sympathy here, or you think that companies engaged this this activity are worse than others, consider the fact that more and more companies in general are ignoring, flaunting, or just failing to keep up with data protection laws in other countries. What's even worse, in my opinion, is that many of our laws are ill suited to dealing with the digital world and often don't provide any protections for data that is more accessible, and greater in scale, than that which has ever been available. I have rarely known about data law changes, and have found myself ignorant of the laws in other countries at times. Not because I am looking to avoid any compliance, but because I'm too busy to keep up. I suspect many data professionals are in the same situation. We are unaware of problems in our data handling until there's a complaint or someone notices. Privacy is important to many people in the world, despite the fact that all too often people aren't sure how they would define the rules of privacy for their data. It's a complex subject, and I understand the problems of passing or updating laws when various companies, advocacy groups, and even friends of lawmakers have opinions. I don't know how things may evolve over time, but I do suspect that privacy and data handling will become more of a part of data professionals' jobs in the future. » Join the debate, and respond to today's editorial on the forums 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: » To submit an article, rant or editorial, log in to the Contribution Center |
|||||||||||||||||
|
|
|||||||||||||||||
Question of the Day |
|||||||||||||||||
|
Today's Question: I create the following 2 tables (#A and #B) and insert the data as shown into the respective tables.
CREATE TABLE #A(S VARCHAR(10),R VARCHAR(10))
INSERT INTO #A
VALUES ('Alpha','D1234')
, ('beta','A1122')
, ('charlie','D1234')
, ('bravo','C1342')
, ('Doug','B1964')
, ('harry','A1122')
CREATE TABLE #B(R VARCHAR(10),S INT)
INSERT INTO #B
VALUES ('D1234',1001)
, ('A1122',4001)
, ('D1234',2001)
, ('C1342',5001)
, ('A1122',3001);
Next I execute the following T-SQL
SELECT tblA.S, tblA.R, subQuery.S
FROM #A tblA
CROSS APPLY (SELECT R, S
FROM #B tblB
WHERE tblA.R = tblB.R
) subQuery;
The question is: how many rows are returned when I execute the above SELECT statement? Think you know the answer? Click here, and find out if you are right. This question is worth 2 points 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 |
|||||||||||||||||
|
For SQL Server 2008 or later, given the following table:
CREATE TABLE dbo.Table1 (
RowID INTEGER IDENTITY PRIMARY KEY CLUSTERED,
DateTimeStamp DATETIME DEFAULT GETDATE(),
Col1 INTEGER SPARSE,
Col2 INTEGER SPARSE,
Col3 INTEGER SPARSE,
TblColumnSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);
What will be the result of the following statements?
INSERT INTO dbo.Table1 (Col1) VALUES (1), (2);
INSERT INTO dbo.Table1 (Col2) VALUES (3), (4);
INSERT INTO dbo.Table1 (Col3) VALUES (5), (6);
INSERT INTO dbo.Table1 (TblColumnSet) VALUES ('<Col1>1</Col1><Col2>2</Col2><Col3>3</Col3>');
SELECT RowID, DateTimeStamp, Col1, Col2, Col3, TblColumnSet
FROM dbo.Table1;
Answer: 7 rows inserted and returned. Explanation: The first three INSERT statements each insert two rows into the table, with the values for each statement being inserted into one column. The fourth INSERT statement inserts one row. An insert into the column set will insert the corresponding values into the specified columns in the XML. You can issue INSERT and UPDATE statements against a sparse column set to modify the column values represented in the column set. All sparse columns will be updated - not just the ones specified in the column set. If there are sparse columns that are not listed in the statement, those columns will be set to NULL.
References:
Using Column Sets: http://msdn.microsoft.com/en-us/library/cc280521.aspx |
|||||||||||||||||
|
|
|||||||||||||||||
Featured Script |
|||||||||||||||||
Generate C# class code for tableFor any supplied table, my proc, usp_TableToClass, generates class code in C#, including fields, properties, getters and setters. 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 : AdministeringTemp db log file is growing . - tempdblog file is growing to 5 gb. I dont have more space on the drive. want a immediate solution . without restarting... xp_delete_file not deleting files on share drive - Hello, xp_delete_file not deleting files on share drive. is there any permission to be given? SSIS FTP writes big temp files to profile folder on C - My post below was not getting any bites in the SSIS forum so I'll try it here. Thanks for any... sp_who2 - sp_lock - Hello, i read this so many times but keep getting confused between blocking and locking..now when i run sp_who2 i... SQL Server is terminating in response to a 'stop' request from Service Control Manager - Hello, I've got one serous problem that i cant check what is happening, at the same hour of yesterday and today... SQL Server 2005 : Backupsbackup file size - Can anybody help in this matter Original database size [b]100 MB[/b] Backup files created, in scheduled time but size was [b]357... tran log backup confused 2.5gb log file but 70gb log backup size - Ok I have a question I have a 116gb DB the full backup ends up around 70.5GB in size I... SQL Server 2005 : Business IntelligenceSorting in DMX Query... - Hi All, I am using order function in a MDX query to get the member values of a dimension in... Cube processing - Hi All, i have 10 cubes in my solution, individual cube sizes are 21GB, 50GB, 19GB so on.. when am trying to... Pulling node value using SSIS XML Task - I have a node ID that I need to pull (DataFile id="1") and place into a variable: <Metadata> <BatchProcessing> <OpenJob> <FileJournaling> <DataFile id="1"/> </FileJournaling> <Status> <StatusCd>0</StatusCd> <StatusType>"Ok "</StatusType> <StatusDescription>"Ok"</StatusDescription> </Status> </OpenJob BI Jobs? - Hi people! I'm asking directly to people who works in Business Intelligence! Definition of BI? Do performancepoint, reporting services, excel services... Anything out there better than SSRS - Hi All Beginning to look around for other BI tools. Don't have a vast amount of knowledge in this area, but... SQL Server 2005 : SQL Server 2005 General Discussionsql server table valued function parameter issue - Hi all, I got following table valued function (SQL Server 2005). I got an compile error when I run --1, but... How to delete the oldest file in a given path. Assume some .txt file of the format File_MMDDYYYY.txt - Can someone please help me....i have tried using for each loop, script task and file system task this is the script... How To Setup Server Side Only Alias (No Client Config Needed) For Named Instance Only SQL Installs - How (if possible) can I setup a Server Side alias for a named instance on a server that has SQL... idle configuration for 1000-1200 concurent users - Hi, i want to set up a database server in my company for 1000-1200 concurrent users. so i want to know that... SQL Server Tools - Recomendations On Stress Testing and I/O Testing On New SQL Server - Need to test/stress a new SQL Server box to see what she can do. I went to get a copy... ObjectType Reference from Default Trace - Hi there! I'm querying the default trace to get some audit info on some DB objects. (The variable @Trace contains the... SQL Server 2005 : SQL Server 2005 Integration ServicesSave mail files(.msg) from Mail Server using SSIS 2005 - Hi Guys, I am working on SSIS packge where I want to save mail files(.msg) coming from Mail Server along... Save Outlook mail file (.msg) using SSIS 2005 - Hi Guys, I am working on SSIS packge where I want to store Outlook mail file(.msg) along with attachment using... SSIS FTP writes big temp files to profile folder on C... - Hello, I have a complex SSIS package from a vendor. It includes an FTP task which moves files that total about... SQL Server 2005 : T-SQL (SS2K5)SP_Oacreate and SP_OAMethod Invocation limit - Does anyone know if there is a limit on the number of objects or methods that can be created/called with... Performance issue with tally solution - Hello everybody! I have a performance issue with a set based solution. Please could you advice me what I'm doing wrong? I... Conversion failed when converting datetime from character string. - I have written the following query to extract data splitting a text string into separate fields. This query works fine... SQL Server 7,2000 : Working with OracleError converting data type DBTYPE_DBTIMESTAMP to datetime - Good afternoon: There is a process that somestimes fails with the error: Error converting data type DBTYPE_DBTIMESTAMP to datetime This error usually happnes... SQL Server 7,2000 : SQL Server NewbiesPlease Give Me the Query For Below Mentioned Resultant Tables. - Hi All, I have two tables for example, tblCategories(CategoryID[INT],CategoryName[VARCHAR]) tblEmployees(EmployeeID[INT],EmployeeName[VARCHAR],CategoryIDs[VARCHAR]) Example OUTPUT Like, tblCategories: CategoryID CategoryName 1 AAA 2 BBB 3 CCC tblEmployees: EmployeeID EmployeeName CategoryIDs 1 SQL Server 7,2000 : T-SQLHow to send email from SQL server 2000 - Hi, I want to send email through store procedure in SQL SERVER 2000. Please help me. Increment in sql server. - Hi all i need to do increment a number like aa001 to aa999 then again ab001 to ab999 aa001a0 to aa999a0... SQL Server 2008 : SQL Server 2008 - GeneralReplication - What are the issues with using replication to move data between a live database and one used for reporting? is this... PRINT Vs SELECT & RAISERROR() mystery - Hello, While getting a handle on how to trap errors in stored procedures and how this works when the procedure is... Learning SQL from Scratch - Hi, I have been programming in VBA for years and VB now for just over a year and I would like... preparing for 70-432 exam - Hi All, If any one of you has any dumps or any test papers for the 70-432 exam, Please forward so... Rollback Script - Hi I have a simple stored procedure having a select statement. Recently I added a NOLOCK to the table name in the... SQL 2012 Mirgration - Just read this on MS website. • The following scenarios are not supported in SQL Server 2012: You cannot migrate from a stand-alone... create YTD in a Table - I want to obtain an YTD value for every year and on the same row I have to put the... Unable to access the Linked Server - [font="Tahoma"]Hi Friends, I have been trying to create a Linked Server from my machine for the last few days and I... Unable to access the Linked Server - [font="Tahoma"]Hi Friends, I have been trying to create a Linked Server from my machine for the last few days and I... Replication Error due to Trigger - Situation: I have setup transactional replication between 2 computers. In the publisher, there is a INSTEAD OF DELETE trigger to prevent deletion... DBCC SHRINKFILE EMPTYFILE - Hello, I have a 1TB datafile that I need to move onto several files to lessen it's size. Has anyone... Equals space between two string when concatenate two columns - Table Script CREATE TABLE [dbo].[currency]( [currency_code] [varchar](3) NOT NULL, [country] [varchar](256) NOT NULL, [currency_name] [varchar](256) NULL, [active] [int] NULL, CONSTRAINT [PK_currency] PRIMARY KEY CLUSTERED ( [currency_code]... Restore Faills - Hello i need your best suggestion. Here is scenario. I have a Production server and we backup that server and restore to DAYB... How to replace the strings? - Hi Friends, I have a column called Description which contains values like "Qty: 1 | Desc: 1991 LOADRITE BOAT TRAILER | Value: .00 | Recovered... Running Values ( Cummulative) - Hi All, [code="sql"] Date Item_Name Value Cummulative_Value 2012-01-01 Actual 5 5 2012-02-01 Actual 5 10 2012-03-01 Actual 10 20 2012-04-01 Actual 5 25 2012-05-01 Actual Using GUIDs as primary keys/clustered indexes - Hi All, One of my production databases has 120 tables out of which 104 tables are using GUIDs as clustered indexes.... Downgrade from 2008R2 to 2008 - Hi All Having an issue when downgrading from 2008R2 to 2008. Using the copy database wizard to perform the copy works well... DBCC CHECKDB performance - I have a server and two instance SQL SERVER 2008 R2 SP2 and SQL SERVER 2012 and firstly i try DBCC... SQL query to get all predecessors and successors for given node. - Hi All, I have table which holds ID and predecessors ID. One ID can have multiple predecessors. In such scenario I... SQL Server Consolidation and Collation Settings - Hi, I have just been told to assist in a consolidation exercise where a number of our databases from different servers... Backing Up/Restore to non-local drive - I've told my IT Admin many times that we don't/can't do SQL DB backups to network shares or anything but... Feedback On Proposed Drive/RAID Configuration For New SQL 2008R2 System - I am no hardware expert and so when it comes to things like RAID configurations and Channels I'm not versed... SQL Server 2008 : T-SQL (SS2K8)Finding a table with date appended to it and check for the range of that date - Hi All, I need to create a process that looks for a table in database with date(YYYYMM) appeneded to it where... SQL help - I need some assistance in finding records from a table where a certain ID exists within close proximity to another... rCTE vs LIKE for Hierarchy - Hi people, I'm tunning a database and i've stumbled by some hierarchy queries, on the good old form of ID,... string manipulation - Need help ! - Hi All, Can anyone help me with below sql server 2005 and up: input string: BUILTIN\ADMINISTRATORS: [System Admin]YES;[Security Admin];[Server Admin];[setup Admin];[Process Admin];[Disk Admin]YES;[Database... Time as a high precision difference of dates - Hi all, I am trying to get a high precision difference of dates in SQL Server 2008 R2, and can't seem... Generation of Records - I have got some Monthly Data which I want to pro-rata it on a Daily Basis. E.g. The Current Data looks like: [code="other"]YearMonth Value -------- ------ 201207 5000 201208 4000[/code] I... Potentially delusional co-worker, inline vs. multi statement TVF - Greetings -- I am having a disagreement with a co-worker about what constitutes a multi-statement Table Valued Function. A sample call... Comparision of a datetime variable against a column of the type nvarchar - Hello All, (Sorry, if I have posted this question already in another topic) I have a table like this: [quote]MinionRpt ( Id [int],... What would be right approach? - I have a table which contains data for 12 months. Now i want to delete the data for the month... Fiscal Dates - My company wants to create a Financial Calendar Table which contains only the Fiscal Dates. The requirements is ti populate... using IN statment & a string variable. - Hi, I need some assistance and i've read multiple answers which are very similar but just not sure how to tie... ORDER BY PROBLEM - Hi, I have column in a table which needs to be ordered in an ascending order. [code="sql"]DECLARE @NumberTable TABLE( ItemNumber VARCHAR(50)) INSERT @NumberTable SELECT 'ABC-1702-XYZ' ItemNumber... Cannot get Left JOIN to work correctly - I have 2 tables in my database and am RIGHT JOINING the tables and it is acting like an inner... SQL Server 2008 : SQL Server NewbiesTrying to get data into one view - Hello, I have already posted this before, but gave too less info. I am trying to get a total of unique usernames... Using Access to put data into SQL Server - Hello everyone. So I have a database in SQL Server 2008 that contains 5 tables. I have connected Microsoft Access... SQL Server 2008 : Security (SS2K8)Roles/Permissions for 3rd Party Providers doing Rollouts/updates - Hi, I have just started at a new company and I am tightening security at the moment. One of our 3rd party... SQL Server 2008 : SQL Server 2008 High Availabilitylog shipping tables query - i have two test log shipping servers set up. both going to different secondaries but using the same backup location... same role name for mirroring setup? - Hi, 1. Principal & mirror server result the same role, Is it correct for FULL safty configuration without wittness? [code="sql"]SELECT role FROM sys.database_mirroring_endpoints;[/code] Role... Failover Clustering - I just added a third node to the existing 2 nodes SQL 2008 enterprise cluster. I attempted to failover to... Standby-file location in log-shipping - Is there a query I can use to find the location (path) of the standby file in a log-shipping configuration? SQL Server 2008 : SQL Server 2008 AdministrationNeed SQL server 2008 R2 for the below Database servers with Windows Server 2008 R2 as OS !!!!!!!!!!!!! - We need SQL server 2008 R2 for the below Database servers with Windows Server 2008 R2 as OS for 2... SQL Agent memory issue - Hi I have a bunch of(6x) 64 bit sql 2008 (not R2) standard edition servers that seem to be leaking memory the... Need help for SQL Server Standard edition 2008R2 ???????? - I am desperately looking for one help.Could you please look below doubt and confirm the same? Does SQL Server Standard... Latest service pack - how to be alerted - Having been a DBA for quite some time I am aware of the need to obtain and install the latest... E-Mail notification after a scheduled task completes - Hello -- I have created a script within a SQLExpress 2008 installation that will do full backups, with overwrites, of three... x-cmdShell access - Dears all how can i restrics xp_CmdShell accesss to run some command? for example xp-cmdshell can not run format syntax or delete... SQL Server Agent service did not start due to a logon failure - I have a SQL Server MAchine with 3 instances. I can't get the SQL Server Agent Service to Start. The account was... Career : Certification70-457 70-458 70-460 - Hi guys, I am planning to pass these three exams in the next couples of months. (Self-funded .... :( :( ) Any suggestion would be... Programming : PowershellPowershell for network path - I am totally new to powershell(just 2 days) so please bear my ignorance.Here is the code I am trying to... SQLServerCentral.com : Anything that is NOT about SQL!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... Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,... 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... Reporting Services : Reporting ServicesDatabar with single value - This is such a newbie question, but I haven't been able to figure it out. I have a databar that... Hiding column group when exporting from SSRS 2008 R2 to Excel - I'm working with a tablix in SSRS 2008 R2. I had to add a parent group with the code "=Int((RowNumber(Nothing)-1)/65000)"... Forward Dependencies are not valid. - I am working on reports from our HR system that allow a manager to only see their own direct reports... Database Design : Design Ideas and QuestionsDatabase Design - Using a single common table for all 'Entities' - Hi I am after a bit of advice.... I am considering the design for some tables in my database and wondered... Data Warehousing : Integration Servicesquery AD in SSIS - I would like to use SSIS get data from AD to a SQL table. I setup the ado.net conneciton manager and... SSIS - datetime manipulation - Hi you all. I have this task that I'm working on right now, which is to load flat file into an... Length of field imported from CSV file cut off at 255 characters - Hello, I am using SSIS in SQL Server 2008 R2 and am importing a field called "Notes" from a pipe-delimited CSV... Data Warehousing : Analysis ServicesIdentifying Facts and Dimensions.. Urgent Help Required.. - Hi all, I have an OLTP Database and we are currently planning to build a datawarehouse and a cube and then... Fact/Dim design question for cubes - With Accounts cube we quite a few measures setup such as Active, Residential, CreditScore buckets. Any issues with creating FactAccounts table... Missing data from dimensions - I have a DSV with a filter. when I process that particular dimension, not all the rows are read from... Article Discussions by Author : Discuss Content Posted by Brian KnightTransparent Background Color in SSRS Textbox - I get a warning similar to this: [i][rsInvalidColor] The value of the BackgroundColor property for the textbox ‘textbox22’ is “Transparent”, which... |
|||||||||||||||||
|
|
|||||||||||||||||
|
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 ©2012 Red Gate Software Ltd. All rights reserved. Contact: webmaster@sqlservercentral.com |
|||||||||||||||||
How to save results of a procedure with more than one result set
SQL in the City - San Francisco 2012
SQL Saturday #162 - Cambridge, UK
How to Find Outdated Statistics in SQL Server 2008
Reducing Your Storage Footprint Using SQL Storage Compress
The MERGE statement in SQL Server
