Hot answers tagged

10

Your first query works as an inner join because the y.TransType = 'used' condition which uses the right table is in the where clause. Your second query can be rewritten without derived tables by simply moving that condition to the on clause: select x.*, y.* from factI as x left join factI as y on x.tickedId = y.tickedId and ...


6

That DONE_IN_PROC message from the execution plan is completely independent of your WHILE loop, and isn't what's being checked in your conditional BREAK. Martin deleted his answer but this is very much by design. Your query shouldn't behave differently depending on whether or not your client tool is retrieving execution plans - that would be bad. Execution ...


5

If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower? It depends. No - if you run exactly the same queries as before (no access to the new data at all). SQL Server's partitioning implementation creates a separate rowset for each partition, so when you create a partitioned index, it creates a separate b-tree ...


4

Based on your sample data it looks like there are 6 columns in total. COL1, COL2, COL4, COL5, COL6 = All fixed width . COL3 = This is a fixed width for a given day, but it is variable day to day (based on my understanding). You should be able to calculate COL3's width each day based on the length of any given line. You know COL1,2,4,5,6 are all fixed ...


4

Aside from the typo, another way is to use STUFF to replace the first character directly: -- Demo SELECT STUFF ( O.name, 1, 1, UPPER(LEFT(O.name, 1)) ) FROM sys.objects AS O;


3

Tables DECLARE @RateCategory AS table ( RateCategoryID integer PRIMARY KEY NONCLUSTERED, Managed bit NOT NULL, Customer varchar(10) NULL, Category char(1) NULL, UNIQUE CLUSTERED (Managed, Category, Customer) ); DECLARE @Job AS table ( JobNumber integer PRIMARY KEY, Managed bit NOT NULL, Customer varchar(10) NOT NULL, ...


3

The issue with your current query is that you are only looking for queries that could be running at that very moment. To get a good full picture of your server state, you could try to run SQL Server Profiler or check for connections with a built-in stored procedure likesp_who. These approaches are more complicated and time-consuming than using a stored ...


2

If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower? Wrong question. Yes, they will be slower - the index is deeper, more data must be accessed to filter. But the real question is: Will it be significantly or at least noticeable slower - and that is likely a no, because the index depth growth is NOT linear ...


2

What sort of exec requests don't have a sql handle? (sys.dm_exec_requests) Sql_handle is a hash value which identifies SQL text of the batch/query being submitted to the server. Text can be NULL for encrypted objects so this can cause sql_handle to be NULL


2

I found the answer eventually .... There was a job set up to incrementally process the cube. Incremental cube processing requires a query to be defined to select new fact data to merge into the cube. In my case, this query was defined badly and was selecting the entire set of fact data, i.e. all the data that was already in the cube. Although fully ...


2

No ! But, you can : 1 script our your database schema and use BCP out and bcp in method or 2 restore the database as a copy on EE or Dev edition, remove all enterprise features and backup the database and restore it on standard edition.


1

Since you are running SP3 in QA and SP1 in production (assuming you are running with Trace Flag 4199) you are missing out on a lot of performance related fixes in your production environment. My first suggestion would be to test the query and compare the generated plan on servers of the same build number. Without details of the query and the underlying ...


1

You need to break apart your AND (FP.Age, FP.PersonType) INpart. The following code parses correctly and should provide the data you want. AND ( FP.Age IN ( SELECT FP.Age FROM FactPerson FP WHERE (FP.PersonType = 'A') AND (FP.Age BETWEEN 18 AND 21) ) AND FP.person IN ( SELECT ...


1

I too was experiencing a 5 to 10 second delay when right-clicking a table to pull up the context menu. My situation might be a little different than some however because I use local databases only. My solution: After reading the answer from imran about disabling antivirus, I took that concept a step further since I do not want to disable my antivirus ...


1

It sounds to me like you are suggesting that you require a highly available file share (see https://blogs.technet.microsoft.com/clausjor/2012/06/07/smb-transparent-failover-making-file-shares-continuously-available/). In order to do this you will obviously need Clustered storage (an ip and network name). It would be a little unusual to do this using your ...


1

In your first query, in the where clause, you're restricting results to those where y.TransType = 'used'. This turns the left join into an inner join because you're throwing away results where y.TransType is null (aka where the other ticket doesn't exist). An easy fix for this is to move that condition to the on clause like this: select x.* from factI as ...



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