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
Hi,
how do i calculate rowsize for user tables in a database
Thanks in advance.....
jfk
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
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..
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 RelatedHi!
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?
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?
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.
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
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 RelatedSQL7
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
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
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
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 RelatedWhen 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 RelatedI 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 RelatedHi,
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
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.
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
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)
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
--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.
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
If two records containing datetime stamps are SELECTed, how can the interval between them be quickly calculated?
View 8 Replies View RelatedI 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
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.
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
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
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.
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!
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
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
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?