New answers tagged sql-server-2005
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, ...
Late answer but could be of use to other readers. Please, have in mind that there are lots of maintenance or reporting tasks, you can create, that carry unseen risks associated with them. What would happen when a drive gets filled up during differential backups performed daily? And what if an index rebuild job runs unusually long? How about if a data load ...
As described in previous answers - After indexes are created, they will undergo automatic maintenance by the SQL Server Database Engine whenever insert, update or delete operations are executed on the underlying data. Even so, these automatic modifications will continuously scatter the information in the index throughout the database – fragmenting the index ...
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 ...
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 ...
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 ...
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 ...
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
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 ...
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 ...
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.
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.
Corruptions can occur due to media corruption or memory bit-flips. Ideally, one has a backup and restores from a backup. If you cannot restore from a uncorrupted DB, when you have a situation where you have inconsistencies between internal tables you can try to run REPAIR_ALLOW_DATA_LOSS. If repair does not fix the issues you can force a new broker in the ...
You must pivot your data. This can be done using the Pivot operator: SELECT ID, [date] ,  as Hour1,  as Hour2,  as Hour3,  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 ...
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]
looks like some one made an error while dml statement , other wise it shouldnt have given you that error. if you do a select statement and check the order of the number after 1234567 is 1234700 , then it is definitely insert statement error.
This is faster SELECT SUM (row_count) FROM sys.dm_db_partition_stats WHERE object_id=OBJECT_ID('Users') AND (index_id<2);
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.
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. ...
Top 50 recent answers are included