Hot answers tagged

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 the account id is always at the third position use split_part() select * from gorfs.seg WHERE split_part(full_path, '/', 4) = '4865' Note that due to the leading / the actual index is 4, not 3. The following query: with seg (id, full_path) as ( values (1, '/userfiles/account/4556/attachment/1234'), (2, ...


5

And I want to limit the number of referees on a single match to three, How can i do that? One method is with a trigger: CREATE TRIGGER tr_match_referee ON dbo.match_referee FOR INSERT, UPDATE AS IF EXISTS( SELECT 1 FROM inserted JOIN dbo.match_referee WITH (READCOMMITTEDLOCK) ON match_referee.id_match = inserted.id_match GROUP ...


5

Summary Add the index: CREATE INDEX index_name ON TableName (companyid DESC, id DESC) INCLUDE (active); Try this rewrite: SELECT COUNT_BIG(*) FROM ( SELECT TOP (9223372036854775807) TN1.active FROM TableName AS TN1 WHERE TN1.id = ( SELECT MAX(TN2.id) FROM TableName AS TN2 ...


5

Since you are running Microsoft SQL Server 2014 - 12.0.2000, the very first RTM build including the new Cardinality Estimator I would strongly suggest you try updating to one of the latest CU's. As stated in this blog post on msdn You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix. SQL Server 2014 Service ...


4

There are several flaws to this approach: The term "preview" can be quite misleading in most cases, depending on the nature of the data being operated on (and that changes from operation to operation). But what is to ensure that the current data being operated on will be in that same state between the time the "preview" data is gathered and when the user ...


4

Not exactly what you are looking for but could perhaps be of interest to you. The query creates weeks with a comma separated string for the days used in each week. It then finds the islands of consecutive weeks that uses the same pattern in Weekdays. with Weeks as ( select T.*, row_number() over(partition by T.ContractID, T.WeekDays order by ...


4

This turned out to be a tricky puzzle which took up far more time than I thought it would - I guess for you too? :-). I (finally) managed to solve it thus: First I created (using DDL and DML for those who wish to try this at home) a table called stringtest: CREATE TABLE stringtest (my_string VARCHAR(255)); and populated it as below INSERT INTO ...


4

This one uses a recursive CTE. Its result is identical to the example in the question. It was a nightmare to come up with... The code includes comments to ease through its convoluted logic. SET DATEFIRST 1 -- Make Monday weekday=1 DECLARE @Ranked TABLE (RowID int NOT NULL IDENTITY PRIMARY KEY, -- Incremental uninterrupted sequence in the ...


4

The principal difficulty here is probably that the alternative value of MemoText you want to use in case the fieldnum=142 value is null is in a different row. It would be much easier if it was just a different column of the same row. That way you could use the pattern suggested by Manu – I mean specifically this part: set <field_to_update> = ...


3

I ended up with an approach that yields the optimal solution in this case and I think will do well in general. The solution is quite lengthy, however, so it would be interesting to see if someone else has a different approach that is more concise. Here is a script that contains the full solution. And here is an outline of the algorithm: Pivot the data ...


3

You can UNPIVOT and then PIVOT. Half of it is below (untested) WITH P1_Src As ( SELECT ID_Skill, Val, Col FROM Job_Skill UNPIVOT (Val FOR Col IN (Min_Job, Idea_Job, Max_Job)) AS U ), P1 AS ( SELECT Col, [1], [2], [7], [8] FROM P1_Src PIVOT (MAX(Val) FOR ID_Skill IN ([1], [2], [7], [8])) AS P ) SELECT * FROM P1 You would need to apply a case expression to ...


3

From comments: DISTINCT is very similar to GROUP BY <all selected columns> and getting rid of the temporary table may be impossible when joining many tables as the server needs it to check uniqueness of the returned rows. Covering indexes (Using index) are quite useful when you need to get the top performance. I prefer IN() instead of multiple ORs ...


3

I am assuming that the ID column is either the PK or otherwise declared as unique in all three tables. Now, if each of these scenarios is equally possible: all three tables have a row with the ID of 101, only two (any two) of the three tables have a row with the ID of 101, only one (any one) of the three tables has a row with the ID of 101, and you want ...


3

I couldn't understand the logic behind grouping weeks with gaps, or weeks with weekends (e.g. when there are two consecutive weeks with a weekend, which week does the weekend go to?). The following query produces the desired output except that it only groups consecutive weekdays, and groups weeks Sun-Sat (rather than Mon-Sun). Whilst not exactly what you ...


3

You might have to play with CHAR(13) and CHAR(10) depending on the source of the text, but this might be simply: WHERE CHAR(10) + col LIKE '%' + CHAR(10) + '[A-Za-z].%' ------^^^^^^^^ in case the text *starts* with a lettered list Note this will also find: Some sentence is here a. foo Some more text later If you need to only return rows where there ...


2

My concerns are as follows. The transaction handling doesn't follow the standard pattern of being nested in a Begin Try / Begin Catch block. If this is a template then in a stored procedure with a few more steps you could exit from this transaction in preview mode with data still modified. Following the format increases developer work. If they change the ...


2

Based on your comment this seems to be an ISO week number, but the logic is the same for any other numbering schema: take the year and convert it to the first day of the first week of this year, then add the (weeknumber -1) * 7 to get the starting day of the week and add 4 to get Friday. I don't know about DB2 syntax, but a quick check revealed that TRUNC ...


2

The simplest approach is often the best and I don't really have that much of an issue with code duplication in SQL, especially not in the same module. After all the two queries are doing different things. So why not take 'Route 1' or Keep It Simple and just have two sections in the stored proc, one to simulate the work you need to do and one to do it, eg ...


2

Using an index requires bouncing back and forth between the index and the data. In MyISAM, each index is a BTree sitting in the .MYI file. At the leaf node of the index is a pointer into the .MYD file. (Or, for FIXED, it will be a record number.) Your SELECTs are happy to scan linearly through the index (a BTree is efficient at that), but then for each ...


2

You could simply create a Group wihtin the registered servers pane in SSMS and add all servers to that group. Then you are able to run a query against the whole group. Just run the Create procedure (or whatever you need) there and it will be executed on all servers in the group (that could have connected with success). Be aware that select queries ran ...


2

Your query has many issues that difficult optimisation. You use LEFT JOIN in several tables where INNER JOIN should suffice (e.g. all cases of ged_tbl_document_dv and ged_tbl_document_type). You include tables that you later never really require (e.g. all cases of ged_tbl_document_type), so removing all of them wouldn't impact the result (replace all ...


2

Not sure what you are using for this, but apparently it is looking for a very specific answer. Your answer will work, but it looks like they are expecting WHERE City <> 'Paris' This would also be valid: WHERE City != 'Paris' but you would probably get the same error.


2

How do I convince SQL Server that the function is nondeterministic and make the function evaluate for each row, computing different hash values? SQL Server marks the function nondeterministic, because it accesses data in system tables: SELECT UserDataAccess = OBJECTPROPERTYEX(FN.ObjectID, 'UserDataAccess'), SystemDataAccess = ...


2

We solved the issue. The issue was caused by the new Cardinality Estimator in SQL 2014. We disabled the new estimator by activating trace flag 9481 and removing the query plan from the cache then the query worked again.


2

So it was hard for me to determine what your actual question was from the post, but I assume it is that when you execute: SELECT ref_no, CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN 0 ELSE CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN MAX(amount) OVER (PARTITION BY ref_no ORDER BY paid_date ASC ...


2

For the sake of completeness, here is a two-pass gaps-and-islands approach that I tried myself before asking this question. Update: As I was testing it on the real data I found few cases when it was producing incorrect results and fixed it. Here is the algorithm: Generate islands of consecutive dates (CTE_ContractDays, CTE_DailyRN, CTE_DailyIslands) and ...


2

will this script delete all tables with the prefix of Test_ on the database I execute it on? Yes, it will DROP tables. DELETE is normally used with deletion of data. Your script will physically drop the tables. Also, please dont use INFORMATION_SCHEMA and there is no need for a cursor. Your script can easily be written using dynamic sql. EDIT : In ...


1

Whenever you create a table with constraints in Oracle, an entry is made into the user_constraints table and if constraints involve creation of an index (like in this case, primary key constraint also creates a clustered index), an index is also created on the table which you can find querying user_indexes. select * from user_constraints where ...


1

...or you can simply use parenthesis to isolate the components and cast as usual: select ((regexp_matches(full_path, '\d+'))[1])::int from gorfs.seg where account_id = 4865 The question is: why do you need to extract the account_id from the full_path if you already have it in the where clause?



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