Tag Info

Hot answers tagged

12

The reason for the behavior is that SQL Server can't determine how many rows will match to ForeignKey, since there is no index with RowKey as the leading column (it can deduce this from statistics on the #temp table, but those don't exist for table variables/UDTTs), so it makes an estimate of 100,000 rows, which is better handled with a scan than a ...


9

Yes SQl server installation is likely to fail if you are trying to install it on root of a drive which is never advisable and has security implications. Actually it is related to permission setting on specific folders. As per this connect item Based on further investigation this issue is due to the fact that SQL tightens the ACL for default data/log ...


6

I think I would build a table with a queue of some sorts, and have a job poll that table and pick up any work it has to perform. So for example your trigger writes a record id in a table called processqueue, then you have a job that picks up all the records from the processqueue table and do it's heavy lifting for each and every record, removing the record ...


6

(From the screenshot) I see an unusual configuration where you have SQL Server's Min and Max memory set the same. Leave min memory as default. See my answer to SQL Server Min Server Memory. To determine ideal memory, refer - How to determine ideal memory for instance? and SQL Server Maximum and Minimum memory configuration We have SSIS running packages on ...


6

The indexes that were created and dropped by DTA were most likely hypothetical indexes - ones that DTA creates while running to perform its analysis and should (but doesn't always) delete once done. These hypothetical indexes can be created even if you weren't looking at tuning that specific table! I'd recommend you query some of the system DMVs directly ...


5

SORT_IN_TEMPDB means that SQL server will use tempdb to allocate the temporary space as opposed to allocating space in the user database whose index is being rebuild. This means you will need less free space in your user database during an index rebuild operation and more free space in tempdb. It gives you better advantage when tempdb is on a different set ...


4

The script is blocked because it cannot obtain exclusive access to the database (technically it is not executing, it is waiting). I think it's in Microsoft's best interests to not allow you to drop a database that is currently being used, so I wouldn't really blame them for making this hard. What you need to do is not only ensure you run your drop from ...


4

You can generate a customized index table, for example, that has a row for all year&week pairs that the date range (a certain vacation etc) encompasses. Then you can join dateranges by going through that index table. It will be large, but does avoid large scans as you can just list all vacations that have any weeks that are in common with another ...


4

You have a few options (TL;DR -> working function is towards the end of Option #2, BUT also see Update section after Option #3!): If you are ok with just hex values (0 - 9 and A - F) you can just call CRYPT_GEN_RANDOM which was introduced in SQL Server 2008: SELECT CRYPT_GEN_RANDOM(5) AS [Hex], CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(5), 2) AS ...


4

In order to capture messages (either from PRINT or RAISERROR('', 1, 10) or like it) in .NET (regardless of calling a Stored Procedure or ad hoc SQL), you need to set up a method that will get called by the SqlConnection.InfoMessage event. The basic implementation is as follows: [SqlProcedure()] public static void Print_CLR() { using (SqlConnection conn ...


4

The main strengths of SSIS as I see it are 1) the ability to do things across servers which aren't otherwise linked (eg via linked servers) and 2) the ability to do things in parallel (eg multiple Execute SQL tasks operating concurrently). If you are using traditional SQL then you probably don't need 1), but you might need 2). Your ETL must be running in ...


4

The latest version of the RML Utilities (09.04.0051 as at today) support Extended Events tracing, so maybe you could work with your DBAs to do some controlled tracing. A screenshot from the help file: They do however have a point because the default trace template provided with RML includes statement-level events (SQL:StmtStarting, SQL:StmtCompleted, ...


4

The problem mentioned on MSDN has nothing to do with sys.sql_modules or OBJECT_DEFINITION(); they've misinterpreted the problem. What actually happened is they were thwarted by an output limitation in Management Studio, which by default will only show 255 characters and at most 8192 in any output tuple in Results to Text. So first, make sure you change this ...


4

Which option from above will perform better? Best case, both will produce exactly the same execution plan, with the same runtime performance. This can require some careful design and some fairly advanced skills, as Rob Farley mentions in his answer. Rob also has a blog post describing the core issue, and it is also discussed in one of his chapters from ...


3

Table variables and temp tables are handled differently in a number of ways. There is a great answer here with lots of specifics as to where they are different. Specifically in your case I would guess that the fact that temp tables can have additional statistics generated and parallel plans while table variables have more limited statistics (no column ...


3

Even with SQL 2000 Service broker is still your answer. I'm not going to go into how to set up a service broker solution here because to be honest it's complicated and I've only done it once. That being said, design your service broker system in 2008 and 2008 R2. For your 2000 system design a second SB system on one of your 2008 R2 systems (let's use ...


3

How much memory does SSIS need? (The real answer is: It depends.) However, Jonathan Kehayias has a formula that is pretty good, or so I think, since it works great for me. https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/ The simple formula for reserving space for needs other than SQL Server in Windows is: ...


3

You can use a filtered index to enforce uniqueness on id and is_primary when is_primary is 1. An example here: create table Computer( id char(20) not null , NIC_name varchar(100) not null , is_primary bit not null , constraint pk_computer primary key ( id asc, NIC_name asc ) ); create unique index ix_one_primary on Computer(id, ...


3

You shouldn't have any problems scripting your database out and running it on the new database. There isn't any data for foreign keys to affect. After that you will have to be more careful to load the data in the correct order. Lookup tables first, then data tables etc. That isn't a huge issue since you can get the order down in test before trying it in ...


3

Changing your table from a heap to having a clustered index should significantly improve your performance on both queries and perhaps even on inserts. Generally speaking, your clustered index should be narrow, unique, and ever increasing. Using a datetime that you can't guarantee to be unique is not ideal because it's 8 bytes and, since it isn't unique, sql ...


3

SQL Azure Transparent Data Encryption is possible, but it is currently still in preview. If you do not want to accept the supplemental terms of use for this then you can simply install SQL Server on the VM you just created or provision a new machine and use one of the numerous pre-built images that are available. I would recommend you simply spin up a new ...


3

I believe its just a message and not an error unless and until the task is getting impacted when this message is logged in the error log: We also encountered the same for one of our production server, where windows admin team enabled the SQL Server backup through VSS (Volume Shadow Copy Services). And when this process runs it freezes I/O temporarily to ...


3

First, let's clarify terms. AlwaysOn is a Microsoft marketing term which includes both SQL Server Availability Groups and SQL Server Failover Cluster Instances. You should stop using AlwaysOn and be specific for all future communications. If you are referring to FCIs: the configuration should be completely transparent to your application. They should ...


2

Decimal and whole numbers are encoded very differently in varbinary. Decimals need more space. Try: SELECT CONVERT(VARBINARY(32), 1), CONVERT(VARBINARY(32), 1.0); As for your ultimate goal, storing whole numbers as varbinary to save space, I think you've answered that question yourself - not worth it.


2

What would be the typical "gotchas" involved with scripting all the data to migrate a 2014 SQL server database to 2012? A counter question would be why are you downgrading from 2014 to 2012 ? Now lets address your question, if you are just downgrading from 2014 to 2012 with same version (standard --> standard) then there should not be any problem ...


2

Overlapping date ranges can be found thus WHERE FromDate <= QToDate AND ToDate >= QFromDate You will get one row returned for each partially or fully overlapping range. For example, if you had separate pay rates for morning, afternoon and evening shifts and someone worked all three, there would be three rows returned. The same basic pattern ...


2

Yes, if you are using Peer2Peer replication between the primary and secondary servers which is an enterprise only feature I would personally use Mirroring/AlwaysOn for redundancy between primary and secondary sites as this includes ways to failover without changing the DNS records. Either by adding ';Failover Partner=PartnerServerName;' to the connection ...


2

Double check that the paths you are trying to restore to exist on the D drive. SQL Server will not create the directory path if it doesn't exist. If you haven't already, run the restore command with the verify only option MSDN. If that is successful here are a few additional things you can try to isolate why it is failing If you have the space on the ...


2

1. Why Statistics IO show higher reads than Profiler? No idea, sorry. There are often differences because they are measuring different things as alluded to in the knowledge base article. There is no additional documentation on this that I am aware of. You might be able to infer some things through detailed testing, but there's really no guarantee whatever ...


2

Use the built-in bitwise operators to examine the column. For example select schema_option & 0x01 AS GeneratesObjectCreation, schema_option & 0x02 AS GenerateCustomSP, ... etc.



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