|
|||||||||||||||||
In this issue |
|||||||||||||||||
|
|||||||||||||||||
In This Issue |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
Editorial - The Group Account |
|||||||||||||||||
Managing security for groups, allowing access is selective ways to different individuals is hard. However it's also something that many DBAs do on a regular basis. We deal with the challenges of row level security and tying particular pieces of data to particular individuals, groups, or other pieces of data. We work to ensure security and systems are flexible, allowing for disparate requirements to be managed with rights, schemas, and other mechanisms. And many of us do it well. I was reminded of the hard work we do with security when I read this piece on family accounts and the lack of support from Amazon and Apple. There are any number of comments and criticisms of the idea from people that ecosystems are bad ideas, or supporters of this idea have trust issues. Those are valid criticisms of the process, but that doesn't change the fact that as our digital world has grown more closely linked, and many of us do want ways to share some of our digital assets with others, while retaining our privacy. This isn't just a family concern, as many vendors don't manage customer accounts well with all kinds of software. I've encountered many situations where an employee registers software under their email account and subsequently leaves for a new job. Finding licenses or being notified of patches becomes a problem, especially when the new administrator is often searching for information in some sort of disaster situation. I don't have a great solution, but I do know that we want the ability to share assets, in much the same we could lend a book or wrench, or car to someone else in the physical world. At some point our cars, other secure application software, maybe even our databases will be authenticated in more secure ways. The future digital software that doesn't allow for the re-assignment or information or rights to other individuals in an easy and secure manner will fail us. » 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: Your computer shows the following system date and time:
Which of the following functions will return the date and time as shown below? 2013-01-17 11:40:42.8078994 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 |
|||||||||||||||||
ProblemYour boss gives you a table with employee names, and wants you to produce a table with 2 blank rows after each employee name. (This will be used for signatures of employees and spouses/partners at the annual company formal dinner and dancing party.) The report writer is on vacation so you got the task. Your boss wants what is shown below:
You try this:
--
-- The Lonely and Neglected Cartesian Product (Cross Join)
--
-- create and fill employee table with 5 row sample
--
create table #temp1(PK int IDENTITY Primary Key, column1 varchar(20))
insert into #temp1 values ('employee 1')
insert into #temp1 values ('employee 2')
insert into #temp1 values ('employee 3')
insert into #temp1 values ('employee 4')
insert into #temp1 values ('employee 5')
--
-- create and fill 'tally' table with 3 rows
-- because we want 3 rows per employee
--
create table #temp2(PK int IDENTITY Primary Key, column1 varchar(20))
insert into #temp2 values ('')
insert into #temp2 values ('')
insert into #temp2 values ('')
--
select * from #temp1
select * from #temp2
--
-- First query attempt
--
select t1.PK, t1.column1, '' as 'Signature Column'
from #temp1 t1 cross join #temp1 t2 order by t1.PK
--
-- Second query attempt
--
select t1.PK, t1.column1, '' as 'Signature Column'
from #temp1 t1 cross join #temp2 t2 order by t1.PK
--
-- Third query attempt
--
select t2.PK,
case t2.PK
WHEN 1 THEN t1.column1
ELSE t2.column1 END 'Signature Column'
from #temp1 t1
cross join #temp2 t2
order by t1.PK, t1.column1
--
drop table #temp1
drop table #temp2
Which of these works? Answer: Third query Explanation: Cross Joins, also called Cartesian Products, have a bad reputation because most SQL programmers, at some point in their careers, forget a "WHERE" or "ON" clause in a join thereby accidentally creating a cross join, which consists of all the rows of the left table joined to all the rows of the right table. If the left table has n rows, and the right table has m rows, then the cross join has n * m rows, which could bring even a powerful database server to its knees. Imagine a cross join on a billion row table to itself; your DBA will be after you in a flash! The Cartesian product is named after René Descartes, 31 March 1596 – 11 February 1650, a French philosopher, mathematician and writer whose formulation of analytic geometry gave rise to the concept. Hence it much predates any computer. Reference: http://en.wikipedia.org/wiki/Ren%C3%A9_Descartes Yet cross joins can be elegant and useful when carefully controlled and tested. They exhibit the awesome power of SQL as a set based declarative language. Reference: http://en.wikipedia.org/wiki/Cartesian_product The first query attempt is wrong, because it cross joins employees with employees, thus producing the square of the number of employees as its row count (5 * 5 = 25). Also, it repeats employee name in each row. Reference: http://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx The second query attempt is wrong, because like the first, it repeats the employee name in each row. Reference: ibid The third query attempt is correct. It produces 3 rows per employee, with the correct (5 * 3 = 15) Cartesian product row count, with each employee name appearing only once, and sorting is correct: Reference: http://technet.microsoft.com/en-us/library/ms181765(v=sql.110).aspx Now my question to you: Is the company sponsored employee & spouse/partner formal dinner and dance, a benefit enjoyed in a bygone era? |
|||||||||||||||||
Pick up your copy of this great book from MVP Glenn Berry at Amazon today. |
|||||||||||||||||
|
|
|||||||||||||||||
Featured Script |
|||||||||||||||||
Check BackupsChecking Backups on your SQL Server 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 : AdministeringSQL Job getting stuck at one step - Hi There, I have a SQL job with 16 steps but it is getting stuck at step 9. All that step... Migrate database from sql 2005 to Oracle - We have one of the server 2005 database 450 GB, business needs to migrate from sql server to Oracle.. Please any... Ideal table partition size per partition - What should be the ideal table partition size (per partition) in a DB Server having 80 CPUs and 1 TB... Auditing SQL Server 2005 Standard Database on Insert Update and Delete operations - Hi I have doubt about data Auditing in SQL Server 2005 EE. Is SQL Server 2005 has any default data... Database in Recovery mode from last 7 days. - Hello, Please any one can help me, how to recover databases or change status from in recovery to normal. i had... Database in Recovery Pending State - One of my databases is in [b]Recovery pending[/b] state. I tried to run an Alter command on the database to... SQL Server 2005 : Backupsrecent transaction log backup was crashed - We have a Full backup at 1.00am, and differential backup at ever 4 hours, and transaction log backup is at... Log backup and running time - Hi, Bit of an idea to ponder: I have a server with 15 user databases ranging in size, the largest being 120GB.... SQL Server 2005 : Business IntelligenceCube report #error issue - Hi, All My report dataset contains 3 date fields,, date1, date2, date diff datediff field formula is "[b]=cdec(DateDiff(DateInterval.Day,Fields!Date.Value,Fields!Date2.Value))*(-1)[/b]" and the expression for... Approch For picking each value from the table and assign to package variable and use. - Hi all, I have a requirement like, In my table have a data as shown below Table:- ID Tablename --------------------------- 1 Table1 2 Table2 3 Table3 . . . N... ssis space problem - Hi friedns i have small doubt in ssis plese tell me how to solve this issuse i have source table and... How can I store the username and password without config file - How can I store the username and password not in the config file or SQL Server in SSIS, so that... Using calendar date picker in Reporting Services MDX cube report - Hi, to anyone who can help me I would really appreciate it. I have spent several days trying to make... SQL Server 2005 : DevelopmentRows Value into Columns - hi Guys, Need your help to sort out the Query, i have fix columns Field1 to Field10, and Variant Rows... Counting all childs and sub-childs of a node in tree - Hi friends, I have one problem. I have one tree which I have saved in sql server table like [code]ID Node... SQL Server 2005 : SQL Server 2005 General DiscussionExtracting xml data from text column - I'm very new sql server (oracle dba). I was assigned to work with sql server 2005. The user wants a... Excel import truncates at 255 characters - Hi Everyone, I am trying to import some product data from a xls spreadsheet (provided by an external supplier) into SQL... SQL Server 2005 : SS2K5 Replicationreplication design advice - Hello Folks, we are planning to replicate database for reporting purpose and we need to restrict data based on companyID' we are... SQL Server 2005 : SQL Server 2005 Performance TuningPoor Performing SQL Server Queries - Hi Team, Am using below query to get top 20 poor performing queries, finally i got the result set of 20... HOw to get Automatic Email notification for High CPU Utilization >80 % with the query which is causing the high CPU usage - hi all, Do any one have any script by which i can get a automatic email notification when a HIGH CPU... SQL Server 2005 : SQL Server 2005 Integration Servicessave the result of a lookup task to a variable - Guys, I have a dataflow task that creates a header record with a batch ID as an identity column, I have... Exporting 30 query output to Excel spreadsheet - Hi All, I am new to SSIS and am currently working on a requirement as follows: We have 30 sql scripts which... How to make a CASE STATEMENT (Case when ....then...) in SSIS - Here is my problem : I want to do a REAL "Case Statement" in my code (not only a single If). I... SQL Server 2005 : T-SQL (SS2K5)daily weekly monthly calculate sum - hi guys, i need urgent sql query,kindly help me i need sql output like given parameter @startdate and @enddate->01/01/2011 to 01/01/2012 output like state... SQL Server 7,2000 : Performance Tuningselect statement is too slow - Hi, I have 81 columns in my table. and record count=6690237. clustered index is been applied on PK. when i... SQL Server 2008 : SQL Server 2008 - GeneralSQL Server 2008 Management Studio debugger won't......er.......debug - I've used the debugger many many times to debug stored procedures on the same server, but just lately, when I... How do I get Distinct entries from Duplicate records? - Hello Guys; I have a table which contains duplicate entries (almost all) but with a differentiating PK & another 1 column. Below is... Missing Transaction Log Backup Schedule!!! - Hi all, I have a rather perplexing issue regarding a scheduled T/log backup not executing!! I'm using an agent job to... Installing SSAS - Hi Friends, I installed SQL server in my machine and now i want to configure SSAS. Can you tell me... Could the disconnected database be updated? - Colleagues, I am in a bad need for ideas. The situation looks like that. We (my company) have Server A which runs... How to filter DATE data by DATE-1 day and store filtered result in another column..? - [size="3"][font="Courier New"]Hi All, I have below query... Declare @date DATE Set @date = '2013-01-29' Select max(GT.P_CODE) As P, max(GT.FG_DESCRIPTION) As T_SIZE, max(GT.PRESSCAVITY_DESC) As PRESSCAVITY_DESC, max(GT.PLANNED_QUANTITY)... Stored procedure - Hi all, I want to modify the existing stored procedure . What is the process and how can i know the new... identity order is not in order - We have a table in our system that has an identity to one of the field. One would think when you... Need help comparing two execution plans - Hi all, We're testing an upgrade from SQL 2005 to SQL 2008 R2 in our data warehouse environment. I've run into... How much data have you replicated with Transactional Replication? - Hi, I have a 4.7 TB Database and I need to replicate a good deal of that to another server.... Facing issues in extracting 80k rows into excel using SSRS 2008 R2 (looking for fix) - getting an error which says no. of rows cannot exceed 65k in order to extract to excel. Help with sorting this recursion - [code="sql"] DECLARE @DateLevel int = 0, @DateLevelUp int, @FTID int = 7 CREATE TABLE #FieldRels ( FTIDBase int, FTIDCalc int ) INSERT INTO #FieldRels(FTIDBase, FTIDCalc) SELECT 6,... What causes open transactions sys.dm_exec_requests? - What actually opens a transaction? For example, if I look at the currently running queries, several may say running but... Naming Convention for UDF's, Views and SP's - I recently has to take a written SQL test for a client and one of the questions got me stumped... ExecuteNonQuery returns -1 even if rows are successfully inserted - I have am using ExecuteNonQuery to get number of rows inserted But i found that it returns -1 even though... Last Sunday of a month in sql - Hi all, I would like to know how to find last sunday of a month in sql... I wanted basically to... Read Committed Snapshot Isolation - when Read Committed Snapshot Isolation is ON, 1. whether Phantom Reads possible ? 2. whether tempdb needs to managed ? (how?) 3. when a... Replication and Data Migrations - I have a test database and transactional replication set up and running properly. Now that it is up, I have... logical consistency-based I/O error - Hello, when i want to see properties of one data base ,or run maitenancetools or backup sqlserver give me this... Unable to install sql server 2008 Management studio - Dear Sir/Madam, I downloaded Visual Studio 2010 from microsoft so its automatically installed sql server 2008 R2. In Sql server 2008... Convert Non Clustered PKs to Clustered - My database that I have inherited suffers from many of the tables have PKs setup to have but they are... Matching rather unstructured character data against lookup tables - Hello all, I am looking for advice on possible approaches to take to solve a specific problem. The problem is in... SQL Server 2008 : T-SQL (SS2K8)Trying to select XML data for one tag from an text field and isn't returning the data I need - Hello DBA's/Developers, I'm trying to select XML data from one tag that is stored in an TEXT data type and can't... Neew T-SQL for setting date - UPDATE [tblTransactions] SET [AllocatedDate] = ........ Basically I need to set AllocatedDate to "Today" using something like "Now" or "Now()"... any ideas?... problem with join T-Sql - i have customer table and it have 1 foreign key from other table in it then i have service table... Comparing Conditional WHERE clauses - I've been trying to find performance stats on different ways to do conditional WHERE clauses. Take these two for example: [code="SQL"]... Comparing figures based on relative date - Hi All I'm having trouble working out a solution to the following issue: I have a table containing a date and a... Want one values with similar ids - Hi friends i have a question Id Transcation amount 1 20000 1 30000 2 40000 2 20000 2 20000 3 45000 3 30000 3 5000 I want result as 1 20000 2 40000 3... Calculating a Moving Average - Hi, I have a need to calculate a moving average. Ultimately, I need to convert the Select statement into an Update... export table with a geometry column - I tried to export data from one server to another server, they are both 2008. When using export wizard I got... Error converting data type varchar to numeric - I get this error when using this function: [code="sql"]CREATE FUNCTION dbo.[PSBPR_FormatData] (@DataValue DECIMAL(15,4), @DataType NCHAR(1)) RETURNS VARCHAR(20) AS BEGIN DECLARE @FormattedData VARCHAR(20) SELECT @FormattedData = CASE WHEN @DataType... Need a backup script to get backup history status for the latest backup from all linked sql servers - Hi Guys, I am actually in need of a t-sql or a powershell script that will help me get the latest... Get the substring after second white space - I have a requirement here. There is a string '0005 ganeshkumar A999'. I want the last four character 'A999' as... SQL Server 2008 : SQL Server NewbiesTransaction log backup file is too big - Hi (I'm new here, and am in a new job where I don't know the setup and have nobody else to... SSRS reports - 2 different data sources - 1 cube and 1 flat table using same parameter - Is it possible for a flat table to use the same parameter as the cube? I know several flat tables... SSRS matrix issue - i've been wracking my brain trying to find a solution...it's probably right before my eyes. i have a list of... SQL Server 2008 : SQL Server 2008 High AvailabilityCluster Installation Time Out Issue - Hi. I want to create a failover cluster and always get the following error while creating the windows server cluster : Security... SQL Log Shipping out of sync - Comparing Log shipping Primary and Secondary Servers - Hello all, I have a SQL log-shipping High availability setup from a Primary Server to a Secondary Server which ships transactional... SQL Server 2008 : SQL Server 2008 AdministrationLock Pages in Memory - I have Windows 2008 R2 with SQL 2008r2,RAM 16 GB If I am Setting SQL MIN & MAX memory to 13 GB,... Migrate jobs from 2000 to 2008 - Hi All, I need your advice to process migrate the SQL SERVER AGENT JOBS from 2000 to 2008. Please advice... Index fragmentation - Dear Experts I have made a tracing file, then run tuning advisor. The recommendation shows a lot of indexes. I looked... Fullbackup succeeded but no files in disk - Hi , we are taking full backups every date with script .backup job status is successfull but only one or two... Running "DBCC FLUSHPROCINDB" as part of maintenance plan? - Hey guys, I was looking through the database maintenance plan the other day, and it looks our vendor has Flushing... Restore space needed WAY more than bak file size - I have a 14GB uncompressed .bak file that I'm trying to restore to a new 20GB (20,000MB) database that I... Compression backup - Any pros and cons for using compression backup? I know the obvious one that could save up a lot of space... SSIS 2008 Connection Error - Client: Windows 7 Server: Windows Server R2 SQL: SQL Server 2008 Trying to connect to Integration Services via SSMS2008. I'm getting the following... differential backup almost the same size as full - We have a vendor production (surveryor) with SQL server database. The recovery mode is simple, we don't need to do... Execution of SQL Job, the moment a file exist - Hi, I have a scenario.. I have a SQL Job which needs to be executed based on demand, means the moment... Sql Server 2008 R2 takes more memory - one of our new production server launched 3 days before, Windows 2008 and Sql server 2008 R2 with sp2.. there... SQL server performance condition Alert - HI experts Do any one knows a good article explains in detail how to use sql server performance condions ALERTS Thanks lot Transaction Log Backup - Our company has never backed up the transaction log file, and is now about 143 gig, with about 35 gig... Server configuration - Hi, I've a customer that has a weird infrastructure implemented to store his customers databases. He has 4 SQL Servers, with 1... Reporting Services Logs not being deleted - We have a server running SQL 2008 SP1, Standard. We recently discovered that SSRS isn't cleaning up it's log files.... SQLServerCentral.com : Anything that is NOT about SQL!Semi-SQL Related (correlated sub query maybe? ok bad humor) - Hi all :-D So I have been on a self-edification kick lately. There are so many big concepts surrounding business disciplines... 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 Servicesone parameter returning all,remining parameters also need to return ALL - i have 6 parameters ,in this 4th parameter returns "ALL" 5th and 6th also in need to return all 5,6 have... format the number in indian currency format - hi.... to all.. i'm using sql server 2008 express with advance edition with BIDS... i've developed one report in that employee salary..... Total indian currency format - hi... to all i've created an report in that report i've one salary field now i want to display the total amount... Data Warehousing : Integration ServicesUsing value from target table to filter values from source - Hello, I want to import all the rows from a source table where the value of a column (ID) is... Output multiple SQL query result into excel using SSIS - Hi All, I am new to SSIS and am currently working on a requirement as follows: We have 30 sql scripts which... Data Warehousing : Strategies and IdeasHow often do you back-up your data warehouse? - Hi All, I'm trying to find a happy medium to a back-up plan for an up-and-coming data warehouse. The DB won't... Fact/Dimension design suggestions - I'm from Higher Education and I'm working on a warehouse so our recruitment folks can compare application counts of certain... Data Warehousing : Analysis ServicesError attaching MdxStepByStep.mdf for SQL Server 2008 MDX Step by Step. - When I try to attach the MdxStepByStep database I get the following error: One or more files do not match the... |
|||||||||||||||||
|
|
|||||||||||||||||
|
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 |
|||||||||||||||||
Stairway to XML: Level 10 - Converting XML Data
SQL Server 2012: Time Marches On
A Month of PowerShell – Day 10 (Navigating SMO collections)
