Hot answers tagged

10

That's a really big question so let's break it up a bit. What can I do in advance? Start with some required reading. 2008 R2 Backwards Compatibility 2008 Backwards Compatibility These links have links to further information such as Deprecated SQL Server Features Discontinued SQL Server Features Breaking Changes Behavior Changes to SQL Server ...


10

So my question here is not how, rather will like to know the risk involved or any pre-checks that can be done before planning this upgrade? You should run upgrade advisor and address the issues reported by it before migrating. Refer to my answer for an extensive list of pre and post upgrade steps. SQL server which needs to be upgraded from version ...


7

General view updatability The key part of the CREATE VIEW (Transact-SQL) documentation is: Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table. Note that even if the view is technically updatable, it may not be actually updatable in practice, due to limitations of the query ...


5

Today's date is 2016-02-11 and this query return 2016-02-11 00:00:00.000: SELECT curDay = DATEADD(day, DATEDIFF(dd, 0, getdate()), 0) You can then include it into your DATEADD statement: SELECT curDay = GETDATE() , DATEADD(day, 1 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Monday , DATEADD(day, 2 - DATEPART(weekday, ...


5

The ability to create a user-defined table type was first introduced in SQL Server 2008 and so it is not possible to do this in 2005. See the answer to this question: http://stackoverflow.com/questions/2606263/how-to-create-a-table-type-in-sql-server-2005


3

Imagine your UPDATE is updating s with the FROM clause of your view. Then read this blog I wrote recently to see how it could be affected. http://sqlblog.com/blogs/rob_farley/archive/2016/01/12/join-effects-with-update.aspx Assuming you're not breaking the rules for updateable views, then you should be okay. Just also be wary of the things in my post.


3

You update column state inside the view. It refferences s.ClaimStatusName AS [Status] inside the view. From the code of the view we see that you update dimClaimStatus table (dimClaimStatus AS s). Seeing that you have 2 columns idClaimStatus and ClaimStatusName you have ID \ Name structure of the table. Inside the view you show ClaimStatusName. So you ...


3

You must pivot your data. This can be done using the Pivot operator: SELECT ID, [date] , [1] as Hour1, [2] as Hour2, [3] as Hour3, [4] as Hour4 , total as [Sum] FROM ( SELECT * FROM data d CROSS APPLY (SELECT total = SUM([money]) FROM data WHERE [date] = d.[date] AND ID = d.[id]) a ) t PIVOT ( MAX([money]) FOR [hour] IN ...


2

First a quick point .. It's pretty unusual someone "needs" to use read uncommitted unless the need is to randomly get bad data. Lecture done, permissions are generally found under individual commands. In this case you are looking at SET TRANSACTION ISOLATION LEVEL. Typically you would find a Security/Permissions section, although in this case there ...


1

IMO if you are going to the effort to migrate then you should go all the way to 2014 even if you run it in 10.0 compatibility mode. You are going to pay for the license anyway. Also the regression testing effort and developer/DBA learning curve will be significant in either case. If you stop at 2008R2 now you will just have to repeat the exercise again in ...


1

SQL Server 2014 support parallel DML in the form of SELECT INTO and SQL Server 2016 support parallel INSERTs into heap via INSERT SELECT providing the TABLOCK hint is used on the source table, this also works on clustered column stores, I am yet to try this out with clustered indexes though.


1

Sounds like you were attempting to perform the delete operation in a single transaction. This being the case, the answer is "Yes, it will most likely break." Even without replication, it's a bad idea to do such a large operation in a single batch. You would be better off looping a smaller number of deletes - it's faster and less of a performance issue. ...


1

This is possible using piecemeal restore. The source database's objects and filegroups have to be organised in a way that supports this. The RESTORE requires additional specific keywords. While the concept may take a little bit of getting used to, the amount of scripting to implement is no more than that for other solutions suggested.


1

Try something like this: SELECT ID ,[date] ,SUM(case when hour = 1 THEN money END) AS Hour1 ,SUM(case when hour = 2 THEN money END) AS Hour2 ,SUM(case when hour = 3 THEN money END) AS Hour3 ,SUM(case when hour = 4 THEN money END) AS Hour4 ,SUM(money) AS [Sum] FROM data GROUP BY ID, [date]



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