Hi,
This question comes without the wish for any philosofical debates concerning NULL
Normally SQL Server 2005 uses 13 bytes to store a NUMERIC(22,7) value. Does it use the full 13 bytes when the column contains NULL as well? And what about DATETIME and (BIG)INT? Most of the info on storage of NULL values is about varchar/char/nvarchar/varchar...
Thnx, Jeroen.
1) it is in the same filegroup as the rest of the table?
2) it is stored in a filegroup separately from the rest of the table (I think this is allowed)?
If I have a table
CREATE TABLE [dbo].[logg](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Details] [varchar](MAX) NULL)
insert logg (Details) values('')
insert logg (Details) values(null)
Will both statements above access only a single page (as it fits into one page) or does the VARCHAR(MAX) always put its data on a separate page. If so, is the null insert treated differently from the '' insert?
Hi ,This is related to datawarehouse , data mining . We are told that datamining tools such as spss or sas , need a large table which has lotsof columns inside . Based on our project , out final table which willbe used in data mining , has lots of computed values . The finalnumber of columns of the table is 7800!(lots of calculated values)First of all , i dont see the reason of this requirement of the datamining tools. I will be appreciated if someone can clarify this: Whydoes a data mining tool need such a large table?The main issue is if i create table with 7800 columns inside ( in factoracle only allows to create table with 1000 columns) , i believe itcant be queried.My basic calculation shows that the average row size of this tablewill be 160kb. , considering my db_block_size of 16kb. this means 10blocks for 1 row. (The table will have more than 10.000.000 rows) Nomatter how fast my disk subsystem is , i think the queries againstthis table will fail. So what can i do? May be I need a differenttype of storage technique for instance column based storage ( i heardthat sysbase has this feature , dont know the details / purpose..)How can i solve such a problem? The database server really does notmatter , it can be oracle,sqlserver , sybase,informix , etc... I willbe appreciated if someone can help me abut the issue.Kind Regards,hope
View 1 Replies View RelatedDear all,
In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.
During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.
However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.
Hence, I had to create a quite awkward design to enforce the constraints:
1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes)
2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes)
3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState)
4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it.
5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.
Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.
Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.
Thanks in advance for any advice.
How to implement distinct storage tiers on SQL Remote BLOB Storage (RBS)?
I want to use this SQL Feature to move files(images, videos, pdf files) from a database to a distinct database dedicated to RBS. Then I want to have several storage tiers, where objects will be saved and moved according access frequency. Old data will be arquived in cheap storage, but it must be always accessible if needed.
Description:
- 1st and main tier: new and frequently accessed objects stored in high performance storage;
- 2nd tier: automatically move older or less accessed objects to an inexpensive and different storage tier;
- in all cases, all objects must be accessible to all users, but accessing to archived objects(2nd tier) will be much slower;
I have two columns A (which allows nulls) and B( which does not allow nulls).
How can I add the contents of columns A and B SO THAT I DO NOT GET A NULL RESULT WHEN A IS NULL.
The result of A+B concatanation will be stored in a column, C.
Appreciate your help
Ziggy
I am a Windows developer for the IBM Tivoli Storage Manager Server (TSMS) product.
Our product installation is built with InstallShield and uses the Windows Installer.
On a new installation of Windows 2003 x64 Storage Server R2, at a customer's site, the TSMS product fails to install.
The install of the OS has version 3.01.400.3959 of the Windows Installer and I see no newer version that installs.
Part of our product is 32 bit (console) and another part is x64 (server).
When installing I can see that the install's default is being redirected/reset to C:Program Files (x86)TivoliTSM after it is explicitly set by a custom action to ..Program Files.. . I further observe that our custom actions to write 64 bit registry entries are being refused.
REGSAM samMask = KEY_ALL_ACCESS;
if ( regIsWow64Process () ) samMask = samMask | KEY_WOW64_64KEY;
lStatus = RegCreateKeyEx( hLocalConnectKeyRoot,
szSubkey,
0L,
NULL,
REG_OPTION_NON_VOLATILE,
samMask,
NULL,
hKey,
&dw ) ;
The above fails to create the key.
We have tried four versions of our TSMS spanning many changes but the install acts the same.
This does not happen on any other Windows OS we test on but we do not test on Windows 2003 Storage Server R2 being that it is an OEM product. We did test on Windows server 2003 R2 x64 and do not see this problem.
Do you have any suggestions on how to tackle this problem?
I have full installation traces but can only see that the registry work is being refused. I can't see why.
ColumnA has existing data in it some of the data are Nulls
what would be the best way to have new as well as existing
data change to 0, using a constraint?
hi, I have a problem with a flat file and a table
I have a flat file with 5 columns and in the table I have 30 columns and they all are (not null)
I cannot leave other columns in target for which it throws mistake, have to insert something, since as I insert information in the demas columns???
Since I can solve this problem???
helps please
If I had a basic table with 3 VARCHAR fields; let's say A, B, C
How could I write a query that returns the count of the number of NULL columns for every record in a table?
Ideally, it would be something like:
SELECT
CAST (A IS NULL) AS INTEGER
+ CAST (B IS NULL) AS INTEGER
+ CAST (C IS NULL) AS INTEGER
FROM MyTable
That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?
Content table
deleteddate revokeddate
1/1/2001 null
null 2/2/2003
4/5/2004 null
I am trying to create a stored procedure where.I need to loop through every row of the table and check if there exists a date in either of the column. If date exists in either of the column for every row, I need to update some other stuff.For the above table, it meets my requirement, so I need to update. However if the table is as below, it doesn't meet the requirement, so I don't need to update
deleteddate revokeddate
1/1/2001 null
null null
4/5/2004 null
I started off as below, But I am getting incorrect values.
Declare @deleterevoke bit
Declare @DelRevId int
Declare @DelRevnumrows int
Declare @tempDeletedDate datetime
Declare @tempRevokedDate datetime
[code]....
johnny writes "I am trying to return only the columns from multiple tables that are NOT NULL for a specific ID. Once I have the tables JOINED is there a way to only get those columns that are populated. Thanks."
View 2 Replies View RelatedThis procedure doesnt increment the "Views" column like it should when the value is NULL (not zero)
CREATE procedure [dbo].[sp_theDb_image_addView]
@imageId int
AS
SELECT [views],
CASE WHEN [views] is null then 1
END
FROM theDb_image WHERE imageId=@imageI
UPDATE theDb_image SET [views] = [views] + 1 WHERE imageId=@imageI
The CASE statement apparently isn't working.... any advice?
Thanks.
Using SQL2000. I want to return the # of columns with non-nullvalues. Here's my query so far:selectcase when Dx1 is not null then 0 else 1 end +case when Dx2 is not null then 0 else 1 end +case when Dx3 is not null then 0 else 1 end +case when Dx4 is not null then 0 else 1 end as DxCountfrom tblClerkshipDataCleanwhere PalmName = @PalmNameThere are 7 rows for the particular PalmName I'm using. The queryreturns a result of 7. However, there are 25 values in Dx1 thru Dx4so the query should be returning 25.What am I doing wrong here? Thanks in advance.
View 4 Replies View RelatedDoes anyone have a suggestion for how to efficiently determine what columns in a table are NULL for all rows? - Shean
View 5 Replies View RelatedI want to run a query that selects rows from the table where a datetime column has null values;
select * from Orders where IsNull(dClosedDate,'Null') = 'Null'
However i get this error:
Conversion failed when converting datetime from character string.
Any help appreciated
Ive created a Data Access Layer (xsd file) and some of the tables have DateTime columns. I need to allow NULL values to be saved, but when I try to do that I get an error
In my code (c#) ive used Nullable DateTime, but when I try to assign a nullable Datetime to a DateTime column, I get this error
cannot implicitly convert type 'System.DateTime?' to 'System.DateTime'
I understand the reasons why, so how do I save null DateTime values to the underlying database through a schema ?
In the schema file, the DateTime columns have <DBNULL> as the default value, AllowBDNull is true, but I am only allowed 'Throw exception' on the NULL value property
I could probably do this another way (by not using a schema and writing the code myself) but Id prefer to keep the strongly typed code.
I dont want to use MAx or MIn values for the default datetime values, so how can this be done ?
How to find out that there is a null value in a column rather than a valid integer, DateTime or bool value, for strings I use 'as' operator to cast the column value and it returns null when column value is null, but for value types using 'as' operator causes compile error and using simple casting causes runtime error, for example:
int count = (int)row["Count"];
and
int count = row["Count"] as int;
the first one throws an exception when Count is null and the second doesn't compile at all since 'as' applies to reference types, so what is the way other than exception handling to determine null value in a column?
Hello,
Is then any type of statement I could run to drop all the columns of a table that have a NULL value thoughout or must this be done manually?
Thanks,
Bryan
I am trying to divide these two columns...
parsename('$'+ Convert(varchar,Convert(money,Q.[Product Cost]-R.[Shared Cost]),1),2) as [Pre Cost],
parsename('$'+ Convert(varchar,Convert(money,R.[New Product Cost]-R.[Shared Cost]),1),2) as [Post Cost]
Formula:
[Post Cost] / [Pre Cost]
Expected:
If [Post Cost] is null then I would need to assign 1 and divide by [Pre Cost] and similarly if [Pre Cost] is null then I would need to assign 1 so that I can divide [Post Cost] / [Pre Cost]
Table DDL below:The tables I have contain Timesheet information. Each row in thetblTSCollected table contains an entry for an employee into thetimesheet system, specifically by scanning the barcode on their badge.A whole bunch of business logic periodically attempts to "pair" theseinto logically matched scans. For example, some employees will scan inand out of a single place of work. For these there will be a rowwritten to the tblTSRuleApplied table which contains, inter alia andsome redundant data, the fldCollectedID for the two rows. The earlierwill be put into the fldStartTimeCollectedID, and the later into thefldEndTimeCollectedID. Some employees will clock on at their base,then perform sub-duties at different locations during the day, andclock off at their home base at the end of their shift. For these, thesystem would identify the outer records as a matching pair, and thenpair up inner records by location.However, if the employee fails to enter a valid "clocking in and out"pair (for example, if they clock in at the wrong location) the systemneeds to generate a "dummy" "clocking in and out" record for thepayroll department. Ideally, this would have NULL values in thefldStartTimeCollectedID and fldEndTimeCollectedID columns. This wouldalert a user in a different part of the system, where missingtimesheets were being arbitrated, that an employee appeared to havefailed to clock in for that day. Of course, the user could seeon-screen that they had clocked in, but at an incorrect location.Unfortunately, the database designer is not here for the moment (he wasknocked off his bicycle recently), but he put a unique index on thetblTSRuleApplied table that prevents the same value being entered intothe fldStartTimeCollectedID and fldEndTimeCollectedID columns. This isgenerally A Good Thing, since we don't want the same timesheet scan toform both a "clocking on" event and a "clocking off" event.So, is there any way of retaining the requirement that thefldStartTimeCollectedID and the fldEndTimeCollectedID columns may notcontain the same value in a single row, UNLESS that value is NULL inwhich case all is hunky dory. I should add that the clients don't muchcare for Triggers (and neither do I for that matter).Many thanks if you are able to help.Edwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINTFK_tblTSRuleApplied_tblTSCollectedGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected1]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINTFK_tblTSRuleApplied_tblTSCollected1GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSArbAccept_tblTSRuleApplied]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINTFK_tblTSArbAccept_tblTSRuleAppliedGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSCollected_tblTSRuleApplied]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINTFK_tblTSCollected_tblTSRuleAppliedGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSCollected]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSRuleApplied]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSRuleApplied]GOCREATE TABLE [dbo].[tblTSCollected] ([fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldTimeStamp] [datetime] NULL ,[fldRuleAppliedID] [int] NULL ,[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fldProcessed] [smallint] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblTSRuleApplied] ([fldEmpRuleID] [int] NOT NULL ,[fldRuleAppliedID] [int] IDENTITY (1, 1) NOT NULL ,[fldStartTime] [datetime] NULL ,[fldEndTime] [datetime] NULL ,[fldStartTimeCollectedID] [int] NULL ,[fldEndTimeCollectedID] [int] NULL ,[fldStartArbStatus] [smallint] NULL ,[fldEndArbStatus] [smallint] NULL ,[fldDurationArbStatus] [smallint] NULL ,[fldPrimary] [smallint] NOT NULL ,[fldDateEntered] [datetime] NULL ,[fldEnteredBy] [int] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADDCONSTRAINT [DF_tblTSCollected_fldProcessed] DEFAULT (0) FOR[fldProcessed],CONSTRAINT [PK_tblTimesheetCollected] PRIMARY KEY CLUSTERED([fldCollectedID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblTSRuleApplied] WITH NOCHECK ADDCONSTRAINT [DF_tblTSRuleApplied_fldPrimary] DEFAULT (1) FOR[fldPrimary],CONSTRAINT [PK_tblTSRuleApplied] PRIMARY KEY CLUSTERED([fldRuleAppliedID]) WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [IX_tblTSRuleApplied_1] UNIQUE NONCLUSTERED([fldStartTimeCollectedID],[fldEndTimeCollectedID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblTSCollected] ADDCONSTRAINT [FK_tblTSCollected_tblEmployee1] FOREIGN KEY([fldEmployeeID]) REFERENCES [dbo].[tblEmployee] ([fldEmployeeID]),CONSTRAINT [FK_tblTSCollected_tblLocation] FOREIGN KEY([fldLocationCode]) REFERENCES [dbo].[tblLocation] ([fldLocationCode]),CONSTRAINT [FK_tblTSCollected_tblTSRuleApplied] FOREIGN KEY([fldRuleAppliedID]) REFERENCES [dbo].[tblTSRuleApplied] ([fldRuleAppliedID])GOALTER TABLE [dbo].[tblTSRuleApplied] ADDCONSTRAINT [FK_tblTSRuleApplied_tblTSCollected] FOREIGN KEY([fldStartTimeCollectedID]) REFERENCES [dbo].[tblTSCollected] ([fldCollectedID]),CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected1] FOREIGN KEY([fldEndTimeCollectedID]) REFERENCES [dbo].[tblTSCollected] ([fldCollectedID]),CONSTRAINT [FK_tblTSRuleApplied_tblTSDurationStatus] FOREIGN KEY([fldDurationArbStatus]) REFERENCES [dbo].[tblTSDurationStatus] ([fldStatus]),CONSTRAINT [FK_tblTSRuleApplied_tblTSEmpRules] FOREIGN KEY([fldEmpRuleID]) REFERENCES [dbo].[tblTSEmpRules] ([fldEmpRuleID]),CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus] FOREIGN KEY([fldStartArbStatus]) REFERENCES [dbo].[tblTSTimeStatus] ([fldStatus]),CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus1] FOREIGN KEY([fldEndArbStatus]) REFERENCES [dbo].[tblTSTimeStatus] ([fldStatus])GO
View 7 Replies View RelatedHi,
I would like to ask how can I get count of null values in each column together?
I know I can use COUNT(*) - COUNT(<col>), but I need to explicitly name every column in the query.... is it possible without it?
Hello guys I have a very simple query that is to be used to verify what users have access to what servers. Here it is:
/* SQL Server & Windows NT login name, security access */
USE master
go
SELECT name, loginname, sysadmin, securityadmin, serveradmin, setupadmin, @@SERVERNAME
FROM syslogins
WHERE status != 0
AND ((sysadmin = 1) OR (securityadmin =1) OR (serveradmin =1) OR (setupadmin = 1))
I would like to know how to stop the columns that have a null or 0 value from being returned to me. I don't want to see a bunch of 0s since almost no users do have these privaleges, I'd rather have that be hidden. Do I need to create a view for this? Thank You!
Hi All.
I'm having some issues with what seems to be a simple update statement but is giving me grief when one or both of the columns I'm comparing are null. My statement (simplified) is as follows:-
UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
TAB_B.TRADCODE <> TAB_A.TRADCODE
)
If either of the TRADCODE fields (or both) are null then the comparison fails to return the row to update. I've tried setting the ANSI_NULLS setting to off, this has no effect, presumably because it will only work when comparing a column to a variable or evaluating if the column is null itself.
I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.
I'd be grateful for any pointers!
Thanks in advance
In a query in SQL server 2005 I have a column LastName + ', ' + FirstName.All works fine except when a LastName or FirstName = NULL then the result is NULL.How do I get around this?ThanksA Newbie to SQL.
View 1 Replies View RelatedHi,
I need help to build a query that shows me how many columns inside a range on columns are null.
Example: quantity1;quantity2;quantity3;quantity4;quantity5; quantity6;quantity7;
Which columns are null?
Thanks in advance
Hi, I need to build a query that just includes certain columns if they haven't NULL row values.For example:SELECT NAME_COLUMN IF not NULL, SELECT GENDER_COLUMN if not NULL, otherwise won't select these 2 columns i used as example.Any help, will be greatly appreciated,Best regards,Teixeira
View 3 Replies View RelatedIf i have a table with Col1,Col2,Col4, and Col5, how can I create and add a Col3 with null values? The format would be varchar.
View 13 Replies View RelatedI have a table with 4 column in below
Total amount = 1000
salemancode1 = space
salemancode2 = Staff-99
salemancode3 = space
salemancode4 = staff-88
How I can write a one query statement to do this, we expect to count how many salemancode is not space and count the number of salesman to over the total amount.
total amount / (no_of_saleman) as commission
the result is 1000/ 2 the commission is $500.
Christine the Pharmacist writes "Please pardon as I am not horribly advanced with SQL...
Using SQL Server 2000, Windows 2000, Service pack 4
I'm trying to figure out marketshares for "preferred" products. I have a table put together by hospital and month of medication use with the totals of each drug dispensed in columns as well.
(example table below)
Hospital Month Drug A Drug B Drug C Drug D
A 7 5 10 58 73
B 7 NULL 26 98 43
etc.
** Drug C and Drug D are the preferred drugs
I'm getting correct preferred % in all cases except when a value is NULL (result is NULL). I'm using an aggregate (sum), which should ignore NULLs, but it ignores NULLs within a column, and not ignoring across columns. I've tried setting concat_null_yields_null off and I'm still getting NULL (since I'm using the plus sign).
This is my admittedly confused select statement, suspect this is where my problem lies (problems with saying "sum", but using "+" as well?):
select a.Hospital,a.month,convert(float,sum(c.Drug_C+d.Drug_D))/convert(float,sum(a.Drug_A+b.Drug_B+
c.Drug_C+d.Drug_D)) as preferred_share
Thanks for any advice!"
Hi,I have some tables where I import data in, lots of field have gotten aNULL value which the application can not handle.Now can I replace each NULL value with '' in a columns with:update <tableset [<column>] = '' where [<column>] IS NULLBut because there are lots of columns this is pretty much work, alsothere are multiple tables.Is there an easy way to replace all NULL values in all columns in atable?Thanks in AdvanceBob
View 2 Replies View RelatedHello everyone,
I have a little problem here. I need to select data from multiple columns and multiple tables, some of those columns can have NULL value. I have tried few things, and havent find the right way so far. Problem that i am incountering is that when i run SELECT statment as it is right now, i dont get back results which match search parameters but have NULL value in one or more column in select statment. How can i fix this problem?
SELECT Person2role.person2roleID,Person.firstname, Person.lastname, Person.sex
FROM Person LEFT OUTER JOIN
Person2Role ON Person.personID = Person2Role.personID
WHERE (Person.firstname LIKE '%' + ISNULL(@firstname + '%', ''))
AND (Person.lastname LIKE '%' + ISNULL(@lastname + '%', ''))
AND (Person.sex = ISNULL(@sex, Person.sex))
This is a part of SELECT statment..just so you can get the idea of what i am doing..i have few more parameters
I am working with SQL Server 2005 Express Version
Thank you,
Alex