Isnull function

Question

Isnull function

Viewing 13 posts - 46 through 57 (of 57 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

1) Is there any difference between these two statements ?Both can
insert the data.
2) Which method should i follow,if i face the above situation ?
3) Which one is the optimized or better way to insert?

Also,

How NULL's are treated in memory?i.e How NULL values are get stored in memory or data page ?

for example,

Char(1) hold Null value

char(4) also hold Null Value

char(4) - would store 'Null' + 3 Spaces

char(1) - Would store 'Null' only

Experts input are always welcome!

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Did you read the blog I referred you to?

Specifically look at the "Inside the storage engine" series he's written. It's explained far better than I could, and with diagrams and examples

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 3

karthik M

One Orange Chip

Points: 29139

January 10, 2008 at 2:39 am

#766527

Edit: Ok

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Slot 0 Offset 0x60 Length 15
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x6209C060
00000000: 10000c00 01000000 01000000 0300f9 ...............
UNIQUIFIER = [NULL]
Slot 0 Column 1 Offset 0x4 Length 4
c1 = 1
Slot 0 Column 2 Offset 0x8 Length 4
c2 = 1
Slot 1 Offset 0x6f Length 15
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x6209C06F
00000000: 10000c00 02000000 02000000 0300f9 ...............
UNIQUIFIER = [NULL]
Slot 1 Column 1 Offset 0x4 Length 4
c1 = 2
Slot 1 Column 2 Offset 0x8 Length 4
c2 = 2
DBCC execution completed. If DBCC printed error messages, contact your system administrator

I am not able to understand the above statements.It's too complex to me. Can you explain to me ?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 5

Also, is there any link between handling NULL values and Versioning Tag ?

Kindly clarify me.

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Don't post entire blog articles here. It can be a violation of copyright, especially since it's not attributed. Could you please edit and remove it.

There are a lot of other, earlier posts on other aspects of the storage engin. I would suggest you read the earlier ones first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 7

But my question is,How null values are get stored in the memory ? i am very much confused.:doze:

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

The same way they get stored on disk.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 9

Gila,

what do you mean by same way ? Kindly explain me.

karthik

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 10

Which DBCC command did you execute?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

The in-memory page structure (where rows are stored) is the same as the on-disk structure. That's why I pointed you at the storage engin posts.

Paul goes into a lot of detail on the physical structure of rows, pages and extents.

Jeff: It's a partial output of DBCC Page, copied from Paul Randal's blog

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

I did some thinking on this whole discussion on the drive home.

Karthikeyan, no disrespect intended, but from what I've seen, you're probably better, at least for now, not worrying about the internal disk and memory structures of SQL. There are very few times that it's necessary or useful to have that knowledge.

For now, concentrate on learning the 'visible' parts of SQL. The T-SQL language, dev and admin best practices, etc. Do some investigation on your own into how SQL behaves. Look at the system tables. Read books online.

There's plenty of info available on the internals of SQL. Kalen Delaney's Inside SQL Server books, Paul Randal's blog. Other blogs by members of the SQL dev team, the Guru's guide to SQL Internals. Right now though trying to understand that is like trying to run a hurdles race when you haven't learnt to walk.

I hope I haven't offended you. It's not my intention.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 13

Gila,I Know you never offend me.Because you are my good friend.:)

Gila,Thanks a lot.Really i got good idea about 'NULL' from your basic examples and explanation.Also i would like to say 'Thanks' to Jeff.

Thanks a lot!:)

karthik