Tag Info

Hot answers tagged

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


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


6

It is possible to set up a method to grant rights to run a job that a user does not have enough authority to run on its own. Erland Sommarskog has written a lot on how to grant permissions through stored procedures using counter-signatures. He has a solution at: http://www.sommarskog.se/grantperm.html#countersignatures The key point is: "To be able to ...


5

It's a question of timing. Consider deleting StudentID #1: The row is deleted from the Student table The cascade delete removes corresponding rows from Enrollment The foreign key relationship GPA -> Student is checked The trigger fires, calling MergeGPA At this point, MergeGPA checks to see if there is an entry for Student #1 in the GPA table. There is ...


4

ORDER BY is not that expensive by itself. The problem here is that it is used together with OFFSET. So postgresql does sorting of all data before it can take needed 10 rows. You are probably implementing paging using ORDER BY + OFFSET. This is a known postgresql limitation and there is postgresql way to handle this.


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


3

The following two queries give index usage stats per table which should be a good indicator of which tables are causing the most IO operations. SELECT d.name as [Database], OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM ...


3

You can do a join on Value <> Value for the settings you are looking for. select S1.Settings as Settings1, S1.Value as Value1, S2.Settings as Settings2, S2.Value as Value2 from Settings as S1 inner join Settings as S2 on S1.Value <> S2.Value and S1.Settings = 'clientvisits' and S2.Settings = ...


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


3

Most likely you are not putting necessary quotes around your code. Try something like this. sqlCommandString1 = "Select Correction FROM Wlookup " + "WHERE Citystate = '" + ComboBox1.Text + "'" Otherwise it is interpreted as SELECT Correction FROM Wlookup WHERE Citystate = Washington as opposed to: SELECT Correction FROM Wlookup WHERE Citystate = ...


3

It sounds like you are looking for a FULL [OUTER] JOIN. Per documentation: FULL OUTER JOIN First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row ...


3

Your syntax for using the database link is incorrect, you've got the link and table identifiers in the wrong order. It should be: select ... from table_name@db.link.name Having too many . in a table name results in an ORA-00933 error, like you're getting.


2

You've done a lot with one night's experience with MERGE statements! The DELETED column prefix of the OUTPUT clause should contain the values updated in the MERGE UPDATE SET statements, I think you're on the right track with Attempt 2.


2

Updates are tracked by MERGE by providing: the $Action value UPDATE; the old values in the memory table deleted; and the new values in the memory table inserted. Therefore both in the case where a match is found, and in the case where there is no match by target, the values of the identity column is available in the inserted memory table.


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


2

Clearly you need a "Competitor" table if you wish to represent them, which I assume you do! Competitor Name, Age, Sex, Contact etc. Personally I would create a new table called "Discipline Type" which would differ from "Race Type". The two are functionally independent - race type could be "Triathlon" while "Discipline Type" could be ...


2

Simple, but "dirty": with t as ( select 1 as id, 'Y' as received from dual union select 1 as id, 'N' as received from dual union select 2 as id, 'N' as received from dual union select 2 as id, 'N' as received from dual union select 2 as id, 'Y' as received from dual union select 3 as id, 'N' as received from dual union select 3 as id, 'Y' as ...


2

Although you limited the memory to 28 GB out of 32 GB, this is not the only memory that SQL Server uses. The running programs and needs within Windows take some additional space. If you want to closely tune the memory use, I recommend reading this, which works well for my servers. See Jonathan Kehayias at: ...


2

If you want to keep the existing rows and don't want the table renamed but only a few columns altered/changed/dropped, you can do this with a single ALTER TABLE statement. This will only fail if there is no index on (id): ALTER TABLE table_1 CHANGE COLUMN id id_table_1 int NOT NULL AUTO_INCREMENT, -- ADD PRIMARY KEY -- (id_table_1), CHANGE ...


2

I would like to post this as answer what is point in upgrading to SQL server 2000 which is outdated completely without any support. Even it is not good for testing purpose. Its like using typewriter in era or super computers. Dont uninstall personal edition first. Install enterprise move databses by backup restore method. If you restore master msdb ...


2

Yes, shouldn't be too difficult, try: SELECT a.ID, a.ID_Fruit,,, so on b.ID, b.ID_Fruit, b.ID_Crate, b.[DateTime] FROM TABLEA a INNER JOIN TABLEB b ON a.ID_Fruit = b.ID_Fruit INNER JOIN (SELECT ID_Fruit, MAX([DateTime]) FROM TABLEB GROUP BY ID_FRUIT) maxtime ON b.[DateTime] = maxtime.[DateTime] Its not quite clear if you mean that it should be the ...


2

Instead of using so many JOINs to get the result, you might get better performance pivoting the data using an aggregate function with a CASE expression. Oracle 10g doesn't have a PIVOT function so you'd have to use this type of query (aggregate/CASE) if you aren't going to use multiple JOINs on your table. Since you have a limited number of questions ...


1

select b.*, s.service_name from branches b, branch_services bs, services s where b.branch_id=bs.branch_id and bs.service_id=s.service_id


1

Let's say the table is called mydb.mytable This query SELECT VAR1,VAR2 FROM mydb.mytable where var3=X AND var4=Y order by var5 desc limit 1; can dramatically be improved if you index the table. There is one of two techniques you can try when indexing the table Technique #1 ALTER TABLE mydb.mytable ADD INDEX search_index (var3,var4,var5); Within the ...


1

You could test space-reduction using something like: USE tempdb; CREATE TABLE xc ( x INT NULL ); INSERT INTO xc (x) VALUES (CASE WHEN (RAND() * 10) < 5 THEN NULL ELSE (RAND() * 10) END); GO 10000 SELECT o.name, SUM(au.used_pages) * 8192 / 1048576e0 FROM sys.allocation_units au INNER JOIN sys.partitions p ON au.container_id = p.hobt_id INNER ...


1

In the where clause, trade the Ic alias with IFNULL(c.new_ic,c.old_ic). SELECT `c`.`fullname` AS `fullname`, IFNULL(`c`.`new_ic`,`c`.`old_ic`) AS `Ic`, `bla`.`loan_no` AS `Loan_No`, `bla`.`total_loan_amt` AS `Total_Loan_Amt`, `bla`.`monthly_installment` AS `Month_Inst_Amt`, `bla`.`disb_date` AS `disb_date` FROM (`customers` ...


1

If I understand your logic correctly, a two-level aggregation should do what you need. You don't need PL/SQL or client-side processing (i.e.: .NET). SELECT MIN(Received) Received FROM ( SELECT ID, MAX(Received) Received FROM your_view GROUP BY ID ); http://sqlfiddle.com/#!4/b2f85/3/0 Or @Balazs style is even cleaner, two-level aggregation without the ...


1

It seems you want to keep you PHP code simple and make the SQL code more complicated, while it should be otherwise. Example: lets say that - user A chooses: "color = 'red', size = 'Medium', category = any" - user B chooses "color = any, size = any, category = 'Tshirts'" The conditions could be respectively as simple as: -- userA WHERE color = $color AND ...


1

You want something like this (see below for DDL and DML). This sort of query would be easier with the likes of EXCEPT and MINUS, but we'll have to wait for those... -- first query gets price difference between tables SELECT p1.* FROM product1 p1, product2 p2 WHERE p1.pname = p2.pname AND p1.price <> p2.price -- second query finds extra products in ...


1

In SQL (except in MySQL) you can only select columns that are part of the GROUP BY clause, or aggregate functions on any of the other columns. So you have two options: Add the additional columns to the GROUP BY clause: GROUP BY Rls.RoleName, Pro.[FirstName], Pro.[LastName] Add some aggregate function on the relevant columns: SELECT Rls.RoleName, ...



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