Avoid Null columns in Table - Rick Sawtell

That depends on what your business requirements are.

The simplest is in the table definition itself.

CREATE TABLE Foo (
ColumnName varchar(50) NOT NULL

)



Rick Sawtell

Avoid Null columns in Table - Plamen Ratchev

Not exactly sure what you mean, but a few ideas:

- define the columns as NOT NULL when creating the table
- set DEFAULT values when creating the table and then when inserting data
skip the column if NULL or use the DEFAULT keyword
- use COALESCE when querying the data to set the NULLs to something else,
like a blank string, for example COALESCE(dest, '')

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Avoid Null columns in Table - Plamen Ratchev

What is the condition that you want? Where is this variable @SAMPLING_ID
declared and what data type? What is 'numeric' there?

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Avoid Null columns in Table - Plamen Ratchev

The way I understand this you want to limit the generated SQL only to the
columns that do not have NULL values. The catch with this is you have to
execute a query against each column first and then based on the results to
generate the script. I am not sure it is worth the trouble trying to do it.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Avoid Null columns in Table - rajdaksh

Hi Guys

How should avoid null columns in a table.

Ex
I have Table called Test

Emp_code          Name             DOB
place              dest       add1      add2               DOJ
111                    yesvanth       18-may-1984
null               null         null         null              26-
nov-2008

I need like this

Emp_code          Name            DOB                 DOJ
111                    yesvanth       18-may-1984      26-nov-2008


Please Let me know

thanks
Raj

Avoid Null columns in Table - rajdaksh

Hi i need the query while retriving the data
please give me

i wrote the function but i can't add one more condition in dynamic sql


DECLARE @table sysname
SET @table = 'CLM_FIELD_DEFINATION'

DECLARE @id_field sysname
SET @id_field = 'ID'

DECLARE @sql varchar(8000)

--create the schema of the resulting table
SET @sql = 'SELECT  '+'CAST(0 AS nvarchar(4000)) AS [Property],'
+' CONVERT(sql_variant,N'''') AS [Value]
WHERE 1=0 '+CHAR(10)


SELECT @sql = @sql + 'UNION ALL SELECT '+' N'''
+COLUMN_NAME+''',CONVERT(sql_variant, '+'['+COLUMN_NAME+'])
FROM ['+@table+'] WHERE ['+COLUMN_NAME+'] IS NOT NULL '
+CHAR(10)
+ ' AND FK_SAMPLING_ID = '+@SAMPLING_ID+numeric
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = @table
AND COLUMN_NAME <> @id_field
ORDER BY COLUMN_NAME


problem area is "AND FK_SAMPLING_ID = '+@SAMPLING_ID+numeric"

thanks
raj

Avoid Null columns in Table - rajdaksh

Like this how i will reduce the below query

SELECT CONVERT(sql_variant, VARCHAR1)  AS VARCHAR1 FROM
CLM_FIELD_DEFINATION WHERE VARCHAR1 IS NOT NULL AND
FK_SAMPLING_ID=@SAMPLING_ID
UNION ALL
SELECT CONVERT(sql_variant, VARCHAR2)  AS VARCHAR2 FROM
CLM_FIELD_DEFINATION WHERE VARCHAR2 IS NOT NULL AND
FK_SAMPLING_ID=@SAMPLING_ID
UNION ALL
SELECT CONVERT(sql_variant, VARCHAR3)  AS VARCHAR3 FROM
CLM_FIELD_DEFINATION WHERE VARCHAR3 IS NOT NULL AND
FK_SAMPLING_ID=@SAMPLING_ID
UNION ALL