Tag Info

New answers tagged

3

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 ...


1

If I understand you correctly you are looking to see who is blocking and why, not just gathering overall statistics. (Both approaches have real value, of course.) Since every event is transient, so it is not surprising that some values would not return what you expect. We have been using the approach outlined by Tony Rogerson quite a few years ago that ...


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 ...


0

Adding the ORDER BY clause made it run almost instantaneously and I realized that it was only returning records from the first date because, duh, it was a SELECT TOP 10 query, there were more than 10 records from the first date. If I turned it into a SELECT * query I got everything in the range and if I added the ORDER BY clause I got it quickly. Thank ...


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 ...


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) ...


8

This is a frequent problem with poorly chosen clustered keys. Time series in general should be organized by time, since most queries ask for time ranges. Case in point, your query. If you have a correlation between id and timestamp then you can add an appropriate id based predicate: SELECT TOP 10 * FROM messages m INNER JOIN ...


2

This was rather longshot but since the OP says it worked, I'm adding it as an answer (feel free to correct it if you find anything wrong). Try to break the internal query into three parts (INNER JOIN, LEFT JOIN with WHERE IS NULL check, RIGHT JOIN with IS NULL check) and then UNION ALL the three parts. This has the following advantages: The optimizer has ...


0

Depending on the meaning of where not(pe.ThisThing = 1 and se.OtherThing = 0) and the id format you have chosen to use I would try different indexes and something like this: /* http://dba.stackexchange.com/questions/71415/optimize-select-on-subquery-with-coalesce */ /* assuming: where not(pe.ThisThing = 1 and se.OtherThing = 0); or the Id format of ...


0

All set operators are translated to joins or join-like operators. You can witness that in the query plan. For that reason the full outer join that you have there is the most efficient you can do. Disregarding, of course, the hopefully rare situation in which the optimizer picks a bad plan and a rewrite happens to perform better by luck. This can always ...


0

My intuition would be that this should not be a problem as by the time COALESCE(pe.StaffName, se.StaffName) AS StaffName does anything all the rows from the two sources should have already been pulled in and matched up so the function call is a simple in-memory compare-to-null-and-pick. Obviously this isn't the case so perhaps something in one of the sources ...


2

Assuming the primary key of children is (horror) id: select p.* from parents p inner join children c on c.parentId = p.id where c.firstname like 'tom' and not exists ( select 1 from children c2 where c2.parentId = p.id and c2.id <> c.id ) ; or using GROUP BY: select p.* from parents p inner join ( ...


4

Bjorn, There is nothing out of the box that will force the developers to use non-deprecated features (as they are deprecated, not removed). The most tailored solution that could be created is with DDL trigger(s), but note that they can be tricky... especially if not extremely familiar with them. I would suggest PBM, but it has limitations that won't ...


2

You are asking 2 different questions. So I will try to answer this accordingly. First is to identify deprecated features using Use Server side trace or Use Extended Events as Sean pointed out. Now to enforce coding standards, you can use Policy Based Management or From Codeplex - Enterprise Policy Management Framework


3

As ypercube commented No, if the query is what you show, he is totally wrong. It's pretty sargable as it is. You can verify this by: Creating a simple test table with a [Date] column. Insert a large number of rows with varying dates. NOTE: In the above "large number" and "varying dates" is a precaution to ensure that your query is selective enough. ...


2

Simple answer for this is: SELECT mt.Id , mt.TranDate , mt.TranType , CASE WHEN mt.TranType = 1 THEN mt.TranAmount ELSE NULL END AS DepAmt , CASE WHEN mt.TranType = 0 THEN mt.TranAmount ELSE NULL END AS WitAmt FROM dbo.MyTable mt ORDER BY mt.Id;


1

You can calculate the Delay in a cross apply and use dateadd() to add it to st_date in the main query. select D1.Split3_ID, D1.CU_ID, D1.order_id, D1.st_date, D1.sku, D1.Priority, D1.Delay, dateadd(day, D2.Delay, cast(D1.st_date as date)) as CourseDate from dbo.set_dates as D1 cross apply ( ...


2

The column max_length is the maximum column length in bytes. For the National-Character set string types (NCHAR and NVARCHAR), each character requires two-bytes, so NCHAR(10) would have a max_length value of 20.


0

It looks to me like you don't need the derived table. Is there any reason you couldn't write it like this: SELECT Sum(x) * 0.1, Sum(y), a FROM tx INNER JOIN ty ON tx.a = ty.a WHERE x = 1 GROUP BY a This probably won't solve all of your performance issues, but if you set statistics IO on and look at the logical ...


3

There is no inherent order inside a table. There is no pre-stablished order when you insert rows. If run for example the following query: SELECT A.A, B.B FROM tblA A JOIN tblB B ON A.id = B.id Without an ORDER BY, then no particular order will be used. If you want some particular sorted results, you must use an ORDER BY clause. You could use a ...


2

First of all, what you are about to design is probably a VERY bad idea. A much better solution would be to have a dynamic schema where you add new tables and have the application understand how to query those table (you could place them in a schema). This largely avoids all the locking and query plan issues you are bound to run into with this model. There is ...


1

The problem you are actually facing is that you are calling two items asynchronously on your end, but they in fact need to run in order on the database end per your requirements. There is nothing that says the first call should finish before the second since it's asynchronous. The process you have in place does not make sure that happens. IMHO either use a ...



Top 50 recent answers are included