Number Of Columns In Table And Performance

Dec 28, 2007

Hi,

I have a denormalized table (done so with reason) with around 40 columns. I would never have to retrieve data for all of those columns together.

I haven't done any performance measurements yet but just wondering if anyone has ready answer to this: Will there be a performance degradation if I retrieve data from a table with many columns, even if not all columns are referred in the query? (for making it simple, lets assume that all or varchar type of columns, I just want to find out if performance degrades if there are too many columns in table)

Thanks in advance,

Sandeep

View 1 Replies

Number Of Columns And Performance

Apr 25, 2007

Hi,I'm designing a new database and I have a doubt in which surely youcan help me.I'm storing in this database historical data of some measurements andthe system in constantly growing, new measurements are added everyday.So, I have to set some extra columns in advance, so space is availablewhenever is needed and the client doesn't have to modify the structurein SQL server.The question is: the more columns I add "just in case", the slower theSQL reads the table?Of course the "empty" columns are not included in any query until theyhave some valid data inside.Will I have better performance if I configure only the columns beingused at the moment, without any empty columns?Thanks in advance.Ignacio

View 2 Replies View Related

How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time

Sep 24, 2007

Hi

I want to enter rows into a table having more number of columns

For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View 1 Replies View Related

Number Of Columns In Table

Jan 11, 2006

How can I determine the number of columns in a table?

View 3 Replies View Related

SQL 2012 :: Performance Limit On Number Of Indexes Per Table / Database

Oct 1, 2014

Is there a performance limit on the number of indexes per table / database ? With Filtered indexes there appear to be many more opportunities for more finely defined, and therefore smaller indexes resulting in many more indexes on a single table.

View 4 Replies View Related

Max Number Of Columns In SQlServer Table

Jul 16, 2001

We are looking at developing a new app with a SQL Server db. As such, we're all newbies.

What is the maximum number of columns I can put in a SQL Server table?

Thanks in advance.

Bob Anderson

View 5 Replies View Related

Limits On The Number Of Columns In A Table?

Apr 7, 2008

I have a database hosted by GoDaddy. Recently they made some changes to the interface and upgraded to SQL Server 2008. One or the other has made it impossible to access my data in one table.

The table is quite large in terms of the numbers of elements. Each row describes a dog and all the elements are components of the description. There are (I would guess) more than 50 elements all together.

When I try to search the database, the query form goes beyond the top and bottom of the page. I can scroll the database but the search tool (which lies atop the data) does not scroll. The result is that I can't activate the search.

I've tried about 10 machines. All with IE6 display this fault. Machines with IE7 do not. I've tried various screen resolutions on the machines with IE6. That doesn't help.

I've checked other tables in the database. No problem.

In short, there's nothing I can do. I can't edit my data and GoDaddy says, "Tough."

Is there a limit on the number of columns (elements) in a table in SQL Server 2008?

Eric

View 7 Replies View Related

Set Number Of Columns In Matrix Table

Jul 17, 2007

I am creating a report that uses the Matrix control. I need to display a fixed number of columns (5). In my query, I am returning the top 5 rows of data. However, in some cases there are less than 5 rows of data returned from the dataset. Is there a way to force the number of columns displayed in the matrix control and to populate with some text (such as "n/a") if no data is available?

Thanks!

ads

View 1 Replies View Related

Variable Number Of Columns In A Temporary Table

Nov 8, 1999

How do I create a temporary table in a stored procedure with differeent number of columns?
That is: sometimes ten columns, sometimes 24 etc.

View 1 Replies View Related

Create Table With Veriable Number Of Columns

Apr 12, 2008

Hi everyone,

I need to create temporary table in one of the SP.The problem is that number of columns in table will vary depanding on input in SP.

How can i create table with variable number of columns?

Thanks,

Alex

View 8 Replies View Related

Database Table Design For Huge Number Of Columns

Jan 24, 2008

Hi

I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2
e.g
Sales tax = 10%
Use Tax = 10%
Share1 = 60%
Share2 = 40%

So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax
So Use tax Amt (B) = cost1 * share2 * Use tax

same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on..
and total cost with Use tax = Cost1 +B, Cost 2 +B etc.

So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size.
Some of these fields might be used for making reports.

I was wondering which is a better approach out of the below 3:
1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or
2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.
3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.

Your help is greatly appreciated.
Thanks

View 3 Replies View Related

Database Table Design For Huge Number Of Columns

Jan 24, 2008

Hi

I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2
e.g
Sales tax = 10%
Use Tax = 10%
Share1 = 60%
Share2 = 40%

So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax
So Use tax Amt (B) = cost1 * share2 * Use tax

same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on..
and total cost with Use tax = Cost1 +B, Cost 2 +B etc.

So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size.
Some of these fields might be used for making reports.

I was wondering which is a better approach out of the below 4:
1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or
2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.
3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.

4) Create a view just for reports, and calculate values dynamically in UI and not adding any computed values in table.

Your help is greatly appreciated.
Thanks

View 4 Replies View Related

Transact SQL :: How To Count Number Of Specific Columns Of Table

Jul 11, 2015

I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.

i.e. EmployeeID: 001

week1: 40
week2: 24
week3: 24
week4: 39

This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?

View 3 Replies View Related

T-SQL (SS2K8) :: Updating Existing Table With Max (value) And Row Number (partition By 2 Columns)

Sep 15, 2015

I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null

job_cdpermit_nbrreference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1991100003
ABC1992100004
ABC1993100005
ABC2880100006
ABC2881100007
ABC2881100007
ABC2882100008
ABC2882100008

View 2 Replies View Related

Maximum Number Of Columns In A Sql Server Express 2005 Table

Mar 12, 2008

What is the maximum number of columns you can have in a sql server express 2005 table?

View 4 Replies View Related

Dataset Into A Table Or Matrix With Fixed Number Of Columns, Variable Rows

Jul 13, 2007

Hi

I have a dataset with 2 columns, a rownumber and a servername - eg

rownumber servername

1 server1

2 server2

....

15 server15

I want to display the servernames in a report so that you get 3 columns - eg

server1 | server2 | server3

server4 | server5 | server6

...

server13 | server14 | server15

I have tried using multiple tables and lists and filtering the data on each one but this then makes formating very hard - i either end up with a huge gap between columns or the columns overlap

I have also tried using a matrix control but cant find a way to do this.

Does anybody know an easy way to do this? The data comes from sql 2005 so i can use a pivot clause on the dataset if somebody knows a way to do it this way. The reporting service is also RS2005

Thanks

Anthony

View 1 Replies View Related

Limitations In Term Of Number Of Tasks And Number Of Columns

Jun 5, 2007

Hi,

I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.

To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.

Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.

Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?

If not, then do you have any idea why it's so slow ?

Thanks in advance for any answer.

View 1 Replies View Related

SELECT Query - Different Columns/Number Of Columns In Condition

Sep 10, 2007

I am working on a Statistical Reporting system where:

Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by

different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1

Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2

Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:

Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups

Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:

How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)

Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.

Thanks for all your help!

View 1 Replies View Related

SQL 2012 :: Split Data From Two Columns In One Table Into Multiple Columns Of Result Table

Jul 22, 2015

So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:

**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3

**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3

In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID

I am trying to create a table such as shown below:

Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich

This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.

For e.g

Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One

[Code] .....

The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.

View 4 Replies View Related

Performance - String Vs Number?

Apr 11, 2008

Hi guys,

I have 2 tables:

Unit( unit_id integer, unit_name varchar(20) )
Part( part_id integer, part_name varchar(20) )

Then I have a join table between the two

Unit_Part( unit_id, part_id )

Each unit NAME and part NAME is unique. So what kind of performance would I be losing if I were to do something like:

PROCEDURE addPartToUnit( VARCHAR(20) sPartName, VARCHAR(20) sUnitName )
-- Now in this procedure i'll just get the id's via the names from a select statement

Versus

PROCEDURE addPartToUnit( nPartId INTEGER, nUnitId INTEGER )
-- In this proc, i directly input the primary keys of the table so no lookup is necessary

The reason is that I would like for developers to be able to write code without having to know the id's.

What do you think?

View 14 Replies View Related

Large Number Of Tables And Performance

Jan 25, 2008

Hi gurus, I'm creating a web application where I will have a large number of tables (between 10k and 20k), this is done for the sake of scalability as tables will be moved to different database servers as the application grows and also for performance (smaller indexes). I'm worried though how having a large number of tables could affect the performance of SQL Server as the application will start on one single database server. I tried to find some resources on that on the internet but couldn't find any.

I would really appreciate if you can give me some advice and if you have any good links that would be great...

View 10 Replies View Related

Large Number Of Tables And Performance

Jan 25, 2008

View 9 Replies View Related

Max. Number Of Columns

May 21, 2007

does anyone know what the maximum number of columns is that an SQL table can contain ?

i seem to remember that in the past it was something like 200 columns max, but i don't know whether that has changed with newer versions of SQL Server

looking at the spec for SQL Server 2000 i see a maximum of 1024 columns per base table - am i right in interpreting this to mean the maximum is now 1024 columns or is a base table different from an SQL table ?

View 2 Replies View Related

Dynamic Number Of Columns

Jun 2, 2005

when using sorred procedure to create a temporary table is it possable to base the number of columns in that table on another variable?

View 5 Replies View Related

The Number Of Columns Is Incorrect

Sep 4, 2007

While Running a SSIS package after migrating it from DTS to SSIS , in MS SQL Server 2005 ,
it gives error while execution :

DTS_DTSTASK_DATAPUMPTASK_2
The number of columns is incorrect.verify the column metadata is valid.
"OLEDB Destination "(22) Failed the pre execution phase and returned error code 0xC0202025
Thanks for the response .....

View 2 Replies View Related

ADO 2.8 Is There Any Limitation On Number Of Columns?

Sep 11, 2007

Hi,

I'm using ADO 2.8 in a vb.net code. The .Net framework version is 1.1 and windows server 2003.

I'm firing a query that result in 256 columns and few hundred rows. Here is the snapshot of the code

adoRs = New ADODB.Recordset

With adoRs

.CursorLocation = CursorLocationEnum.adUseClient ' adUseClient

.ActiveConnection = adoCn

.CursorType = CursorTypeEnum.adOpenStatic ' adOpenStatic

.LockType = LockTypeEnum.adLockBatchOptimistic ' adLockBatchOptimistic

.Open(strSQL)

End With

The returned record set is empty with all the the 256 columns name. Could anyone shed light why it is returning empty recordset. Is there any limitation on number of columns that a recordset can hold.

Thanks in advance.

With regards
Ganesh

View 4 Replies View Related

When Or Where Does To May Columns Or Tables Affect Performance?

May 4, 2001

I have a db which I have little control over most of it's makeup because of the vendor supplied tools. We currently have over 700 tables and 19000 columns. Has anyone seen a problem or saturation pont with these kinds of numbers? The database delivered to the clients will be from 2-50 gig depending on the site. I can probably through hardware at problems, but if anyone has been down this road any suggestions are appreciated.

View 1 Replies View Related

SQL Server 2005 Full Text Performance With Large Number Of Records

Dec 12, 2007

Hi
We are using the SQL Server 2005 Full Text Service. The data is not huge, but the kind of data is that each record is small and there are a large number of records. There are 35 million records now with 11 GB of data and about 1.6 GB of FT catalog on the table. This is expected to grow to at least 10 times the size of this data. The issue is with FTS taking a long time to return results when the number of hits (rows) getting returned from FTS is large for some searches, it takes a very long time. With the same data & catalog, those full text queries for less common words return timely. The nature of the problem doesnt allow us to only have top results. We need all the results. So it’s not about the size of data but the number of results getting returned from FT. (As the catalog is inverted). The machine is dual processor with 4 GB RAM.

I am considering splitting the table and hence the catalog and using multiple servers to do full text searches in smaller catalogs. Is there any other way this issue can be solved ?

If splitting is the only way, can you give me an idea as to what is a statistical/standard limit to the number of search results/cataog size as which FTS gives good results

Thanks in advance

View 1 Replies View Related

Number Manipulation In Non-identity Columns

Jun 29, 2006

hi.i am using ms sql server 2000. can somebody tell me what the code would be to remove all the values in a given column and replace them with the associated number of the row with each execution. so, if i have a column:nums|1||2||3||4|and somebody deletes record |2|i would like the nums colum to update to|1||2||3|not:|1||3||4|it seems simple but i am having a hard time with this. how is it done?thanks.

View 14 Replies View Related

Find The Smallest Number In Two Columns Was: How Do I Do This ?

Jun 15, 2005

Hi,
I have a sql query which gives me the result set with lots of columns and rows.

a b c allocated unallocated
- ------ 75458702 0484095809
------- 534534 8743857
------- 953459034 90584395

i have to find of which is the smallest number in both allocated and unallocated columns -
here in this case
it would be 534534.
how do i do this ?

Thanks

View 1 Replies View Related

String Match Number Of Different Columns

Apr 24, 2008

Hi,

In short:
I want my following problem to work with a LIKE instead of exact match and if possible be faster. (currently 4s)

Problem:
I got a set of rows with varchar(50), spread out over multiple tables.
All those tables relate to a central Colour table.
For each of the columns, I want to match the values with a set of strings I insert and then return a set of Colour.Id

E.g: input: 'BLACK', 'MERCEDES', '1984'
Would return colour ids "025864", 45987632", "65489" and "63249"
Because they have a colour name containing 'BLACK' or are on a car from 'MERCEDES' or are used in '1984'.

Current Situation:
I) Create a table containing all possible values
CREATE TABLE dbo.CommonSearch(
id int IDENTITY (1, 1) NOT NULL,
clr_id int,
keyWord varchar(40),
fieldType varchar(25)
And fill it with all the values (671694 rows)
)
II) Stored Procedure to cut a string up into a table:
CREATE FUNCTION dbo.SplitString
(
@param varchar(50),
@splitChar char = ''
)
RETURNS
@T TABLE (keyWord varchar(50))
AS
BEGIN
WHILE LEN( @param ) > 0 BEGIN
declare @val varchar(50)
IF CHARINDEX( @splitChar, @param ) > 0
SELECT @val = LEFT( @param, CHARINDEX( @splitChar, @param ) - 1 ) ,
@param = RIGHT( @param, LEN( @param ) - CHARINDEX( @splitChar, @param ) )
ELSE
SELECT @val = @param, @param = SPACE(0)
INSERT INTO @T values (@val)
END
RETURN
END
III)Stored Procedure to query the first table with the second one
CREATE PROCEDURE [dbo].[GetCommonSearchResultForTabDelimitedStrings]
@keyWords varchar(255) = ''
AS
BEGIN
SET NOCOUNT ON;
select clr_id, keyWord, fieldType
from dbo.commonSearch
where keyWord in (select * from splitString(@keyWords, ''))
END

So, how can I use a LIKE statement in the IN statement of the last query.
Furthermore, I was wondering if this is the best sollution to go for.
Are there any better methods? Got any tuning tips to squeeze out an extra second?

View 5 Replies View Related

FnParseString And Unknown Number Of Columns

Mar 20, 2007

Keshka writes "I'm using function fnParseString form http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033 in some of my sp.

it's very helpfull, but my question is if there is a way to split variable into columns if I don't know how many columns I'll have? It could be 1 or 2 or 3 and etc.

Thanks"

View 3 Replies View Related

Selecting One Greatest Number From Two Columns?

Jul 20, 2005

I have table1 and table2.In table1 I have a column of numbers, numbers1.In table2 I have a column of numbers, numbers2.I'd like to select the highest number represented in either column.Example:table1:column1--------------345565643656555676table2:column2--------------3456564556456456456456The number I would want would be 56456 since it's the largest numberout of all combined.How can I get that number with one select statement?--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]

View 1 Replies View Related