Calculating Avg Rowsize In My Db...

Nov 23, 2001

Hi,

I'm using SQL Server 7.0 SP3.

I need to calculate the average size of a record in my database to assist in estimating growth.

Is there a quick and/or easy way of doing this?

Thanks in advance,
Darrin

View 1 Replies


ADVERTISEMENT

Calculating Rowsize For User Tables

Aug 6, 2001

Hi,

how do i calculate rowsize for user tables in a database

Thanks in advance.....
jfk

View 3 Replies View Related

Rowcount, Rowsize

Sep 5, 2007

Trying to get the rowcount, rowsize for every table in everydatabase?
I'm getting the same tables from the same db. anyone ideas how to fix it?





declare @db varchar(40)
declare @sql varchar(8000)
declare @cursor varchar(8000)
declare @table_name varchar(60)


declare a cursor for

select name from master..sysdatabases



open a

fetch a into @db

while @@fetch_status = 0

begin
print ''
set @sql = 'use [' + @db + ']'
print @sql
exec (@sql)

declare b cursor for
select table_name from information_schema.tables
where table_type = 'base table'
and table_schema = 'dbo'
and table_name not like 'dbo.%'

open b
fetch next from b into @table_name
while @@fetch_status = 0
begin

select @cursor = 'exec sp_spaceused ' + '[' + @table_name + ']'
print @cursor
-- exec (@cursor)

fetch next from b into @table_name
end
close b
deallocate b



fetch next from a into @db
end

close a
deallocate a


=============================
http://www.sqlserverstudy.com

View 1 Replies View Related

Error On A Rowsize

Oct 9, 2007

I have a view that selects various fields, from various tbls.
When I run that view it erros out as follows:


Could not insert a row larger than the page size into a hash table. Resubmit the query with the ROBUST PLAN hint.
It seems like 1 particualr record, is exceeding the rowzise. and hence the errors.

How can I resolve this..

View 4 Replies View Related

Regarding RowSize In SQL Server 2000

Feb 28, 2007

Hi,I read this article on net recently.http://joseph.randomnetworks.com/archives/2005/08/30/sql-server-2000-maximum-row-size-8060-bytes/And according to it, SQL Server 2000 have around 8K of Row Size.Now consider this , i have table have two column and in each column i have inserted data around 6k.Now my question is , how sql server is going to store this.Is it in 2 different Row ?Can any one explains this or provides links related to it.ByeAmit 

View 1 Replies View Related

Mysterious Rowsize Error

Nov 27, 2000

Hi!

I get this mysterious error while running an update.
SQLServer tells me that my update row is to big to fit in a row.
Then I check the size of the row and and it is like 2000 bytes so
it should fit without any problem at all. The really strange about
it that when comments a field it works just fine. That field is always
null. Could that be the problem. Anyone who have ever had a similar
problem or so?

View 5 Replies View Related

Find A RowSize On A Record

Dec 11, 2007

I have a select statment that gives me an error, cannot go more than rowsize 8094.
I am trying to find the particualr record/acctno, where the rowsize is excedding the max limi of 8094 chars. Once I have the acctno, I will correct it.
But how do I find which acct has this issue? Bascailly how can I find which record size is more than 8094 chars?

View 1 Replies View Related

Is Being Over The 8080 Rowsize Limit A Big Problem

Dec 19, 2001

I have a SQL2000 database where the developers have a tool which creates screens and tables at the same time. During cleanup (dropping of some columns in these tables) I recieved the below error message

Warning: The table '%.*ls' has been created but its maximum row size (%d) exceeds the maximum numberof bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Apparently the tool is not sophisticated to recognize this problem. I looked at MS KB and it stated that inserts and updates may fail due to this. I probably know the answer to this, but has anyone else encountered this before, and what if any kind of problems did it cause. I am sure it is a no-brainer that this is asking for trouble, but if anybody has any experience with this issue all input appreciated.

View 1 Replies View Related

How Do I Calculate The Current Maximum Rowsize

Jun 5, 2007

I'm wondering how to calculate the current possible row width if all data where filled to max. I'm just curious if I'm approaching the 8060 max.



Sql Server 2005



Thanks,

Mitch

View 3 Replies View Related

T-SQL And Calculating A Sum

Sep 26, 2006

I want to sum up a column, but only summing up the top 25 quantites and then I want to subtract the sum of all quantities from the total of the top 25...how would this be done with t-sql?

View 3 Replies View Related

Calculating PK

Jul 2, 2001

SQL7

I am interested in your opinion on the creation of primary keys in a table - simple ones that is. It would be the replacement for an Identity column.

Some say calculate the value off a lookup table, others say use an external object like c++ dll reading/incrementing from a flat file.

Using an identity column reduces portability of the table.

Thoughts ?

Craig

View 2 Replies View Related

Help !!!calculating Age

Nov 15, 2001

Hi,
What the best way to calculate the age given two datetimes.
I tried using the DATEDIFF(yyy,startdate,enddate) but that seems to only compare the year and not look at the specific day.

I am looking for something that would return the following output


birthday= 12/6/1973
EndDate = 11/30/2001
then the age should be 27

If birthday = 12/6/1973
enddate = 12/11/1973
then the age should be 28

thanks in advance
Zoey

View 3 Replies View Related

Calculating RFM

Apr 17, 2007



I'm in the process of developing a Kimball-method CRM data warehouse and I'd like to have Recency, Frequency, Monetary "scores" at the customer level. Am I better off creating these in the relational database and feeding them to SSAS or have SSAS do the aggregations and assign the "scores"?



Also, has anyone located any SSIS transform scripts that would calculated RFM scores? This is probably a pretty easy task in VB.net but I haven't gone down that road yet.



Steve

View 1 Replies View Related

Calculating The Sum Of Columns...

Oct 31, 2006

It may be simple question, but how can i get the sum of column? with conditions like getting the some of clicks one user send and so...

View 5 Replies View Related

Calculating Ages

Jul 3, 2007

When users register with my site they give me their DoB, how can I work out an age from it. I was thinkin about doing it from the year, but that isnt very accurate. Can anyone help? thanks si! ps Id only want to work out how many years old they are. si! 

View 7 Replies View Related

Calculating ID Numbers

Mar 28, 2008

 I needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into  the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this?  Thanks for any help you provide.

View 2 Replies View Related

Calculating The 3rd Value By The First 2's Selection

May 30, 2008

Hi,
First off, let me state that i am new to ASP.net... and have had to hit the ground running... I have looked on the net but have not found a suitable explanation on how to do this.
Let me explain what i am trying to do: In my aspx page, i have 2 dropdown boxes where the user will select a skill catagory, and then a skill. Based on that selection, i would like to compute what the Service line will be. There is a primary and foreign key on CatID on the skills and SkillCat tables. This data would need to be inserted into the another table aswell, and this would need to go into a report later.
How do i go about doing this? Some examples would be greatly appreciated. And if you know of a good link, please post it for me.
 Skills Table









CatID
SkillID
Skills
SL_ID

1
4
Skill 1
3

2
5
Skill 2
2

2
6
Skill 3
2

3
7
Skill 4
4

3
8
Skill 5
4

4
9
Skill 6
NULL

4
10
Skill 7
NULL

5
11
Skill 8
3

6
12
Skill 9
3

7
13
Skill 10
4

8
14
Skill 11
1

9
15
Skill 12
3

9
16
Skill 13
3

10
17
Skill 14
6

11
18
Skill 15
1
Skills Catagory








CATID
Org
Skillcat

1
Org1
Skill Category 1

2
Org2
Skill Category 2

3
Org3
Skill Category 3

4
Org4
Skill Category 4

5
Org5
Skill Category 5

6
Org6
Skill Category 6

7
Org7
Skill Category 7

8
Org8
Skill Category 8

9
Org9
Skill Category 9
 Service Line Table







SL_ID
Service_line

1
Service Line 1

2
Service Line 2

3
Service Line 3

4
Service Line 4

5
Service Line 5

6
Service Line 6

7
Service Line 7
 
Thanks for all your help in advance.
Dash

View 1 Replies View Related

Help With Calculating Dates.

Aug 26, 2005

Hi,

I'm fairly new to MS SQL, but I'm trying to query the time elapse between two dates. Example below...

Select
dbo.res.book_date,
dbo.res.arrive_date,
Cast (dbo.translog.systemdate as datetime)
From
dbo.res
Where
dbo.res.resid > '500'

The third line is where I need help with a statement. I need the elapse time. Ex. if the book date is 20050820100000 and the arrive date is 20050820120000, I would like the third Select statement to return 2:00 hours or something similar. Thanks.

View 11 Replies View Related

Calculating Time

Aug 27, 2005

Hello,
I am fairly new to this, but I need to write a query that calculates whether an order made a shipping cutoff based on creation time, and when it actually shipped, based on local customer time. All times in the table below are central time.
Heres where it gets complicated for me. If the order was created after 8:00am local customer time the same day, and shipped prior to 5pm local customer time, it would be considered a pass, or a 1. If an order was created after 8am local customer time, BUT didnt ship until AFTER 5pm local customer time, it would be considered a failure or a 0. And to complicate things even more, if the order is created after 5pm local customer time, the order create time would be treated as it was created at 8am local customer time the following day local customer time, and would need to be shipped prior to 5pm local customer time that day. Would CASE be appropriate for this? Or am I thinking wrong? Any help would really be appreciated!!


Ordernum OrderCreate OrderShipDate TimeZ
67890 5/5/2005 11:575/6/2005 15:33 EST
35789 5/5/2005 13:575/5/2005 14:19 MST
44465 5/5/2005 13:58 5/5/2005 21:58 CST
87654 5/5/2005 18:00 5/7/2005 21:58 PST

View 13 Replies View Related

Calculating Median In SQL

Aug 25, 2004

Hi All,
I have a table that of server names and their execution times that run in to hundreds of thousands of records. What i need is some SQL that gives me the median execution times for each of these different servers. At the moment i have some SQL that only gives me the median for all the records in the table not the median execution time for every different server name. For example my tables looks something like this;

ServerName | ExecTime
-----------------------
server1 | 0.07
server2 | 0.17
server1 | 0.27
server1 | 0.37
server2 | 0.47
server1 | 0.57
server1 | 0.67
server2 | 0.77

My SQL below gives me

ServerName | ExecTime
-----------------------
server1 | 0.37

Where as i want

ServerName | ExecTime
-----------------------
server1 | 0.37
server2 | 0.47

Here is my SQL, hope someone can modify it and thanks in advance.


Code:


SELECT DISTINCT instance, exec_time AS median
FROM (SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time) sub_a
ORDER BY 1 DESC) sub_1
UNION ALL
SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time DESC) sub_b
ORDER BY 1) sub_2)

View 4 Replies View Related

Calculating Dates

May 16, 2004

I have a table in my sql server that calculates renewal dates for me, that date is based on the final suit date. The table has the ssn, and the FinalSuitDate its in a one to many relationship with the employeetable.

the Finalsuit table is suppose to calculate the renewal dates(which I'm trying to do in a query) my original expression in access was using dateserial FirstRenewal: DateSerial(Year([FINALSUITDONE])+2,Month([FINALSUITDONE])+1,Day([FINALSUITDONE])=30), but sql does not recognize that.

For Example

If the Finalsuit is 12/01/2000
then the renewal would be 12/31/2002
the renewal are to be done on the last day of the month and two years from the finalsuit date. Problems is I'm having problems doing this in sql servers query?

Can someone out there help please

View 8 Replies View Related

Calculating Age In Years

Dec 19, 2001

--Use the following formula to calculate a person's age in years in a stored procedure,
--where @dob is the person's date of birth and @yyyymmdd is the date on which to determine the age:

DECLARE @age int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)

SELECT @dob = '12/06/1966'
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)

--Notes:
--Substitute "getdate()" for the @yyyymmdd variable if you want to determine the person's age right now.
--The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer.
--The function DATEDIFF(year, @dob, @yyyymmdd) doesn't work because Microsoft chose to implement
--the "year" part of the function to calculate the number of year boundaries crossed.

View 20 Replies View Related

Calculating Between Tables

Nov 15, 2005

Hi All,

I have a very complicated project and for some reason I have decided to use SQL server as the backend database. I am new to it and have encountered my first problem. In basic terms what I want to be able to do is increment/decrement a field in a table according to something that happens in a different table. Where do I start?

So for example, a stereo system/cd player can have 10 cds at one particular time, when 2 cds are removed, then 8 slots are available. Then when 1 of the 2 remaining slots are filled then 1 slot remains and so on....so I want some sort of a counter field. Using that field I want to be able to generate a report on what is currently available.


Please Excuse my lame example but can anyone please help?

Thanks

Ben

View 1 Replies View Related

Calculating Interval

Mar 16, 2006

If two records containing datetime stamps are SELECTed, how can the interval between them be quickly calculated?

View 8 Replies View Related

Calculating Delays With Sql

Jul 11, 2006

I have three columns in my table,'StartDate',EndDate','BatchID'

When I upadate the the table for 'EndDate' I want to calculate the

I want to store this modified value somewhere and calculate the difference in (Value of 'End Date' Before update)-(Value of 'EndDate' after Update')

How it is possible?
Thanks in advance
Swati

View 1 Replies View Related

Calculating Duration

Jan 18, 2007

I am trying to get a query that will allow me to report the time taken to complete a certain training module.

The database itself does not have a duration field so I am tring to get the duration by using MIN and MAX. I can get the timing for when the module was opened and the time for the last mouse click on it, from this I need to be able to calculate the time taken to complete.

Query I am using to get the basic info comes from 3 tables so I have only attached the relevent output. Query used is as follow:

SELECT *
FROM PPS_SCOS, PPS_TRANSCRIPTS, PPS_TRANSCRIPT_DETAILS, PPS_PRINCIPALS
WHERE PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND PPS_TRANSCRIPTS.TRANSCRIPT_ID = PPS_TRANSCRIPT_DETAILS.TRANSCRIPT_ID
AND PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_SCOS.NAME LIKE 'MTM-106 The Dangers of Smoking'
AND PPS_PRINCIPALS.NAME LIKE 'Nigel Cordiner'
AND PPS_TRANSCRIPTS.TICKET NOT LIKE 'l-%'
ORDER BY PPS_TRANSCRIPT_DETAILS.DATE_CREATED

Output:

pps_scospps_scospps_transcript_detailspps_principalspps_principals
SCO_IDNAME DATE_CREATED PRINCIPAL_ID NAME
136850MTM-106 The Dangers of Smoking08:17:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:17:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:17:4016287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:18:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:18:5716287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:19:1416287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:19:4716287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:20:2116287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:20:4416287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:21:2616287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:22:1316287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:24:5516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:25:1216287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:25:2916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:26:4916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:0216287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:2916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:4316287Nigel Cordiner



Have added the column heading and the tables the output comes from.

Relatively new to SQL so any help would be greatly received.

View 4 Replies View Related

Calculating Percentages For Each Row

Dec 10, 2007

One more for you while I'm trying to remember my SQL skill from a few years ago (last one of the day - promise):
My database looks like:

(Ignore that the ERD is in Access, the database is SQL Server 2005)

How do I calculate sales percentages?
I.E. how do I calculate the percentage that each product has sold as a percentage of the total sales?

What I'm aiming for is each row of products is shown, with its percentage of the whole sales.

AVG is involved this time, isn't it?

The productID is of the int datatype (not that you need to know that field - but never mind) and the quantity is a smallint, before you ask


Thank you in advance for any replies.


Paul

View 5 Replies View Related

Calculating In A Statement

Mar 3, 2008

Hi,
Im building a DB for a uni project and im having a little trouble creating a column that makes a calculation using 2 of the other columns.

I need to do this;
Select Reps, Weight, ((Reps / 30) + 1) * Weight AS [1RM]
From ......

But, SQL server 2005 doesnt seem to like my syntax and gives a wrong result.
Can anyone help?

Thanks,
M4tt

View 4 Replies View Related

Calculating A Percentage

Mar 25, 2008

My site has members. Each member has a certain goal, an amount that they are hoping to raise. Here's my tables:

---------------------
Members
---------------------
ID | Goal
---------------------


---------------------
Donors
---------------------
Amount | MemberID
---------------------


I'm trying to get the percentage of money raised compared to their goal. Basically 100*(SUM(Amount)/Goal). Here's my stored procedure:

CREATE PROCEDURE sproc_GetGoalPercentage
(
@memberid int
)
AS
SELECT(SUM(D.Amount)/M.Goal)*100
FROMMembers AS M INNER JOIN Donors AS D
ON M.ID = D.MemberID
WHEREM.ID = @memberid
GROUP BY M.Goal
RETURN


All it does is return 0.

View 7 Replies View Related

Calculating Row Sizes

Feb 20, 2008

I'm putting together a manual system that tracks data growth in a certain database. I was going to use sp_spaceused as a part of it, but then realized the datatypes for size are CHAR, not INT or BIGINT. I was going to do counts, averages, etc. on those columns but that wouldn't work against a CHAR field obviously. I could easily write a little something to strip out the KB, but was hoping there was another way to get those figures.

Secondly...has anynoe seen a stored procedure/code/etc. that just calculates the largest/smallest/average row size for a table? I haven't been able to find anything anywhere...

Any insight would be greatly appreciated...

Thanks!

View 2 Replies View Related

Calculating Business Day

Aug 20, 2007

Hi,

I am wondering what is vb.net method of calculating the prior business day (Monday - Friday).

For example, if today is Monday, Aug. 20, the last business day would have been Friday, Aug. 17.

I have a way to do this in SQL, but not vb.net.

Thanks much

View 4 Replies View Related

Calculating Distince

Jul 12, 2006

I have a table with only one column each for longitude and latitude. They will be passing the sa_property_id which has a long and lat listed. I need to show what else is withing 1 mile or 5 miles of that properties long/lat

--Table
CREATE TABLE [dbo].[PropertyDetails] (
[SA_PROPERTY_ID] [int] NOT NULL ,
[SA_X_COORD] [float] NULL ,
[SA_Y_COORD] [float] NULL ,
[Longitude] [float] NULL ,
[Latitude] [float] NULL ,
) ON [PRIMARY]
GO



Data output from above table

SASA_PROPERTY_ID Longitude Latitude
-------------- ----------------------------------------------------- -----------------------------------------------------
23790208 -120.619821 39.568587999999998

(1 row(s) affected)


Passed parameter = SA_PROPERTY_ID

Need list of matching records within 1 mile of above record

Would this be the best query for it? how do I define the radius?

I also have a function, but am not sure it can do what I need it to.


/*
* History: 14-Nov-05 CNH Simplified to resolve Divide By Zero problems
* 29-Mar-02 DKS Created by Deepak K Srinivasan
*
* Test Data:
*
* City Latt Long
* ---- ---- ----
* NYC 40.77 74
* SF 37.75 122.68
* Oakland 37.73 122.22
* Burbank 34.2 118.37
*/
CREATE FUNCTION dbo.CalculateDistance(@LatDeg1 FLOAT, @LonDeg1 FLOAT, @LatDeg2 FLOAT, @LonDeg2 FLOAT)
RETURNS FLOAT AS BEGIN
DECLARE @EarthRadiusMiles AS FLOAT,
@LatRad1 AS FLOAT,
@LonRad1 AS FLOAT,
@LatRad2 AS FLOAT,
@LonRad2 AS FLOAT,
@DotProd AS FLOAT

SET @EarthRadiusMiles = 3958.755 -- Volumic Radius of the Earth in Miles

SET @LatRad1 = RADIANS(ISNULL(@LatDeg1, 0.0))
SET @LonRad1 = RADIANS(ISNULL(@LonDeg1, 0.0))
SET @LatRad2 = RADIANS(ISNULL(@LatDeg2, 0.0))
SET @LonRad2 = RADIANS(ISNULL(@LonDeg2, 0.0))

SET @DotProd = SIN(@LatRad1) * SIN(@LatRad2) + COS(@LatRad1) * COS(@LatRad2) * COS(@LonRad1 - @LonRad2)

-- T-SQL provides ACOS. So, there is no need to implement it via ATAN:

RETURN @EarthRadiusMiles * ACOS(@DotProd)
END


View 1 Replies View Related

Calculating Next Reference

Feb 6, 2008

Hi,

Our tables have a reference field which users can enter their own references into.

However, they can choose to have the system calculate the next reference number.

The reference can contain any characters from a-z, A-Z and 0-9.

In our system, reference AAAA is greater than reference ZZZ; the next reference after AAAA is AAAB.

How would I calculate the greatest current reference in the table?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved