Database Discussion Boards

Re: How Much Space Is Wasted In Unused Fields In SQL Server?

jschell20-Oct-11 9:05

20-Oct-11 9:05

Roger Wright wrote:
rom a coding standpoint, a single master Transformer table is far easier to manage, and is the approach I'd prefer to follow. But if 90% of the transformers in use have 40% of their data fields set to null, will they still consume the same storage space as they would if all fields contained data?

Say you have 1000 columns for transformers and 900 of them are empty thus can be null.
Also say that a null takes one byte (could take less but one byte is possible.)
The other 100 columns take an average of 1000 bytes which is only 10 bytes per column.

So a record takes 1900 bytes and lets add 100 bytes for overhead (which is way high but makes the math easier.)

So a total of 2000 bytes per record.

TigerDirect (just an example) has an external 2 Terabyte drive for $120 and you need 3 (1 main, 1 failover, 1 backup). So $360

You will be able to store one billion transformers on that drive (one of them.)

Do you have one billion transformers?

And of course if the nulls take no space then you can store even more.
(Note that one design trick which I would suspect might not be relevant but could be is that you put null columns at the end of the table schema. Then it is more likely the database will drop them so no space.)

Roger Wright wrote:
I'll have to manage a dozen or more different tables,

Far as I am concerned you don't have a space problem. You do however have a management problem.

Now if you ONLY need to manage inventory then you should use a meta data design.

The idea in this case is simple. Following tables/columns are examples, variations, are driven by actual requirements. Following are table descriptions.
item: item_id, inventory_number, item_name
property_type: property_type_id, name, description, units
Property: property_id, item_id, property_type_id, the_value

Thus for a pad mounted 24kv transformer the data would look like the following
item: 1, 'tns001', 'big blue transformer'

property_type: 1, 'mount', 'transformer mount', null
property_type: 2, 'volts', 'volts', 'volt'

Property: 1, 1, 1, 'pad'
Property: 2, 1, 2, '24000'

There are many possible variations on the above.

Re: How Much Space Is Wasted In Unused Fields In SQL Server?

Roger Wright20-Oct-11 10:25

Roger Wright

20-Oct-11 10:25

That's really along the path I want to go. I use lookup tables to associate human readable data with numeric identifiers, then code the actual data using the identifiers to conserve space. I also plan to use a few boolean identifiers to control visual elements by turning on and off the displays for controls that are only used for some items. If something new is required, I only have to add a couple of records to the lookup tables.

Will Rogers never met me.

Re: How Much Space Is Wasted In Unused Fields In SQL Server?

jschell21-Oct-11 10:54

jschell

21-Oct-11 10:54

Roger Wright wrote:
visual elements

Text display from the database. Make sure those have their own column.

SQL Query Select

reogeo200817-Oct-11 21:26

reogeo2008

17-Oct-11 21:26

Hi all,

Can anybody help me in the following issue.

I have a course table contains coursename and type

I want to select course name and type and the display should like

Coursename(type)

th problem is my type values are integers..
For example 1 for Regular type ,2 for distance type
so if type is 1 and coursename is MCA
Output should be

MCA(Regular)

is it possiblle thru the sql statement

Thanks in advance,
Reena

Re: SQL Query Select

Blue_Boy17-Oct-11 22:34

Blue_Boy

17-Oct-11 22:34

I guess you have to have linked table for type of course,

Here is example how it should be

select 
case when [type]=1 then 'MCA' when [type]=2 then 'Another Title' end + ' (' +
coursename +')' as TypeAndCourse
from courses

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Re: SQL Query Select

AditSheth17-Oct-11 23:09

AditSheth

17-Oct-11 23:09

Blue_Boy wrote:
select
case when [type]=1 then 'MCA' when [type]=2 then 'Another Title' end + ' (' +
coursename +')' as TypeAndCourse
from courses

just little changes

SQL

SELECT [Coursename] + '(' + case when [type] = 1 THEN 'Regular' when [type]=2 then 'Another Title' end + ')' as TypeAndCourse
from courses

but i am agree with "Mycroft Holmes" suggestion of creating new table..

Where can we go to find God if we cannot see Him in our own hearts and in every living being
-Swami Vivekananda

Re: SQL Query Select

Mycroft Holmes17-Oct-11 22:53

Mycroft Holmes

17-Oct-11 22:53

Create a table of CourseTypes with a PK field and a description.
This is then an extensible solution if you need to add another type.
Create a view with an inner join (assumes the type is required in the course table and that a course can only have 1 type) to the type table and include the course type description field (I name the view vwCourse)

Or just do it the way the Blue one suggested!

Never underestimate the power of human stupidity
RAH

Re: SQL Query Select

Niladri_Biswas26-Oct-11 16:27

Niladri_Biswas

26-Oct-11 16:27

Try this

Declare @Course table ([Course Name] Varchar(100),[Course Type] Int)
Insert Into @Course
	Select 'MCA',1 Union All
	Select 'MCA',2

;With CTE AS(
	Select [Course Type] = 1,[Course Description] = 'Regular' Union All
	Select [Course Type] = 2,[Course Description] = 'Distance'
)

Select 
		Result = 
		c.[Course Name] 
		+ '('
		+ CTE.[Course Description]
		+ ')'
From @Course c
Join CTE ON c.[Course Type] = CTE.[Course Type]

If you are using Denali CTP 3, you can use the new Choose function

Select
	Result = 
			Concat(
					c.[Course Name] 
					,'('
					,CTE.[Course Description]
					,')'
				  )
From @Course c
Join CTE ON c.[Course Type] = CTE.[Course Type]

Output

Result
MCA(Regular)
MCA(Distance)

Niladri Biswas

DB2 Commands

Manivannan Dheena17-Oct-11 19:17

Manivannan Dheena

17-Oct-11 19:17

Hi,
i am new to DB2, i have certain db2 commands like "Create Database", "connect to database".

These commands i am executing in db2 script file say ex: "script.db2" file.
what is the command to quit the command line processor window.

When i open the Command line processor window, and i enterered the "exit" commmand manually it works fine.

But the same i given in my script.db2 file, its not working... what could be the problem..

Please help me to resolve this.

Thanks,
Mani

Re: DB2 Commands

Manivannan Dheena17-Oct-11 23:52

Manivannan Dheena

17-Oct-11 23:52

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0002036.htm[^]

i got solution from this site.

isnull in linq where condition

yesu prakash13-Oct-11 20:02

yesu prakash

13-Oct-11 20:02

Hi,
How to write isnull(exp1,exp2) in the where condition of linq.

eg;

SQL:
... where userpk = isnull(@userpk,userpk)
i need to convert the above query to linq

Thankyou,
Yesuprakash

Re: isnull in linq where condition

Johan Hakkesteegt13-Oct-11 21:37

Johan Hakkesteegt

13-Oct-11 21:37

http://forums.asp.net/t/1329095.aspx/1[^]

My advice is free, and you may get what you paid for.

Re: isnull in linq where condition

Niladri_Biswas26-Oct-11 16:14

Niladri_Biswas

26-Oct-11 16:14

This may help

where 
       !string.IsNullOrEmpty(record.Field<string>("userpk")) 
       ? record.Field<string>("userpk") 
       : null

Niladri Biswas

Data warehouse

coder199113-Oct-11 1:25

coder1991

13-Oct-11 1:25

Hi,
I am BE final year student. I am doing project using data warehouse. It is review analysis of automobiles. Can anybody give me feedback of this, how is the project at final year level?
Also if i get some links regarding review analysis i will e grateful.

Thank you.

Linked Server Query Returning No Records

Frank Lepkowski12-Oct-11 3:26

Frank Lepkowski

12-Oct-11 3:26

I have an x64 Server Running SQL Server 08 and within that server I am setting up a linked server to an XP box running SQL express 05. I am setting up the linked server with a server name of "ComputerName\SQLEXPRESS", using the Microsoft OLE DB Provider for SQL Server. It connects fine and passes a connection test, but when i write a simple select query to get all records from the linked server table it returns no records when there are multiple records in the table I have queried. Any one ever seen this before? It should be noted that when I try to add the linked server and keep the radio button selected for sql server it will not connect giving me an error message

VIA Provider: The specified module could not be found.

OLE DB provider: SQLNCLI10 for linked server returned message login timeout expired.

OLE DB Provider for linked server returned message a network related or instance specific error has occured while establishing.....

I am at a loss? Mad | :mad:

Re: Linked Server Query Returning No Records

Eddy Vluggen12-Oct-11 7:35

Eddy Vluggen

12-Oct-11 7:35

Member 8003276 wrote:
a network related or instance specific error has occured

That's a general message, and there can be many reasons why it can't connect. Try using an IP-address to identify the computer.

Is Sql configured to use TCP/IP or use named pipes?

Did you install all the service-packs?

Bastard Programmer from Hell Suspicious | :suss:

error to mysql

a2bj11-Oct-11 0:16

a2bj

11-Oct-11 0:16

HI error under

error 1045 - access denied for user 'root' '@localhost'(using password : no)

php my admin tried to connect to the MYSQL server and the server rejected the connection .you should the check the host, user name and password in your configuration and make sure that they correspond to the information given by the administrator of the MYSQL server.

Re: error to mysql

phil.o11-Oct-11 0:56

phil.o

11-Oct-11 0:56

What is the question ?

Re: error to mysql

Bernhard Hiller11-Oct-11 20:58

Bernhard Hiller

11-Oct-11 20:58

a2bj wrote:
you should the check the host, user name and password in your configuration and make sure that they correspond to the information given by the administrator of the MYSQL server

Such a good error message! It perfectly tells you what to do.
I cannot believe that some internet provider would allow you to connect to a MySQL database on the server as root, and without a password...

Re: error to mysql

Peter_in_278011-Oct-11 21:09

Peter_in_2780

11-Oct-11 21:09

You can rest easy. It's on localhost, not an ISP. OP just can't remember what he told mysql so he can enter it into phpmyadmin.

:groan:

Peter

Software rusts. Simon Stephenson, ca 1994.

Re: error to mysql

Rakesh Meel16-Oct-11 21:48

Rakesh Meel

16-Oct-11 21:48

Edit config.inc.php this can be found in phpMyAdmin/libraries

change :

$cfg['PmaAbsoluteUri'] = '';

to

$cfg['PmaAbsoluteUri'] = 'localhost/phpmyadmin/';

change

$cfg['Servers'][$i]['host'] = 'localhost';

to

$cfg['Servers'][$i]['host'] = 'Localhost';

and enter you're password in :

$cfg['Servers'][$i]['password'] = '';

eg

$cfg['Servers'][$i]['password'] = 'password';

reboot MySQL

select unique record question

Jassim Rahma10-Oct-11 23:02

Jassim Rahma

10-Oct-11 23:02

Hi,

I am using the following code in sql server to get list of mobile numbers and add the country code to it.

SQL

select ('973' + contact_moblle) AS mobile_number from persons where contact_moblle is not null and contact_moblle != '' and len(contact_moblle) = 8 and left(contact_moblle, 1) = '3' order by file_no

I want to do the same but with selecting unque records only.. something like this (but throwing error!)

SQL

select DISTINCT ('973' + contact_moblle) AS mobile_number from persons where contact_moblle is not null and contact_moblle != '' and len(contact_moblle) = 8 and left(contact_moblle, 1) = '3' order by file_no

Re: select unique record question

PIEBALDconsult11-Oct-11 2:50

PIEBALDconsult

11-Oct-11 2:50

How about a subselect if your database system allows it?

SELECT ('973' + contact_moblle) AS mobile_number FROM (SELECT DISTINCT contact_moblle FROM persons WHERE ... ) T

Also...

"where contact_moblle is not null and contact_moblle != '' and len(contact_moblle) = 8"

can be reduced to:

"where len(contact_moblle) = 8"

Re: select unique record question

Eddy Vluggen11-Oct-11 9:52

Eddy Vluggen

11-Oct-11 9:52

jrahma wrote:
something like this (but throwing error!)

You didn't post an error. I doubt that it'll update the correct field if the dataset is created using distinct values.

Bastard Programmer from Hell Suspicious | :suss:

Re: select unique record question

davidshenba18-Oct-11 17:38

davidshenba

18-Oct-11 17:38

SQL

select ('973' + contact_moblle) AS mobile_number from persons where contact_moblle is not null and contact_moblle != '' and len(contact_moblle) = 8 and left(contact_moblle, 1) = '3' GROUP BY contact_moblle order by file_no

Hope this will help. Check this and let me know the outcome

-Shenbaga Murugan Paramasivapandian
I hate computers and I just mess them up with my code!

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.