Hot answers tagged

96

You can do that in pure SQL. Create a partial unique index in addition to the one you have: CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL; This way you can enter for (a, b, c) in your table: (1, 2, 1) (1, 2, 2) (1, 2, NULL) But none of these a second time. Or use two partial UNIQUE indexes and no complete index (or ...


52

Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with NULLs, and can coerce different behavior at will). That decision dictated that, in this case, NULL = NULL. It wasn't a very smart decision. What they should have done ...


48

When you change a column to NOT NULL, SQL Server has to touch every single page, even if there are no NULL values. Depending on your fill factor this could actually lead to a lot of page splits. Every page that is touched, of course, has to be logged, and I suspect due to the splits that two changes may have to be logged for many pages. Since it's all done ...


42

I have seen database interfaces (e.g. framework libraries) that return 'null' as a string for null columns. I believe there was a flag that would turn this on or off for debugging. This flag allows developers to easily determine if the empty field was a result of a null value or an empty value. This is a bad setting, especially in production, and would ...


32

When carrying out the command ALTER COLUMN ... NOT NULL This seems to be implemented as an Add Column, Update, Drop Column operation. A new row is inserted into sys.sysrscols to represent a new column. The status bit for 128 is set indicating the column does not allow NULLs An update is carried out on every row of the table setting the new columnn value ...


31

Why can't a null be equal to a null for the sake of a join? Just tell Oracle to do that: select * from one t1 join two t2 on coalesce(t1.id, -1) = coalesce(t2.id, -1); (Note that in standard SQL you could use t1.id is not distinct from t2.id to get a null-safe equality operator, but Oracle does not support that) But this will only work if the ...


29

Boolean logic - or Three valued logic IN is shorthand for a series of OR conditions x NOT IN (1, 2, NULL) is the same as NOT (x = 1 OR x = 2 OR x = NULL) ... is the same as x <> 1 AND x <> 2 AND x <> NULL ... is the same as true AND true AND unknown ** ... = unknown ** ... which is almost the same as false in this case as it will not pass ...


27

There is a (closed) connect item requesting support for NULLS LAST in SQL Server. A couple of other ways would be ORDER BY CASE WHEN FullName IS NULL THEN 1 ELSE 0 END, FullName, Login I prefer this as it doesn't rely on hardcoding a string that it is assumed no legitimate data will sort after. I'd rather not have to consider ...


25

There's a good chance that a good chunk of your confusion stems from the journalist's. The article talks about problems using entire application systems, not just databases. Completely reasonable since this is a piece of writing aimed at mass consumption, but technical details are glossed over or misunderstood by the author. Likely a number of these issues ...


25

Aggregate functions ignore null values. So SELECT COUNT(cola) AS thecount FROM tablea is equivalent to SELECT count(*) AS thecount FROM tablea WHERE cola IS NOT NULL; As all of your values are null, count(cola) has to return zero. If you want to count the rows that are null, you need count(*) SELECT cola, count(*) AS theCount FROM tablea ...


24

You can use this approach from Paul White's article Undocumented Query Plans: Equality Comparisons SELECT * FROM A INNER JOIN B ON A.ID = B.ID AND EXISTS(SELECT A.* EXCEPT SELECT B.*)


21

SELECT col1, col2, col3, 1 AS query_id FROM players WHERE username='foobar' union all select null, null, null, 1 where not exists (select 1 from players where username = 'foobar'); Or as an alternative (might be faster as no second subselect is required): with qid (query_id) as ( values (1) ) select p.*, ...


20

I've come across another case where CASE / COALESCE do not short circuit. The following TVF will raise a PK violation if passed 1 as a parameter. CREATE FUNCTION F (@P INT) RETURNS @T TABLE ( C INT PRIMARY KEY) AS BEGIN INSERT INTO @T VALUES (1), (@P) RETURN END If called as follows DECLARE @Number INT = 1 ...


20

I'm afraid that the reason is simply that the rules were set in an adhoc fashion (like quite many other "features" of the ISO SQL standard) at a time when SQL aggregations and their connection with mathematics were less understood than they are now (*). It's just one of the extremely many inconsistencies in the SQL language. They make the language harder ...


19

The article itself includes a link to a Stack Overflow question that demonstrates the problem; it was in a Flex application where the code: currentChild.appendChild("Fred"); would append an element containing the word Fred to an XML document but the code: currentChild.appendChild("null"); would append an empty element, not an element containing the text "...


19

SQL uses ternary (three-valued) logic and comparisons with NULL are tricky, counter intuitive. Regarding the condition: WHERE valid NOT IN ('Green', 'Blue', 'White') it expands to: WHERE valid <> 'Green' AND valid <> 'Blue' AND valid <> 'White' When valid is NULL, all these 3 sub-conditions evaluate to UNKNOWN (neither TRUE nor FALSE). ...


18

This is by design. COUNT(<expression>) counts rows where the <expression> is not null. COUNT(*) counts rows. So, if you want to count rows, use COUNT(*).


17

Realistically, the requirement is crazy. Like all great crazy ideas, however, it is probably based on a nugget of potential reasonableness taken far out of context by people that have no understanding of the underlying rationale. It can be reasonable to design a database schema such that no NULL values are allowed. If you do that, however, you are ...


17

If you want to skip NULL values (but not empty strings), you can use CONCAT_WS() function: CONCAT_WS( ', ', -- Separator CONCAT_WS(' ', tadd.street_number, tadd.street_name), tadd.apt_number, tadd.city, tadd.postal_code, tadd.country ) AS Address From the docs: CONCAT_WS(separator,str1,str2,...) ...


17

The [Ranking] field is showing as "Nullable" due to being a computed column. Yes, it is declared as NOT NULL, but as the MSDN page for Computed Columns states, the database engine can change that determination at query-time: The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most ...


17

You have to distinguish two situations: you compare one COLUMN against NULL, or you compare the whole ROW (RECORD) against NULL. Consider the following query: SELECT id, txt, txt IS NULL AS txt_is_null, NOT txt IS NULL AS not_txt_is_null, txt IS NOT NULL AS txt_is_not_null FROM (VALUES (1::integer, NULL::text) ) ...


16

An extension to @db2's answer with less (read:zero) hand-wrangling: DECLARE @tb nvarchar(512) = N'dbo.[table]'; DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb + ' WHERE 1 = 0'; SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL' FROM sys.columns WHERE [object_id] = OBJECT_ID(@tb); EXEC sys.sp_executesql @sql;


16

This is an interesting finding. Normally, a NULL has no assumed data type, as you can see here: SELECT pg_typeof(NULL); pg_typeof ─────────── unknown This changes when a VALUES table comes into the picture: SELECT pg_typeof(core) FROM ( VALUES (NULL) ) new_values (core); pg_typeof ─────────── text This behaviour is described in the source ...


15

There is No Valid Reason to use a magic value instead of NULL. This might be the thought process of someone creating this mess. They write something like this: SELECT c1, c2 FROM t1 WHERE c3 < 30; When this doesn't return the results they are expecting, they realize that it does not include NULLs and would need to write this: SELECT c1, c2 FROM t1 ...


14

You need to rebuild the clustered index after making the columns sparse. The dropped columns still exist in the data page until you do this as can be seen with a query against sys.system_internals_partition_columns or using DBCC PAGE SET NOCOUNT ON; CREATE TABLE Thing ( ThingId int IDENTITY CONSTRAINT PK PRIMARY KEY, USER_CHAR1 nvarchar(150) null, ...


14

Let me start off by saying I am not a DBA, I am a developer by heart and I maintain and update our databases based on our needs. That being said, I had the same question for a few reasons. Null values make development more difficult and bug prone. Null values make queries, stored procedures, and views more complex and bug prone. Null values take up ...


14

Clarify ON CONFLICT DO UPDATE behavior Consider the manual here: For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified by conflict_target is violated, the alternative conflict_action is taken. Bold emphasis mine. So you do not have to repeat predicates for columns included in ...


13

Apart from all the issues with NULL confusing developers, NULLs have another very serious drawback: Performance NULL'able columns are a disaster from a performance perspective. Consider integers arithmetic as an example. In a sane world without NULL, it is "easy" to vectorise integer arithmetic in the database engine code using SIMD instructions to perform ...


13

A table can't be NULL, nor can a TVP. How do you check if a table is empty? You certainly don't say IF Sales.SalesOrderHeader IS NULL. :-) IF EXISTS (SELECT 1 FROM @tvp) BEGIN -- lots of expensive processing END ELSE BEGIN -- a little bit of cheap processing END ...


13

Looking at it as a grammar problem, ANY is defined as (in Row and Array Comparisons): expression operator ANY (array expression) But is distinct from is not an operator, it's a "construct" as we're told in Comparison Operators: When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM constructs Since PostgreSQL has user-defined ...


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