A database concept used to represent missing, unknown or inapplicable data.

learn more… | top users | synonyms

1
vote
4answers
106 views

How can I allow part of a key to be null and enforce uniqueness of records with nulls?

I have an Access table called Engagement with four fields: Emp_id, Year, Week, Act_id It records when an employee is/was engaged in an activity. Each field is Number and each makes up part of the ...
1
vote
1answer
49 views

Distinguish between unknown and non-present foreign keys

I'm in the situation of importing data from multiple other applications. The data is stored in multiple tables that contain foreign keys. Some of these applications don't provide all the needed data ...
-3
votes
2answers
76 views

Case statement by using IsNull and IsNotNull [closed]

ALTER FUNCTION [dbo].[addC]() RETURNS varchar(255) AS BEGIN DECLARE @desc varchar(255); DECLARE @desc1 varchar(255); SET @desc = '' SET @desc1 = '' Declare c1 Cursor for ...
2
votes
3answers
86 views

Full Outer Join shows NULL when Group By is used

I have 4 subqueries and each of the subquery is grouped by 'Group name'. The attempt is to have each subquery as a column all grouped by the 'Group Name'. Here is the query: select coalesce(co....
4
votes
2answers
333 views

How do I order by NULLS LAST if my database doesn't support it?

Select FullName, Login FROM [User] ORDER BY FullName desc, login asc Results FullName Login ----------------------------- ------------------------------ M...... ...
7
votes
2answers
142 views

What are the consequences of not specifying NOT NULL in PostgreSQL for fields which can't be null?

I have an application (data is stored in PostgreSQL), where majority of the fields in the tables are always not null, but the schema for these tables does not enforce this. For example look at this ...
77
votes
7answers
20k views

Why shouldn't we allow NULLs?

I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL. I don't remember why this was the case though. All I can seem to ...
3
votes
2answers
77 views

Enforce uniqueness across optional attributes for an entity

I have an entity that has multiple attributes, and these attributes are themselves entities. No two entities may have the same attributes, so there needs to be a uniqueness constraint across them. ...
0
votes
2answers
32 views

Compare possible null values in one query

Question is pretty short and simple: I have following query: SELECT * FROM CTE Now I want to get all rows with only maximum value of some field. SELECT * FROM CTE WHERE NodeId = (SELECT MAX(...
1
vote
2answers
31 views

MySQL stored procedure returns null

I wrote a simple procedure to modify the whole column to a set of meaningless values. However, when I tried call NewProc('tableName','colName') it returned 1064 - You have an error in your SQL ...
5
votes
2answers
117 views

Need help with null field values

I am having a problem with syntax. I have the following statement which updates a field in a table with a value from a field in a different table. I need the statement to update the field with the ...
52
votes
7answers
13k views

If a person's name is Null then how would it break the database?

I was reading this article on BBC. It tells a story of a person named Jenifer Null and how she faces day to day problems while using online databases like booking plane tickets, net banking etc. I am ...
12
votes
3answers
694 views

Can IS DISTINCT FROM be combined with ANY or ALL somehow?

Is a postgres way of combining IS DISTINCT FROM with ANY or some other neat way of getting the same result? select count(*) from (select 'A' foo union all select 'Z' union all select null) z where ...
1
vote
3answers
184 views

Finding count of null values in 500 columns of a single table

I have 500 columns in a table in SQL Server. I want to find the count of null values in each column. Is there a way to do it using a cursor and dynamic SQL?
43
votes
5answers
31k views

Why does ALTER COLUMN to NOT NULL cause massive log file growth?

I have a table with 64m rows taking 4.3 GB on disk for its data. Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text. I added a a NULLABLE column with data-...
4
votes
2answers
2k views

Are many NULL columns harmful in mysql InnoDB?

As I explored the clustered B-tree index system of InnoDB, I think that the presence of many NULL (or small) columns has no significant effect on InnoDB performance. Does the presence of excess ...
6
votes
1answer
455 views

SQL Server Left Join strange NULL check

I've just been going through some old stored procedures, doing some optimizations and stumbled across this oddity: SELECT ISNULL(bt.BusinessTypeID, 0) AS BusinessTypeID FROM BusinessType bt LEFT JOIN ...
0
votes
2answers
67 views

How to omit multiple columns where is not null?

I've constructed the below query which satisfies the requirements, except output includes NULL values between all "vendor_address2" columns, which isn't intended. I suspect my error is somewhere ...
5
votes
3answers
6k views

Count null and not null values in a column

How to count and retrieve null and not null on same column in MySQL? mytable --------------------------------------------------- id | name | visited | registDate | ------------------...
0
votes
0answers
55 views

Comparisons with NULL values and not using ISNULL function

Hello and thank you in advance. My question is that with ansi-nulls on, how can I do a proper comparison between two fields without using an isnull function (or coalesce)? For example the following ...
0
votes
4answers
4k views

ISNULL() OR Is NULL in UPDATE statement

I have to run an UPDATE statement on live online database with about 5 lac records. I want to know which statement will run faster: Update Table set REC_ID = isnull(REC_ID,'') Update Table set ...
7
votes
4answers
41k views

Getting SELECT to return a constant value even if zero rows match

Consider this select statement: SELECT *, 1 AS query_id FROM players WHERE username='foobar'; It returns the column query_id with value 1 along with a player's other columns. How would ...
4
votes
5answers
899 views

Enforce NOT NULL for set of columns with a CHECK constraint only for new rows

I have a table and need to add a new column without a default value: Constraint: ALTER TABLE integrations.billables DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed_billables, ADD ...
8
votes
2answers
13k views

How do I check for a null or empty table-valued parameter?

I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty. I have seen this StackOverflow post that says that null/empty TVPs should simply be ...
3
votes
2answers
920 views

Using sp_BlitzIndex - Diagnosed as “Index Hoarder: Addicted to nulls”

Context: MS SQL Server 2012 In using Kendra Little's awesome tool, sp_BlitzIndex, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls". I'm not looking for a debate over NULLs and ...
2
votes
4answers
5k views

Null Or Multiple Parameters When Querying

I have a stored procedure that has a parameter that must take a comma delimited varchar list of values that can be turned into a table of values or can be NULL, such as @Values VARCHAR(10) = NULL ...
0
votes
1answer
2k views

How to show NULL results from query?

i'm trying to create a procedure with filters for search. If the parameter is "NULL" the value of parameter will be column value; Here my problem: When my parameter and column value is NULL. I have ...
0
votes
1answer
258 views

Geography union via cursor returning null

I need to combine multiple geography records into a single entity to store in a spatial data table, but I've learned that the only available function in MSSQL2008R2 that accomplishes this task is ...
-1
votes
1answer
1k views

Totals Column - Blank if Zero or NULL

A report currently computes a "totals" column like so: ,CASE WHEN ISNULL(CLM_BREAKFAST_TYPEA.MealsA, 0) + ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0) = 0 THEN '' ...
3
votes
3answers
3k views

Query to find GUID's “not in” null containing subquery returns no results

When I have a query that checks if a column of type uniqueidentifer does not exist in a table that has a null value then I get no results back. If the subquery does not return a null it works fine and ...
3
votes
1answer
74 views

How to get 0 as array_length() result when there are no elements

I have a query like this: select id, array_length(users_who_like_ids,1) as ct from queryables order by 2 desc; But empty arrays (with no elements) sort on top. I'd rather have array_length() return ...
1
vote
1answer
178 views

INSERT don't work with NOT NULL but UPDATE does, why?

I have this table definition: CREATE TABLE `media` ( `id` int(11) NOT NULL AUTO_INCREMENT, `brands_id` int(11) DEFAULT NULL, `media_type` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL, `...
4
votes
2answers
426 views

Adding hstore entries to an uninitialized (NULL) column

I got bitten by this "feature" recently. If your hstore column is uninitialized and you start adding entries to it, they are all silently swallowed without error. Is this expected? create table ...
7
votes
2answers
1k views

Why is a NOT NULL computed column considered nullable in a view?

I have a table: CREATE TABLE [dbo].[Realty]( [Id] [int] IDENTITY(1,1) NOT NULL, [RankingBonus] [int] NOT NULL, [Ranking] AS ([Id]+[RankingBonus]) PERSISTED NOT NULL .... ) And a ...
9
votes
2answers
4k views

How can I help SQL Server recognize my indexed view column is NOT NULL-able?

I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes): CREATE VIEW dbo.balances WITH SCHEMABINDING AS SELECT user_id ...
0
votes
1answer
1k views

MySQL is inserting “” as 0 in decimal fields. How to stop that?

The fields are nullable, so they do allow nulls. Why would MySQL convert "" to 0 instead of NULL? Is there a way to force it to be NULL instead?
5
votes
4answers
3k views

How to exclude NULL values inside CONCAT MySQL?

If I have this - tadd is the Address table: CONCAT(tadd.street_number, ' ', tadd.street_name,', ', tadd.apt_number,', ', tadd.city,', ', tadd....
2
votes
1answer
210 views

Unique Index/Constraint with multiple columns, one column is nullable

My questions is whether it's possible to have unique index comprised of multiple columns where one of the columns may contain NULL. Example: A table named 'regulated_person' has a number of columns ...
3
votes
1answer
131 views

Display non-null and null count using case statement

In total there are 10 values in my table, 5 of which are null. I need to display the count using case statement in Oracle, like output below: Sample Data: +------+ | Eid | +------+ | 1 | | 2 |...
2
votes
1answer
72 views

Why does coalescing make this query quicker?

I have the following query SELECT SQL_NO_CACHE `table1`.* FROM `table1` LEFT JOIN `table2` ON table2.id = table1.table2_id WHERE (`table2`.`date_assigned` >= '2015-06-21') AND ( ...
1
vote
1answer
89 views

Null in Varchar datatype

I am using a SQL Server database. If NULL value is inserted to a VARCHAR datatype column, what will be the memory size used for the column?
66
votes
8answers
8k views

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process: ...
6
votes
3answers
11k views

Test if any fields are NULL

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column. I need something like SELECT * FROM ...
19
votes
5answers
19k views

Why does a UNIQUE constraint allow only one NULL?

Technically, NULL = NULL is False, by that logic no NULL is equal to any NULL and all NULLs are distinct. Shouldnt this imply that all NULLs are unique and a unique index should allow any number of ...
4
votes
1answer
123 views

VARCHAR NULL storage

I read somewhere online and in a Microsoft Press book there is a field declaration attribute that can be used to indicate NULL VARCHAR values should not consume space at the leaf-level instead of ...
1
vote
1answer
29 views

Normalize End Date - Nulls or separate table

I was hoping for some advice on best practice in schema design - as far as End Date values are concerned. In this case it is for a Suppliers table - we are required to store information on the Start ...
2
votes
0answers
42 views

Better performance to have nullable columns or an extra table? [duplicate]

There are many ways to get rid of nulls in tables, and I have heard of various reasons to get rid of them, but can anyone show me some real numbers as to how your database might perform differently ...
1
vote
1answer
93 views

Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?

I experienced very strange problem today with TSQL function. The function has several parameters (int and bit). One of the (int) is set with value NULL. When calling a function with NULL inside the ...
1
vote
0answers
29 views

How to best distinguish between “types” of missing data?

I am working on large tables (> 100 fields each) currently organized in flat-files in csv-format. An Oracle DB is to be created to store all the data and allow for several more or less intricate ...
1
vote
1answer
711 views

LEFT JOIN DEFAULT NULL VALUE

My query can be simplified as this: SELECT a.id, a.name, b.* FROM a LEFT JOIN b ON a.id=b.id Which b is from group concat select (very dynamic column). Sometimes there are 3 columns in b, sometimes ...