Here is a good procedure for resetting any sequence to 0 from Oracle guru Tom Kyte. Great discussion on the pros and cons in the links below too. tkyte@TKYTE901.US.ORACLE.COM> create or replace procedure reset_seq( p_seq_name in varchar2 ) is l_val number; begin execute immediate ‘select ‘ || p_seq_name || ‘.nextval from dual’ INTO l_val; execute … Read more
Those two requires aren’t equivalent. The equivalent version of the first one would be: SELECT sum(numbackends) FROM pg_stat_database; In that case, I would expect that version to be slightly faster than the second one, simply because it has fewer rows to count. But you are not likely going to be able to measure a difference. … Read more
Try this one – Query: SELECT database_name = DB_NAME(database_id) , log_size_mb = CAST(SUM(CASE WHEN type_desc=”LOG” THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , row_size_mb = CAST(SUM(CASE WHEN type_desc=”ROWS” THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) FROM sys.master_files WITH(NOWAIT) WHERE database_id = … Read more
You can JOIN with the same table more than once by giving the joined tables an alias, as in the following example: SELECT airline, flt_no, fairport, tairport, depart, arrive, fare FROM flights INNER JOIN airports from_port ON (from_port.code = flights.fairport) INNER JOIN airports to_port ON (to_port.code = flights.tairport) WHERE from_port.code=”?” OR to_port.code=”?” OR airports.city=’?’ Note … Read more
There are many formats supported by SQL Server – see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have – therefore, these settings might work some times – and sometimes not. The way to solve this is to use the (slightly adapted) ISO-8601 date format … Read more
Can be done via changing the Collation. By default it is case insensitive. Excerpt from the link: SELECT 1 FROM dbo.Customers WHERE CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS Or, change the columns to be case sensitive.
Use the DISTINCT operator in MySQL: SELECT DISTINCT(Date) AS Date FROM buy ORDER BY Date DESC;
This works for Oracle and SQL Server (you might be able to get it to work on another RDBMS): select sum(case when a is null then 1 else 0 end) count_nulls , count(a) count_not_nulls from us; Or: select count(*) – count(a), count(a) from us;
Ad hoc is latin for “for this purpose”. You might call it an “on the fly” query, or a “just so” query. It’s the kind of SQL query you just loosely type out where you need it var newSqlQuery = “SELECT * FROM table WHERE id = ” + myId; …which is an entirely different … Read more
LEAST(a, b): The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions. The expressions must all be convertible to a common data type, which will be the type of the result (see Section 10.5 for details). NULL values in the list are ignored. The result will … Read more