Hot answers tagged

22

The time returned by NOW(), and other date time functions, is derived from the start time of the query. The THD class here is used to contain all the information for the connection. The NOW() function implementation grabs this value and returns it into the now_time structure. MySQL docs for NOW() also state: NOW() returns a constant time that indicates the ...


17

Another aspect: SYSDATE() can be different, but NOW() cannot. mysql> select now(6), sysdate(6), now(6), sysdate(6); +----------------------------+----------------------------+----------------------------+----------------------------+ | now(6) | sysdate(6) | now(6) | sysdate(6) | +----...


14

SQL Server uses different calculations in different situations. Your example is different from the linked Q & A because your range is entirely contained within a step; it does not cross a step boundary. It is also an interval with two ends rather than one. Writing BETWEEN is the same as writing two separate predicates with >= and <=. Interval with ...


13

A logical read is counted when a single page is retrieved from buffer cache during query execution. This is counted regardless of whether a physical or read-ahead was used to cache the page, or if the page already existed in the buffer cache. Consequentially, logical reads is a measure of how many times pages were actually touched in memory during query ...


11

One way to determine the logical order of joins is to replace the first inner join in your example with a left outer join: SELECT * FROM user_branch T1 LEFT JOIN dimcustomer2 T2 ON T1.BRANCH_CODE = T2.BRANCH_CODE INNER JOIN customer_guarantee T3 ON T3.CUSTOMER_NUM = T2.CUSTOMER_NUM Let us assume that some rows in T1 have no matches in T2. More ...


10

Rowstore The sort spill itself can probably be addressed by enabling trace flag 7470. See FIX: Sort operator spills to tempdb when estimated number of rows and row size are correct. This trace flag corrects an oversight in the calculation. It is quite safe to use, and in my opinion ought to be on by default. The change is protected by a trace flag simply to ...


10

SELECT record_id, rec_date , CASE WHEN rec_date::time < '08:00' THEN 'Night' WHEN rec_date::time >= '20:00' THEN 'Night' ELSE 'Day' END AS indicator FROM records; Why? It's a matter of date-math rather than date-format. You want to do the math correctly and efficiently. The format of 'Day' and 'Night' are not in ...


10

After further research, I have discovered from this StackOverflow post that SQL Server breaks FOR JSON queries into "~2kb chunks". Sql Server splits result of FOR JSON query into ~2KB chunk, so you should either concatenate fragments like on the MSDN page or you can stream results into some output stream. This means that only ~2000 characters can ...


9

The subquery from the selected answer isn't needed. To select products with all the given tag ids the query can be simply: SELECT p.* FROM products AS p INNER JOIN tag_ties AS tt ON tt.ref_id = p.id AND tt.tag_id IN (10, 11, 12) GROUP BY p.id HAVING COUNT(p.id)=3 Extending this idea, we can also query based on the tag ...


9

You can not select aggregates across a field if you don't include the field in the group by list. If you want the totals per year you should write SELECT year,sum(mark),sum(maxmark) FROM table1 GROUP BY year If you want the totals per parameterno it should be SELECT parameterno,sum(mark),sum(maxmark) FROM table1 GROUP BY parameterno From the error ...


9

It is not clear to me what you are asking but I believe that GROUP BY is one of the most misunderstood concepts in SQL, so I'll add this answer anyhow. It may or may not help with the understanding of the concept GROUP BY. Assume we have a table like: CREATE TABLE T ( YEAR INT NOT NULL , PARAMETERNO INT NOT NULL , MARK INT NOT NULL , PRIMARY KEY (YEAR, ...


9

The msdb.dbo.sp_send_dbmail procedure (or other procedures) only accept either a parameter or a literal value. Consider changing the execution of sp_send_dbmail to this: SET @usage_data += ' end of data' exec msdb.dbo.sp_send_dbmail @profile_name='some_mail_profile', @recipients=@admin_list, @subject='Mail from sql server', @body=@usage_data, @importance=...


9

I'm afraid the phrase "logical execution" does not make much sense; query execution by definition is physical materialization of a result set. I think what you mean by "logical execution" is the query compilation, the phase where the query syntax and semantic meaning is analyzed and the query plan is prepared to implement said semantic meaning. Joined ...


8

One way would be to use NOT EXISTS. SELECT * FROM YourTable T1 WHERE Status IN ( 'Closed', 'Rejected', 'Cancelled' ) AND NOT EXISTS (SELECT * FROM YourTable T2 WHERE T2.Id = T1.Id AND T2.Status NOT IN ( 'Closed', 'Rejected', 'Cancelled' )) ; Or an alternative method if ...


8

IMHO the easiest way is by using a lookup table. create table lk (id int, name text); insert into lk values (1, 'apple'),(2, 'google'),(3, 'msft'),(4, 'amazon'); create table t (id serial, lk int); insert into t (lk) values (2),(1),(3),(4); select t.id, lk.name from t join lk on lk.id = t.lk; id | name -: | :----- 2 | apple 1 | ...


8

With the help of @a_horse_with_no_name, I was able to find the error in my thinking. There is a great example in the PostgresSQL docs that really clarified it for me: Insert or update new distributors as appropriate. Assumes a unique index has been defined that constrains values appearing in the did column. Note that the special excluded table is used to ...


8

Permissions to create a table in the current database shouldn't preclude you from creating one you can work with. You can just create a #temp table: CREATE TABLE #test123( c1 float, c2 float, c3 float, c4 float, c5 float ); INSERT #test123(c1,c2,c3,c4,c5); VALUES (2,3,NULL,1,2), (2,NULL,NULL,1,2), (2,3,NULL,NULL,2), ...


8

Does this get you what you want? DECLARE @x XML = ' <SearchJobConfig> <QueryID>1072</QueryID> <QueryString> <SearchCriteria name="Search query" > <ExpressionSet logicalOperator="AND"> <SimpleAttributeExpression displayName="Date" npmPropertyId="4" searchOperation="GREATER_EQUAL" dataType="string" ...


8

You can use grouping set also declare @JobRequiredProducts table (jobId int, productId int); declare @Product table(productId int, productUnitPrice decimal(8,2)) insert into @JobRequiredProducts(jobId,productId) values(1,4),(1,5),(1,6); insert into @Product(productId, productUnitPrice) values(4,175.99),(5 ,100.00),( 6 , 125.00) SELECT j.jobId, ...


7

3 solutions (2 of which are similar to @stickybit's, but easier on the eye) are below. I often find it beneficial to look at answers/threads which have multiple solutions to the problem - some of which are obviously better than others but it can be a learning experience! The simplest and by far the most elegant solution is (thanks to the hint from @...


7

There will always be logical reads. SQL Server never returns data to you directly from disk. If the page you need is already in the buffer pool, there is a logical read. If it’s not, it will be moved into the buffer pool, and then there is also a logical read. You will always get the read coming from cache. Also, I don’t understand the point of your test. ...


7

SHOW ALL The SHOW ALL command displays the current setting of run-time parameters in 3 columns. SHOW ALL ; pg_settings The pg_settings view shows the same items as SHOW ALL but with additional details, across 17 columns versus 3 columns. TABLE pg_settings ; pg_file_settings To read what is stored in the postgresql.conf file itself, use the view ...


7

This is usually called a GROUPING AND WINDOWS solution. Basically you set reset points according some rules, then you set up groups by SUMming reset points and finally use aggregated functions to get the desired value. ;WITH reset AS ( SELECT AuditID, CreditID, TS, Val, /* Next CASE returns 1 when: a) It is the ...


7

You should use a case statement like this: SELECT * FROM test123 WHERE ( (CASE WHEN C1 IS NULL THEN 1 ELSE 0 END + CASE WHEN C2 IS NULL THEN 1 ELSE 0 END + CASE WHEN C3 IS NULL THEN 1 ELSE 0 END + CASE WHEN C4 IS NULL THEN 1 ELSE 0 END + CASE WHEN C5 IS NULL THEN 1 ELSE 0 END) < 2); The ISNULL approach is returning ...


7

This should work for you. At least for the entries in the PowerCurve table that I copied over, it works correctly. This should get you close. See comments in the code for more details. Assistance from https://stackoverflow.com/questions/9873990/round-to-5-or-1-0-in-sql on how to round to nearest .5/0. /** FIRST - we setup the data and tables we need. ...


7

select id from table where field in (gigantic list of ordered longs) Queries with long IN lists are slow, as they are parsed and compiled each call. Either pass the list as a JSON array: select id from table where field in (select value from openjson(@values)) Or use a Table-Valued Parameter or bulk load a temp table with the values. Ensure there is an ...


6

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


6

In SQL Server 2012 or later, I would implement this using sequence objects. For SQL Server 2008 R2, my replacement for that missing feature is Sequence Tables. In this case, there would be a key in the master sequence table for each year, for example: CREATE TABLE dbo.SequenceTable ( sequence_name nvarchar(20) NOT NULL, next_value integer ...


6

Test: SELECT t_min_max.name, t_min.value AS value_1st, CASE WHEN min_id <> max_id THEN t_min.value END AS value_last FROM ( SELECT name, min(id) AS min_id, max(id) AS max_id FROM table GROUP BY name ) AS t_min_max JOIN table AS t_min ON t_min_max.min_id = t_min.id JOIN table AS t_max ON t_min_max.max_id = t_max.id; With complex ...


6

You could try something like this SELECT CASE WHEN RIGHT(fieldname,1) = '"' AND LEFT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(STUFF(fieldname, 1, 1, '')), 1, 1, '')) WHEN RIGHT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(fieldname), 1, 1, '')) WHEN LEFT(fieldname,1) = '"' THEN STUFF(fieldname, 1, 1, '') ELSE fieldname END as ...


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