Tag Info

Hot answers tagged

7

A non-clustered index scan may be chosen in this scenario: the optimizer determines that it is cheaper to scan all rows rather than perform seeks/range scans the non-clustered index is "skinnier" than the clustered index the non-clustered still covers the columns needed by the query (or it covers enough of them and a lookup for the remainder is still ...


5

You can't, ish, is the short answer. You have 3 ish components in a data flow task that can modify data. OLE DB/ADO.NET Destination, OLE DB Command and a Script Component. I'm ignoring Script Component for this answer as it's the swiss army knife of components. If you want to do something badly enough, the Script Component will allow you to. Whether ...


4

Yes but not with row_number(). You can use either the window aggregate min(): select id, code, descriptor from ( select id, code, descriptor, min_code = min(code) over (partition by id) from @t ) as t where code = min_code ; Or the rank() window function (or the dense_rank() one, they'll both work the same for the rnk = 1 check): ...


4

There are a couple of possibilities. First if the orphaned user is a windows login/group (type U or G) then no problem. -- If the login doesn't currently exist on the server CREATE LOGIN [Windows Account] FROM WINDOWS; -- Fix the user USE dbname ALTER USER [user name] WITH LOGIN = [Windows Account]; If it's a "SQL Login" (type S) and the login exists ...


4

This happens because the msdb log file name has been changed from the default which is "MSDBLog". In my case, it was called "msdb_log". The name "MSDBLog" is hard-coded into the upgrade script so it fails if the name has been changed. To fix the problem, we need to rename the log file to 'MSDBLog'. We have to start SQL with trace flag 902 to stop it from ...


3

Of course you could also write this using a CTE: ;WITH t AS ( SELECT id, code, descriptor, min_code = MIN(code) OVER (PARTITION BY id) FROM @t ) SELECT id, code, descriptor FROM t WHERE code = min_code; You cannot say: SELECT id, code, descriptor FROM @t WHERE code = MIN(code) OVER (PARTITION BY id); Because: Msg 4108, Level 15, State 1, ...


2

The link provided by Martin Smith to an existing SO question provided me with the answer: The main restrictions are that foreign key relationships must be based on a single key to contribute to the simplification process


2

From SQL 2012 onwards, there is the WITH RESULT SETS option, which makes it easy to use the output from the OUTPUT clause with no sticky issues in SSIS not understanding the metadata for the resultset, eg you can use the output from an OUTPUT clause in SSIS Source components, eg OLE DB Source. I set up a simple example using a stored procedure with MERGE ...


1

A restore failing on the secondary will have no impact on the primary. You want to monitor your secondary DBs to make sure this hasn't happened.


1

You can calculate a running total in SQL 2008 R2 by using the ROW_NUMBER() window function along with a correlated sub-query to calculate the running total column. Here is an example using some data I have available. You would need to amend the query accordingly to suit your tables/data and would likely need to use a PARTITION BY within the ROW_NUMBER ...



Only top voted, non community-wiki answers of a minimum length are eligible