@EBrown As a non-DBA person, do you consider setting up and maintaining a shadow copy of a database to be less of a problem than setting up permissions? I'm not a DBA myself and I would most likely go for the latter, but I don't know if there are any peculiar details to your situation, of course.
@AndriyM I mean, I guess it doesn't matter all that much, I just don't know if maintaining permissions for many users is less problematic than maintaining a shadow copy.
I think the usual way is to set up roles, assign the necessary permissions to those roles, then assign individual users to the appropriate roles. You might even not need a custom role and use built-in ones instead.
Rick Byham has a WIKI post showing the fixed server and fixed database roles and how they map. You can look here: http://social.technet.microsoft.com/wiki/contents/articles/database-engine-fixed-server-and-fixed-database-roles.aspx
The chart shows that db_datareader role is identical to GRANT S...
@EBrown Well, it doesn't allow you to change anything, which seems to be your primary concern, but being able to read all data in the database might be 'potentially harmful', depending on what the database stores...?
If this really is a mystery or unknown to not only me then I can easily turn it into a question on site.i just don't want to come out as daft. Don't care what you heapsters think :)
Query:
declare @X xml = '
<item ID = "0"/>
<item ID = "1"/>
<item/>
<item/>';
select I.X.value('@ID', 'int')
from @X.nodes('/item') as I(X);
Result:
-----------
0
1
NULL
NULL
Execution plan:
The top branch shreds the XML to four rows and the bottom branch fetches the value for the a...
@MikaelEriksson A scalar aggregate always produces a single row, even if there is no input. e.g. scalar max of no rows is null; scalar count of no rows is zero. For a vector aggregate, group by (), the result is no row in both cases.
Still not easy though. That Connect item came from a query Adam Machanic passed on to me. He didn't spot the scalar aggregate, and neither did I at first.
Haha, this question I had come as a result of trying to figure out why his XML query was so slow.
Funny thing is that the standard answer of not using the parent axis was wrong in this case. Using the parent axis was the thing to do only do it with a twist.
with xmlnamespaces (default schemas.microsoft.com/sqlserver/2004/07/showplan …')
select C.X.value('../../@LogicalOp[1]', 'varchar(20)'),
C.X.value('@Column', 'sysname')
from T
cross apply T.X.nodes('//RelOp/OutputList/ColumnReference') as C(X);
Without the [1] it probably would never finish.
Doing double cross apply took about 10 minutes I believe.
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select R.X.value('@LogicalOp', 'varchar(20)'),
C.X.value('@Column', 'sysname')
from T
cross apply T.X.nodes('//RelOp') as R(X)
cross apply R.X.nodes('OutputList/ColumnReference') as C(X);
^^^ That is what I would think is the fastest. But it was not. Far from it.
I have a standard pattern I use when working with plans:
SELECT IDENTITY( INT, 1, 1 ) rowId, CAST( target_data AS XML ) AS target_data, GETDATE() dateAdded
INTO #tmp
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s on s.address = st.event_session_address
WHERE s.name = 'stolen'
ALTER TABLE #tmp ADD PRIMARY KEY ( rowId );
CREATE PRIMARY XML INDEX _pxmlidx_tmp ON #tmp ( target_data );
That one's for Extended Events but you get the idea.
I have also found it's possible to get faster plans by messing with the weightings, don't have a repro to hand an don't encourage this behaviour out in the open : )
On my dev box I sometimes need to run very IO intensive queries such as index builds and CHECKDB. This can put so much load on the disk that it hardly can process anything else. This causes enormous lagging in other programs. For that reason I sometimes need to suspend sqlservr.exe using Process ...
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select C.X.value('../../@LogicalOp[1]', 'varchar(20)'),
C.X.value('@Column', 'sysname')
from T
cross apply T.X.nodes('//RelOp/OutputList/ColumnReference') as C(X);
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select R.X.value('@LogicalOp', 'varchar(20)'),
C.X.value('@Column', 'sysname')
from T
cross apply T.X.nodes('//RelOp') as R(X)
cross apply R.X.nodes('OutputList/ColumnReference') as C(X);
@wBob I suspend (well, freeze) threads all the time to catch SQL Server doing stuff. I accept dumps and other complaints from SQL Server as acceptable collateral damage.
@PaulWhite lol that's fair. With this person though, I'm just thinking why not schedule them for the nighttime / weekend? And "you're gonna need a bigger boat".
@PaulWhite I drafted a snarky comment along those lines, along with "invest in an NVMe SSD", but deleted it, in the interests of being less snarky these days,
@MikaelEriksson I get best result with property index and ordinals, eg
;WITH XMLNAMESPACES (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT R.X.value('(@LogicalOp)[1]', 'varchar(20)') a,
C.X.value('(@Column)[1]', 'sysname') b
FROM T
CROSS APPLY T.X.nodes('//RelOp') as R(X)
CROSS APPLY R.X.nodes('OutputList/ColumnReference') as C(X)
YMMV
I don't normally use ordinals with attributes, but there you go.
Rick Byham has a WIKI post showing the fixed server and fixed database roles and how they map. You can look here: http://social.technet.microsoft.com/wiki/contents/articles/database-engine-fixed-server-and-fixed-database-roles.aspx
The chart shows that db_datareader role is identical to GRANT S...
Query:
declare @X xml = '
<item ID = "0"/>
<item ID = "1"/>
<item/>
<item/>';
select I.X.value('@ID', 'int')
from @X.nodes('/item') as I(X);
Result:
-----------
0
1
NULL
NULL
Execution plan:
The top branch shreds the XML to four rows and the bottom branch fetches the value for the a...
