need simple explanation pls

[peac3]

I found something in creating table :

create table diary
(
d_id int primary key identity(100,1),
d_prob int(9,11) not null

)

why people put "d_prob" for? just for title?

 

[markros]

Your question is not clear and also int(9,11) doesn't look correctly to me. Not in SQL Server, AFAIK.

Which database we're talking about?

Also, the naming conventions is not good here. I don't think we need confusing d_ prefix (which probably states for Diary).

Also, why the table has only 2 fields? This is kind of strange.

PluralSight Learning Library

 

[peac3]

Hi markros,

Sorry, it is supposed to be numeric(9,11) not int(9,11),
the table contains many rows but I do understand that.

the database is sql 2005.

I was just concerning about the "d_prob", what is it refer to.

d_prob numeric(9,11) not null

 

[gmmastros]

d_prob refers to another column in the table. In this case, it is a number and cannot contain the value NULL.

Also, it cannot be numeric(9,11) because the precision (the 9 in this example) must be greater than or equal to the scale (the 11 in this example).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crowley16]

ignoring the data type and other issues with your statement...

I've seen that sort of naming convention and used it myself at times. Basically, if you have lots of tables with common column or column names, then it can help to distinguish columns in joins.

For example, you join 3 tables, product, customer, supplier. All 3 tables have a column called name, using this naming scheme, you'd have: p_name, c_name, s_name instead of name, name, name.
Also, if you have a product table containing ProductId, when joining with another table, say order, which also has a column named ProductId, then you would need to do: "on product.productId = order.productId". Using this naming scheme, you can use "on p_productId = o_productId" since the column names are unique.

Essentially, people use it to avoid aliases (it doesn't work, you still have to use aliases sometimes...). Personally, I think it's kinda pointless and just more typing than necessary...

--------------------
Procrastinate Now!

 

[SQLBill]

It is just a column name. To find out why that name was chosen, you would have to ask whoever wrote the script.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[peac3]

Sorry guys, It's not the column name. I think I need to give full description of the table then,

CREATE TABLE [dbo].[diary](
	[row_number] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[product] [dbo].[d_description] NULL,
	[risk_valuated_pool_id] [dbo].[d_id] NOT NULL,
	[pool_description] [dbo].[d_description] NULL,
	[ugd] [dbo].[d_probability] NULL,
	[pd] [dbo].[d_probability] NULL,
	[lgd] [dbo].[d_probability] NULL,
	[date] [datetime] NOT NULL,
	[source_system] [dbo].[d_description] NULL,
	[central_tendency] [dbo].[d_yes_no] NULL,
	[upper_bound] [dbo].[d_probability] NULL,
	[lower_bound] [dbo].[d_probability] NULL,
 CONSTRAINT [s_retail_pools_pk] PRIMARY KEY CLUSTERED 
(
	[risk_valuated_pool_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

so if you see in column name "ugd", "pd" and "lgd", it mentioned "d_probability", there is no other column in the table called "d_probability" so why it is there?

Thanks,

 

[gmmastros]

Based on this new information, I suspect this is a user defined data type. This can be verified by running the following query...

Select * From systypes where name = 'd_probability'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should have mentioned....

If the query I gave you returns a row of data, then this is confirmation that it is a user defined data type. If the query I showed returns no rows, then I will be wrong about it being a user defined data type.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

They do look like user defined data types. I was looking at this code long enough last night trying to figure it out - the thought of user defined data types never occurred to me may because I never used them.

PluralSight Learning Library

 

[peac3]

I will check it on monday as today is weekend already and will come back to you guys.

But let's say it's user defined data types, pls can you guys explain why people would use these?

As markros said he never used them and so did I.

cheers,

 

[gmmastros]

I don't use them either, but there is a slight advantage to using them. Let me explain...

When you write queries that join tables together, SQL Server attempts to do this as efficiently as possible. This means indexes are likely used by the query engine. Now, suppose you have 2 tables where the one column in the first table is an integer and the corresponding column in the second table is a varchar (that happens to contain all integers). You would be able to use those columns to join the tables in a query. Since the data types are different, sql will need to convert the data in one of the columns to the data type in the other column. This will slow down the query. By using a user defined data type, you are guaranteed that the data types match.

In your case, you have a data type named d_probability. This is probably defined as a decimal(X,Y). If the precision and scale for your many "probability" columns do not match, and you attempt to join and/or filter on that data type, the queries will run slower.

Basically, user defined data types make it easier to be consistent with your data types, and consistency can lead to better performance.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

BUt there is also a huge disadvantage to using them which is why most of us stay away from them. If you define a user data type and it needs to change later, you can't just change the datatype. YOU have to do some comlex process of identifying and changing the userdefined datatypes to something else temporarily, drop the old datatype, createa a new one with the new definition and then change all the existing fileds that you want to use that data type back to the user defined one.

"NOTHING is more important in a database than integrity." ESquared

 

[Crowley16]

it's easier to maintain consistency by having a big stick and beating any developer who doesn't stick to the house style with it repeatedly than using custom data types.

it can be a lot more fun as well, but that may be just me...

--------------------
Procrastinate Now!