Tag Info

Hot answers tagged

14

The other answers are technically correct, but not real-world correct. Here's what you need to ask the business: What time horizon am I aiming for? In your case, you're looking for a 12-month number. During that time, will we be archiving data, or keeping all data? In some businesses, you're allowed to (or required to) only keep a certain amount of data, ...


12

You can't accurately project future growth without a history of previous growth. You can however cheat and get a rough trend using backup history, as detailed by Erin Stellato in Trending Database Growth From Backups. Plot the output of the following query in Excel: SELECT [Database] = [database_name] , [Month] = DATEPART(month,[backup_start_date]) ...


7

The safest way is probably to only add zeroes when the length of the column is 1 character: UPDATE Table SET MyCol = '0' + MyCol WHERE LEN(MyCol) = 1 This will cover all numbers under 10 and also ignore any that already have a leading 0. EDIT To just select the data try: SELECT CASE WHEN LEN(MyCol) = 1 THEN '0' + MyCol ELSE MyCol END FROM ...


7

To start off, NULL does not mean "no value" it means "Unknown value" in SQL Server. There is a session setting called ANSI_NULLS that could make your queries behave as you would like them to, however, it's deprecated and will be forced to ON (which you don't seem to like) in a future version: http://msdn.microsoft.com/en-us/library/ms188048.aspx I get what ...


7

My question is, do you think we should look into upgrading to SQL Server 2014 Enterprise so that we can partition our time sheet tables? No. Absolutely not. On a pocket change server of 8 cores you would be spending ~$50k and be unlikely to see any benefit. I would suggest trying, in this order: Increase the memory allocation to SQL Server. You ...


6

Knowing this, why does sql server need to issue U locks (when using RCSI)? It seems to me that sql server could simply read the rows, and request a X lock directly if an update must be performed. Unlike SI, RCSI does not detect update conflicts. As documented in Books Online, modifying data under RCSI reads currently-committed data, not a possibly ...


6

It is possible to set up a method to grant rights to run a job that a user does not have enough authority to run on its own. Erland Sommarskog has written a lot on how to grant permissions through stored procedures using counter-signatures. He has a solution at: http://www.sommarskog.se/grantperm.html#countersignatures The key point is: "To be able to ...


6

It's a question of timing. Consider deleting StudentID #1: The row is deleted from the Student table The cascade delete removes corresponding rows from Enrollment The foreign key relationship GPA -> Student is checked The trigger fires, calling MergeGPA At this point, MergeGPA checks to see if there is an entry for Student #1 in the GPA table. There is ...


5

Do I need to make the "unlock" SERIALIZABLE as well? Despite the name, the serializable isolation level does not guarantee transactions will be executed sequentially, or in the order received. Rather, serializable guarantees transactions will have the same persistent effects on the database as if they had executed sequentially, in some undefined order ...


5

It is fairly well documented that UDFs force an overall serial plan. I'm not certain it is all that well documented :) A scalar T-SQL function indeed prevents parallelism anywhere in the plan. A scalar CLR function can be executed in parallel, so long as it does not access the database. A multi-statement table-valued T-SQL function forces a serial ...


4

Making use of Jeff Moden's Tally-Ho! CSV splitter from here: CREATE FUNCTION [dbo].[DelimitedSplit8K] --===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter CHAR(1)) --WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE! RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values ...


4

You can use the following query to determine how much RAM is being used by each database: USE master; SELECT d.name, CAST(COUNT(1) AS BIGINT) * 8192 / 1048576 AS MBinMemory FROM sys.dm_os_buffer_descriptors bd INNER JOIN sys.databases d ON bd.database_id = d.database_id GROUP BY d.name ORDER BY d.name; To see memory used by objects in a specific ...


4

Sure you CAN do it, but why would you? To save a few CAST expressions? That seems a bit weak. The reasons to have auto incrementing surrogate int PK's are many: You pretty much never have to manage them with normal databases. Ever. Unless you are rebuilding tables or inserting data where you have to turn the identity off and on. But operationally it works ...


3

I will take you question point wise 1.In my opinion its higly unlikey for an unused large table to cause issue with query running for diffrent database. SQL Server memory is dynamic in nature if suppose large portion of memory is occupied by datapages of DB1 Lazy writer and checkpoint pages will work together to age out pages which are not used recently or ...


3

You can do a join on Value <> Value for the settings you are looking for. select S1.Settings as Settings1, S1.Value as Value1, S2.Settings as Settings2, S2.Value as Value2 from Settings as S1 inner join Settings as S2 on S1.Value <> S2.Value and S1.Settings = 'clientvisits' and S2.Settings = ...


3

Turns out I was installing SQL Server Management Studio, without the database engine which is what was needed. Needed to install SQL Server Express with Tools which solves this problem. Link to where I found the answer is below. ...


3

The NULL problem is a thorny issue with SQL. It is basically a mistake that is now burnt into all SQL software on the planet. We have to deal with it. value <> 26 or value is null is a good way to implement this logic. There are other formulations of the same semantics. If you know that value is never -1 (for example) you can say ISNULL(value, -1) ...


3

Create a trigger on update (another on delete is probably a good idea). CREATE TRIGGER [Table].[uPreventUpdateOops] ON [Table] FOR UPDATE AS BEGIN DECLARE @Count int SET @Count = @@ROWCOUNT; IF @Count >= (SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE OBJECT_ID = OBJECT_ID('Table') AND index_id = ...


3

First of all, the Table Designer is not the wisest of all software and probably does not choose the approach to change the table that you would expect. The designer usually creates a new table, moves the data, then drops the original table. However, based on your error the designer also may not be FILESTREAM savvy. But if you try it yourself through ...


2

after research, I had found the answer. The Problem is the @performance_condition, my example will not work. But if I add a "SQLServer:" at the beginning of the Condition, like this: N'SQLServer:Replication Dist.|Dist:Delivered Cmds/sec|MyFancyReplication|>|1000' This is the Key to add custom warnings to Sql Server 2014. I believe this is a bug with ...


2

The problem lies in your clustered index (Index id of 1) which is the table data. This is why a rebuild is not going to fix it. If this is truly a huge deal (for example financial history or data) then I would suggest contacting a consultant who deals with corruption as they will be able to find the issues and mark down what may or may not be lost or ...


2

Veeam will give you full backups of your SQL Server VM which are not that useful when all you want to do is restore a database to see what a particular value was on a certain date. You don't want to de restoring an entire server to get at a database. The replication gives a level of High Availability but it wouldn't save you from someone running a stupid ...


2

Yes, shouldn't be too difficult, try: SELECT a.ID, a.ID_Fruit,,, so on b.ID, b.ID_Fruit, b.ID_Crate, b.[DateTime] FROM TABLEA a INNER JOIN TABLEB b ON a.ID_Fruit = b.ID_Fruit INNER JOIN (SELECT ID_Fruit, MAX([DateTime]) FROM TABLEB GROUP BY ID_FRUIT) maxtime ON b.[DateTime] = maxtime.[DateTime] Its not quite clear if you mean that it should be the ...


2

When I run a query with a bad plan the server slow down and all other query, That says they're contending for the same resource, and non problems on disk I/O doesn't say much about why the disk wouldn't be the resource they're contending for. Logically we know that a query with a "bad plan" is going to read a lot of data from the disk, perhaps ...


2

As I said in the comments above, this should really be formatted in the application layer, where you are controlling the presentation to the user. The issue lies with the way SQL handles the DATEDIFF function, in that comparing the HOUR difference between 08:59 and 09:00 will result in 1 hour, because the hour has ticked over and there is 1 hour difference ...


2

Example 4 has the fewest scans and reads: Example 1 SQL Server parse and compile time: CPU time = 4 ms, elapsed time = 4 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. example1 Id FirstName 1 2 Aaron 1 3 John 1 8 Aaron 1 9 John 1 14 Aaron 1 15 John 1 20 Aaron 1 ...


2

select obj.PK_ObjectID, obj.name, obs.timestamp, obs.note -- etc from Objects as obj join Pts as obs on obj.PK_ObjectID = FK_ObservationID order by obj.PK_ObjectID, obs.FK_ObservationID You're googling the wrong places, if I may say so. You yourself say you're new to SQL, and yet you've figured out how to implement a cursor instead of writing ...


2

This would most definitely not be preferred to an auto-increment integer field. For starters: Index width would be degraded significantly with your proposal. Just how do you propose to manage all those random 2-digit integers to enforce uniqueness. Have you thought of how much code would have to be written and maintained to implement this scheme. Won't ...


2

I will answer you question point wise like you asked No its not necessary that LSN of differential backup would match the LSN of full backup. LSN is basically a stamp of last records that accessed the log. You should have also included DatabaseBackupLSN/checkpointLSN and then you will note that DatabasebackupLSN of differential backup will match the ...


2

Think of the right answer in terms of the COALESCE command, which returns the first non-null value from a list. The prioritized list of recommendations are: Your tested and proven settings. Some folks have enough time to do repeated benchmarks of their own applications on their own hardware. That's fairly unusual, though. Microsoft's Fast Track Reference ...



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