Showing posts with label various. Show all posts
Showing posts with label various. Show all posts

Saturday, February 25, 2012

Articles on SQL Server capacity

I have been doing some reading for a project comparing various RDBMS and I
have read that SQL Server is not very stable for DBs over 300GB but I have
also read about terrabyte-plus databases running just fine. Are there any
recommendations or articles from Microsoft about the capacity of SQL Server
(2000 or 2005)?Hi
Probably you can check this:
http://chanduas.blogspot.com/2005/05/maximum-capacity-specifications.html
please let me know if you have any questions
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Robert Kinesta" wrote:
> I have been doing some reading for a project comparing various RDBMS and I
> have read that SQL Server is not very stable for DBs over 300GB but I have
> also read about terrabyte-plus databases running just fine. Are there any
> recommendations or articles from Microsoft about the capacity of SQL Server
> (2000 or 2005)?|||> I have read that SQL Server is not very stable for DBs over 300GB
Sounds like either advocacy or marketing misinformation. I have worked
on and know of many SQL Server databases that are much larger. Equally,
bad design or improper hardware can defeat a database of any size.
Take a look at:
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.mspx
--
David Portas
SQL Server MVP
--

Articles on SQL Server capacity

I have been doing some reading for a project comparing various RDBMS and I
have read that SQL Server is not very stable for DBs over 300GB but I have
also read about terrabyte-plus databases running just fine. Are there any
recommendations or articles from Microsoft about the capacity of SQL Server
(2000 or 2005)?
Hi
Probably you can check this:
http://chanduas.blogspot.com/2005/05...fications.html
please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Robert Kinesta" wrote:

> I have been doing some reading for a project comparing various RDBMS and I
> have read that SQL Server is not very stable for DBs over 300GB but I have
> also read about terrabyte-plus databases running just fine. Are there any
> recommendations or articles from Microsoft about the capacity of SQL Server
> (2000 or 2005)?
|||> I have read that SQL Server is not very stable for DBs over 300GB
Sounds like either advocacy or marketing misinformation. I have worked
on and know of many SQL Server databases that are much larger. Equally,
bad design or improper hardware can defeat a database of any size.
Take a look at:
http://www.microsoft.com/sql/techinf...alability.mspx
David Portas
SQL Server MVP

Articles on SQL Server capacity

I have been doing some reading for a project comparing various RDBMS and I
have read that SQL Server is not very stable for DBs over 300GB but I have
also read about terrabyte-plus databases running just fine. Are there any
recommendations or articles from Microsoft about the capacity of SQL Server
(2000 or 2005)?Hi
Probably you can check this:
http://chanduas.blogspot.com/2005/0...ifications.html
please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Robert Kinesta" wrote:

> I have been doing some reading for a project comparing various RDBMS and I
> have read that SQL Server is not very stable for DBs over 300GB but I have
> also read about terrabyte-plus databases running just fine. Are there any
> recommendations or articles from Microsoft about the capacity of SQL Serve
r
> (2000 or 2005)?|||> I have read that SQL Server is not very stable for DBs over 300GB
Sounds like either advocacy or marketing misinformation. I have worked
on and know of many SQL Server databases that are much larger. Equally,
bad design or improper hardware can defeat a database of any size.
Take a look at:
http://www.microsoft.com/sql/techin...calability.mspx
David Portas
SQL Server MVP
--

Friday, February 24, 2012

Array of Value

I'm learning T-SQL, and I want to know what are the various ways to represent an array of values. For example, I might like to have an array of record IDs, which I feed to an "IN" clause. My guess is that such an "array" would be implemented by using a RecordSet. If yes, thats good - but what are the other ways (if any)? If no, then what other kinds of variables or parameters allow me to do something like that?You can use subquery for that like in:

WHERE SomeColumn IN (SELECT SomeColumn From SomeTable)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Search the forums for "split function". You can then pass values into a stored procedure with a varchar like "2,34,67,98" and have that string split into a set that can be used in an IN clause. These split functions basically break the string up and puts the CSVs into a temporary table and then (similar to the above suggestion) selects the values from the table.

|||But what about doing a select that has results stored in a (single-column) RecordSet? I've not tried it, but can't that RecordSet be passed around as a parameter, and then fed "as is" into an "IN" clause? Is that not a common/popular thing to do? If not, why not?|||

>>Is that not a common/popular thing to do?<<

Funny way to put this. Common? Yes. Popular? Yes. Natively supported in SQL Server? Nope.

The "classic" paper on the subject is here: http://www.sommarskog.se/arrays-in-sql.html

You can do it with a table variable, but sadly, you cannot pass this around as a parameter.

Thursday, February 16, 2012

Arithmetic overflow error when doing outer joins

Hey everyone,

I have two tables, one is a large table (v_userviews) containing a list of all the servers and various information about those servers. The other table (l_printers) contains printer information for those servers. I am working on a view to consolidate the printer information in l_printers with the other server information in v_userviews.

I've been trying to get outer joins to work but I am getting this error:
"Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation."

Here is my select statement:

select u.propid, u.address,
SUM((CASE u.Tree WHEN 'tree1' then 1 ELSE 0 END)) AS One,
SUM((CASE u.Tree WHEN 'tree2' then 1 ELSE 0 END)) AS Two,
SUM((CASE u.Tree WHEN 'tree3' then 1 ELSE 0 END)) AS Three,
SUM((CASE u.Tree WHEN 'tree4' then 1 ELSE 0 END)) AS Four,
SUM((CASE u.Tree WHEN 'tree5' then 1 ELSE 0 END)) AS Five,
SUM((CASE u.Tree WHEN 'tree6' then 1 ELSE 0 END)) AS Six,
SUM((CASE u.Tree WHEN 'tree7' then 1 ELSE 0 END)) AS Seven,
SUM((CASE u.Tree WHEN 'tree8' then 1 ELSE 0 END)) AS Eight,
SUM((CASE u.Tree WHEN 'tree9' then 1 ELSE 0 END)) AS Nine,
SUM((CASE u.Tree WHEN 'tree10' then 1 ELSE 0 END)) AS Ten,
SUM((CASE u.Tree WHEN 'tree11' then 1 ELSE 0 END)) AS Eleven,
SUM((CASE u.Tree WHEN 'tree12' then 1 ELSE 0 END)) AS Twelve,
SUM((CASE u.Tree WHEN 'tree13' then 1 ELSE 0 END)) AS Thirteen,
SUM((CASE u.Tree WHEN 'tree14' then 1 ELSE 0 END)) AS Fourteen,

count(u.server) as totalservers,
sum(cast(left(u.totalspace,len(u.totalspace)-2) as int)) as totalspace,
sum(cast(left(u.totalusedspace,len(u.totalusedspac e)-2) as int)) as totalusedspace,
count(p.printer) as numprinters

from serverops.dbo.v_userviews u LEFT OUTER JOIN novell_twr.dbo.l_printers p ON u.propid = p.propid
where u.os='netware'and u.state in ('ny', 'nj', 'fl')
group by u.propid, u.address

the l_printers table is in this format:

Printers Server Propid
nvarchar nvarchar varchar

Thanks for all your help. :beer:Try commenting out all the sums, and see what the count of total servers looks like, I am going to guess it is somewhat larger than you expect. If so, then you will have to look over how you are joining the two tables.|||Try commenting out all the sums, and see what the count of total servers looks like, I am going to guess it is somewhat larger than you expect. If so, then you will have to look over how you are joining the two tables.

You were right, the total servers was definitely larger than expected. Will I have to use another method other than outer joins to consolidate the two tables?|||How many servers do you have?|||How many servers do you have?

Around 4800|||what's defined as tiny int?

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

smallint

Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).

tinyint

Integer data from 0 through 255.

post some ddl|||what's defined as tiny int?

post some ddl

Nothing is defined as tinyint

Here are some DDL

L_Printers:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[L_Printers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[L_Printers]
GO

CREATE TABLE [dbo].[L_Printers] (
[Printer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Server] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

R_Servers (v_userviews is actually just a distinct top 100 view of r_servers):
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[R_Servers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[R_Servers]
GO

CREATE TABLE [dbo].[R_Servers] (
[Server] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type] [varchar] (51) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Classification] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPX Internal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Secondary IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_IP1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_IP2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_RIBIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Branch #] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSVersion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InProduction] [bit] NULL ,
[NOTES] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tree] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NWContext] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomain] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomainRole] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTXEUpdate] [datetime] NULL ,
[MacAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CommonName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TFloors] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bank_Floors] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalEmp] [float] NULL ,
[Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Street Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Room] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Floor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPResponse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ResponseOK] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TapeDrive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TapeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DriveCapacity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IP Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBPW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Remote PW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Console PW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeviceStatus] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBInstalled] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPack] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeviceOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ASource] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BackupDetails] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServerID] [numeric](10, 0) NOT NULL ,
[Model] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Serial] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssetNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Memory] [numeric](18, 0) NULL ,
[ROM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPU] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPUSpeed] [decimal](18, 0) NULL ,
[TotalCPUs] [int] NULL ,
[Vendor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PODate] [datetime] NULL ,
[ReceivedDate] [smalldatetime] NULL ,
[ActivationDate] [smalldatetime] NULL ,
[RefreshDate] [smalldatetime] NULL ,
[WarrantyEndDate] [smalldatetime] NULL ,
[TowerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TowerID] [int] NULL ,
[DistrictID] [int] NULL ,
[Priority] [int] NULL ,
[Severity] [int] NULL ,
[SupportComment] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SiteContact] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupportStaff] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PSRegion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DistrictManager] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DMName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupportQueue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[District] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Domain] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[inProductionDate] [smalldatetime] NULL ,
[Region] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalSpace] [nvarchar] (257) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalUsedSpace] [nvarchar] (257) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackPosition] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackRow] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSType] [int] NULL ,
[Project#] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClusterID] [numeric](18, 0) NULL ,
[RIBLicenseKey] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prop_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [datetime] NULL ,
[CheckSum] [int] NULL ,
[ManagmentServer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastAudit] [datetime] NULL ,
[RecordAuditor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimarySupport] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVM] [bit] NULL ,
[KVMType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[expanse] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOBOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryRecordOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreationDate] [datetime] NULL ,
[Strategic] [bit] NULL ,
[ServiceBillingCC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HardwareSupport] [bit] NULL ,
[Updated] [datetime] NOT NULL
) ON [PRIMARY]
GO