Binding Gridview from two different tables

Hi all,

The Scenario:

Database1:Table1(callingPartyNumber,originalCalledPartyNumber, finalCalledPartyNumber, DateTimeConnect, DateTimeDisconnect, Duration)

Database2:Table2(Name,Number)

Output in Gridview:

callingPartyNumber

NameoriginalCalledPartyNumberfinalcalledPartyNumberdateTimeConnecteddateTimeDisconnectedDuration (HH:MM:SS)

I bind gridview programatically using DataTable and stored procedures. The data comes from a table (Table1) in a database (Database1) on SQL Server. The gridview displays fields callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber, DateTimeConnect, DateTimeDisconnect and Duration in this order. All the columns in this gridview are databound columns.

I have another table (Table2) in a seperate SQL Server database (Database2) but on the same server which maps the callingPartNumber value with the name attached with that number. Note that the field names in Table2 are different from the field names in Table1. Is it possible to display the Name field also in the gridview after the first field callingPartyNumber and then the other fields.

Its like data coming from two tables into the gridview.

Thanks

I would suggest you to use a View in GridView by joining this tables.

CREATE VIEW dbo.Callers AS

SELECT dbo.Database1.Table1.* dbo.Database2.Table2.* FROM dbo.Database1.Table1 RIGHT OUTER JOIN dbo.Database2.Table2 WHERE dbo.Database1.Table1.CallingPartyNumber = dbo.Database2.Table2.Number

|||

SELECT callingPartyNumber,Name,originalCalledPartyNumber, finalCalledPartyNumber, DateTimeConnect, DateTimeDisconnect, Duration

FROM database1.dbo.Table1 t1

LEFT JOIN database2.dbo.Table2 t2 ON t1.callingPartyNumber=t2.Number

Change "LEFT JOIN" to "JOIN" if you only want records that have a name.

|||

Fabulous!!! Thank you so much. I guess I explained my problem nicely this time...eh...One question though, I didn't even get a chance to try your suggestion and the post was marked as answer within a few minutes of your posting it. I read somewhere, most probably in a post from a moderator, that they give us a chance (usually 48 hrs) to mark the post as an answer.

|||

I am still trying to figure out this but thought would share. You might know what I am doing wrong. When I do the LEFT JOIN, it pulls all the names right, but the total count of records is always a bit off than when I don't do a LEFT JOIN. I read about all the joins and LEFT JOIN is what I am looking for, but I think I am going wrong somewhere. I also have a WHERE clause added to the query.

CREATE Procedure GetCDR_LikeTollFree ( @.theDurationint =null,--to find duration more than theDuration @.totalDurationbigint =null,--to find the total duration for the result set @.callingPartyNumbervarchar(255) =null, @.originalCalledPartyNumbervarchar(255) =null, @.finalCalledPartyNumbervarchar(255) =null, @.dateTimeConnectdatetime =null, @.dateTimeDisconnectdatetime =null )AS BEGIN SELECT callingPartyNumber,Name, originalCalledPartyNumber, finalCalledPartyNumber,dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 ,'01-01-1970 00:00:00')AS dateTimeConnect,dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600,'01-01-1970 00:00:00')AS dateTimeDisconnect, durationFROM db1.dbo.Calls t1LEFTJOIN db2.dbo.NumPlan t2ON t1.callingPartyNumber=t2.NumberWHERE (t1.callingPartyNumberLIKEISNULL(@.callingPartyNumber, t1.callingPartyNumber) +'%')AND (t1.originalCalledPartyNumberLIKEISNULL(@.originalCalledPartyNumber, t1.originalCalledPartyNumber) +'%')AND (t1.finalCalledPartyNumberLIKEISNULL(@.finalCalledPartyNumber, t1.finalCalledPartyNumber) +'%')AND (t1.duration >= @.theDuration)AND ((t1.datetimeConnect - 14400) >=ISNULL(convert(bigint,datediff(ss,'01-01-1970 00:00:00', @.dateTimeConnect)), t1.datetimeConnect))AND ((t1.dateTimeDisconnect - 14400) <=ISNULL(convert(bigint,datediff(ss,'01-01-1970 00:00:00', @.dateTimeDisconnect)), t1.dateTimeDisconnect))END

The query before inserting the LEFT JOIN was giving the correct number of records.

|||

Sorry about that...I am pretty sure the problem is at the table end. I have some bad data (duplicates) and thats creating a problem.

|||

Looking at your query, the only reason I can see that the record counts would be different is if in the NumPlan table, you have more than 1 name listed for a specific Number. If that is the case, then you will get one record back for each name.

As for your other question, moderators can also mark posts as answered (Submitters can always unmark them if need be). But it helps when people are scanning the forums looking for questions that haven't been answered, so I normally mark posts that I feel have a 95%+ chance of fully answering the question as answered. Unfortuantely, the moderator and MVP tags are a bit quirky and they come and go by themselves sometimes, so it's hard to tell who is a moderator/mvp and who isn't.

|||

Motley:

Looking at your query, the only reason I can see that the record counts would be different is if in the NumPlan table, you have more than 1 name listed for a specific Number. If that is the case, then you will get one record back for each name

You are absolutely right. I figured that out but it took some time. I keep trying myself and when I fall short of time, I post, and it has happened so many times that I figure out the solution to the problem after posting.

Motley:

As for your other question, moderators can also mark posts as answered (Submitters can always unmark them if need be). But it helps when people are scanning the forums looking for questions that haven't been answered, so I normally mark posts that I feel have a 95%+ chance of fully answering the question as answered. Unfortuantely, the moderator and MVP tags are a bit quirky and they come and go by themselves sometimes, so it's hard to tell who is a moderator/mvp and who isn't.

I was almost sure that you marked that post as answer as it wasTHEanswer. Don't worry about it. I only mentioned that as I read that moderators generally give us 48 hrs.

sql

Bind to multiple tables from stored procedure

I know a sql stored procedure can return >1 tables. How can I use .Net 2.0 to read these tables one at a time, for example the first one could iterate Forum entries and the second one all internal links used in these forums... The idea is to use fewer backtrips to the sql server?

Isthis article of any use?|||no fortunately not :(

Bind multi-table dataset to a datagrid

I have created a SQL procedure that returns a dataset with a varying number of tables like the following example:

RepID-- PhoneUPS
---- ----
3---- 3

RepID-- PhoneUPS
---- ----
4---- 0

RepID-- PhoneUPS
---- ----
5---- 2

No more results.
(9 row(s) returned)
@.RETURN_VALUE = 0

All of the tables have the same header row, but are a seperate table. From my experience, I am not able to bind more than one table to a datagrid. Does anyone have any suggestions on how I can go about displaying this full dataset on my aspx page? I've been going in circles on this for two days, so I'm open to any suggestions :)

Cheers,
AndrewCan you create a UNION query in the stored procedure, rather then seperate resultsets?

Failing that, you can manually add each row of each resultset to the Items collection of the Datagrid.|||Thanks for the tip. I've been working on my SQL procedure trying to incorporate the UNION in the SELECT statement. I am having problems with the logic of working it in, though. Here is the original SQL procedure I've created:

/*BEGIN Procedure */

ALTER PROCEDURE Rep_Status_Array

AS

/* Build array or ID numbers to cycle through */
DECLARE RepIDs_cursor CURSOR
FOR SELECT UserID
FROM TBL_Users
WHERE (TBL_Users.DepartmentID > 0)

OPEN RepIDs_cursor
DECLARE @.userID int
FETCH NEXT FROM RepIDs_cursor INTO @.userID

/* Begin WHILE loop to collect data for each ID */
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SELECT
RepID=@.userID,

PhoneUPS=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 11) AND (SalesmanID = @.userID)),

LOTUPS=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 1) AND (SalesmanID = @.userID)),

CVR=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 1) AND (SalesmanID = @.userID)),

FETCH NEXT FROM RepIDs_cursor INTO @.userID
END
/* END WHILE loop */

CLOSE RepIDs_cursor
DEALLOCATE RepIDs_cursor

/* END Procedure */

The problem I'm having with this is that each time through the WHILE loop creates a new table in the dataset that is returned by the procedure. Any suggestions?|||Is it even possible to use UNION or UNION ALL within a WHILE loop?|||To the best of my knowledge, it is not possible to use the UNION statement in conjunction with a WHILE loop.

I'm going to go with Douglas' second recommendation and manually create a table within my page, adding each row one at a time.|||Another alternative:

Create a temporary table, and then INSERT all selected rows into the temp table, and then at the end of the SP,

SELECT * FROM #temp

(with any required ORDER BY).|||Thanks for the help, Douglas, much appreciated!

Andrew

Bind many tables from a single sp on many tables on a single repor

Hi,
i've a single stored procedure that return many tables from different select
queries. it is possible to bind on a single report all "recordset" on
different tables? in other words how i can "navigate" the source dataset, is
possible to refer to a kind of dataset index? ex. dsname[1], dsname[2] etc?RS does not support this. You either need multiple stored procedures or you
need to pass a parameter to the sp that says which recordset you want. Note
that either way you will have to call a stored procedure per dataset. It is
a one to one relationship.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"luspo" <luspo@.discussions.microsoft.com> wrote in message
news:CC7325EC-29A7-43F7-B857-4F0F0CFF1E67@.microsoft.com...
> Hi,
> i've a single stored procedure that return many tables from different
select
> queries. it is possible to bind on a single report all "recordset" on
> different tables? in other words how i can "navigate" the source dataset,
is
> possible to refer to a kind of dataset index? ex. dsname[1], dsname[2]
etc?

Bind 2 tables from different database file.

Hi!

I have 2 databases. One is ASPNETDB.MDF and another is PPQ_Data.MDF
ASPNETDB.MDF is generated by ASP.net (VWD 2005) when I use Login control.
PPQ_Data.MDF is created by me. It contains menu and order information of my website.

I would like to connect this 2 databases together so that I can createa GridView table that binds 2 tables, one from ASPNETDB.MDF and anothertable from PPQ_Data.MDF. So, it is kind of displaying nested data.

Is there anybody out there that know how to connect 2 databases together?

Again, my purpose of connecting 2 databases together is to pull out a table from each database and bind the 2 tables together.

thanks in advanced for any advice or articles submitted.

You can use qualified database object name to refer to tables in different databases, and even from different servers (you have to make sure the app can connect to all referenced databse resources). Forexample (I suppose you database name to be c:\ASPNETDB.MDF and c:\PPQ_Data.MDF):

select * from [c:\ASPNETDB.MDF]..table1 , [c:\PPQ_Data.MDF]..table2

For more information, please see this article:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_fa-fz_4ox9.asp?frame=true

|||Ohhw, yeah, thanks for the reply. I almost forget about SQL server online book. thanks!

BINARY_INTEGER in CRXI

Hi...
could anyone clarify my doubts...

I am using BINARY_FLOAT columns in my oracle tables.
In crystal report XI, when you connect to oracle and see the list of tables and columns...the table does not show the columns which are of BINARY_FLOAT type.

What could be the reason??
CRXI doesnt support this???

Help me pls

rgds
salaiSee if you find answer here
http://support.businessobjects.com/

binary checksum

Hi,
Can anyone provide me with the syntax for comparing rows of two tables using binary checksum? The tables A and B have 8 & 9 columns respectively. The PK in both cases is Col1 & Col2. I want checksum on Columns 1 to 8.
ThanksTry this draft:

drop table test
drop table test2
go
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)
go
insert test values(1,1,'a','02/03/2004')
insert test values(2,2,'b','02/04/2004')
insert test values(3,3,'c','02/05/2004')
insert test values(4,4,'d','02/06/2004')
insert test2 values(1,1,'a','02/03/2004')
insert test2 values(2,2,'b','02/04/2004')
insert test2 values(3,3,'f','02/05/2004')
insert test2 values(4,4,'d','02/01/2004')
go
select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)|||Hi,

The code doesn't work for this case.

drop table test
drop table test2
go
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')

select *
from test

select *
from test2

select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)

--

All my data is like this. The rows are the same but still checksum selects the rows. Please help.|||drop table test
drop table test2
go
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')

select *
from test

select *
from test2

select t.*
from test t
join test2 t2 on t2.col1=t.col1 AND t2.col2=t.col2 -- Join on PK
where BINARY_CHECKSUM(t.id,t.col3) <> BINARY_CHECKSUM(t2.id,t2.col3)

-- A much more complex, but its the way I do it.

SELECT t.*
from test t
JOIN
(
SELECT t1.col1, t1.col2, BINARY_CHECKSUM(*) as bin_ck_sum
from test t1
) AS X1 ON t.col1 = X1.col1 AND t.col2 = X1.col2
JOIN
(
SELECT t2.col1, t2.col2, BINARY_CHECKSUM(*) as bin_ck_sum
from test2 t2
) AS X2 ON t.col1 = X2.col1 AND t.col2 = X2.col2
WHERE X1.bin_ck_sum <> X2.bin_ck_sum|||TimS, your method is the most appropriate when comparing one entire record to another, but vivek_vdc only wants to check on 8 of the 9 columns in one table against the 8 columns in the other table. Binary_Checksum(*) on both tables will not do this comparison.|||--
TimS, your method is the most appropriate when comparing one entire record to another, but vivek_vdc only wants to check on 8 of the 9 columns in one table against the 8 columns in the other table. Binary_Checksum(*) on both tables will not do this comparison.
--

I agree but, I gave him two different solutions.

The Second, is how I would compare tables and it can be adapted to his problem; I have found that the second solution works best for me. Thier is no reason he can't replace the star with what columns he wish to compare.

THE PROBLEM was no one was joining on the PK of the tables.

Tim S|||You have to join on the primary keys change the join to use the columns in your primary key!

select t.*
from test t
-- Join on PK1 & PK2 ( the Primary Key COLUMNS )
join test2 t2 on t2.PK1=t.PK1 AND t2.PK2=t.PK2
where BINARY_CHECKSUM(t.id,t.col3) <> BINARY_CHECKSUM(t2.id,t2.col3)

If you want any more help please response to the newsgoup. Please give your create table and insert data that MATCHES your tables.

Tim S

--Original Message--
From: vivek_vdc
Sent: Monday, February 09, 2004 3:46 PM
To:
Subject: binary checksum post help

TimS - The code that you have given is assuming col1 & col2 are the PK but I have id & Col1 as PK. The code doesn't work in this case. Please advise on how I should proceed?

Bill of material (SQL2000)

Hi,
Does anyone got a really good/fast example on how to do a BOM with unlimited
levels in SQL2000
- without using cursors :)
2 tables (one for items, one for parent/child relations)
In SQL2005 we of course are blessed with the new CTE, not in SQL2000 :-/
In advance, thanks...
Kr. Sorenhow do you want your data output? just a SP that returns a list of all
materials a particular item requires, right down the tree?
if so then the only way I can think of without cursors is using global
temporary tables and recursively called stored procedures to populate
it (but then you might as well use a cursor for that).
if you're wanting columns of data to represent the tree structure then
it's Dynamic SQL you'll need|||Soren, here goes:
-- DDL & Sample Data for Parts, BOM
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.BOM') IS NOT NULL
DROP TABLE dbo.BOM;
GO
IF OBJECT_ID('dbo.Parts') IS NOT NULL
DROP TABLE dbo.Parts;
GO
CREATE TABLE dbo.Parts
(
partid INT NOT NULL PRIMARY KEY,
partname VARCHAR(25) NOT NULL
);
INSERT INTO dbo.Parts(partid, partname) VALUES( 1, 'Black Tea');
INSERT INTO dbo.Parts(partid, partname) VALUES( 2, 'White Tea');
INSERT INTO dbo.Parts(partid, partname) VALUES( 3, 'Latte');
INSERT INTO dbo.Parts(partid, partname) VALUES( 4, 'Espresso');
INSERT INTO dbo.Parts(partid, partname) VALUES( 5, 'Double Espresso');
INSERT INTO dbo.Parts(partid, partname) VALUES( 6, 'Cup Cover');
INSERT INTO dbo.Parts(partid, partname) VALUES( 7, 'Regular Cup');
INSERT INTO dbo.Parts(partid, partname) VALUES( 8, 'Stirrer');
INSERT INTO dbo.Parts(partid, partname) VALUES( 9, 'Espresso Cup');
INSERT INTO dbo.Parts(partid, partname) VALUES(10, 'Tea Shot');
INSERT INTO dbo.Parts(partid, partname) VALUES(11, 'Milk');
INSERT INTO dbo.Parts(partid, partname) VALUES(12, 'Coffee Shot');
INSERT INTO dbo.Parts(partid, partname) VALUES(13, 'Tea Leaves');
INSERT INTO dbo.Parts(partid, partname) VALUES(14, 'Water');
INSERT INTO dbo.Parts(partid, partname) VALUES(15, 'Sugar Bag');
INSERT INTO dbo.Parts(partid, partname) VALUES(16, 'Ground Coffee');
INSERT INTO dbo.Parts(partid, partname) VALUES(17, 'Coffee Beans');
CREATE TABLE dbo.BOM
(
partid INT NOT NULL REFERENCES dbo.Parts,
assemblyid INT NULL REFERENCES dbo.Parts,
unit VARCHAR(3) NOT NULL,
qty DECIMAL(8, 2) NOT NULL,
UNIQUE(partid, assemblyid),
CHECK (partid <> assemblyid)
);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 1, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 2, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 3, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 4, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 5, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(10, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 1, 'mL', 230.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(10, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 2, 'mL', 205.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(11, 2, 'mL', 25.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(11, 3, 'mL', 225.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 9, 4, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 4, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 9, 5, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 5, 'EA', 2.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(13, 10, 'g' , 5.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 10, 'mL', 20.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 12, 'mL', 20.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(16, 12, 'g' , 15.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(17, 16, 'g' , 15.00);
GO
-- Creation Script for Function fn_partsexplosion
---
-- Function: fn_partsexplosion, Parts Explosion
--
-- Input : @.root INT: Root part id
--
-- Output : @.PartsExplosion Table:
-- id and level of contained parts of input part
-- in all levels
--
-- Process : * Insert into @.PartsExplosion row of input root part
-- * In a loop, while previous insert loaded more than 0 rows
-- insert into @.PartsExplosion next level of parts
---
USE tempdb;
GO
IF OBJECT_ID('dbo.fn_partsexplosion') IS NOT NULL
DROP FUNCTION dbo.fn_partsexplosion;
GO
CREATE FUNCTION dbo.fn_partsexplosion(@.root AS INT)
RETURNS @.PartsExplosion Table
(
partid INT NOT NULL,
qty DECIMAL(8, 2) NOT NULL,
unit VARCHAR(3) NOT NULL,
lvl INT NOT NULL,
n INT NOT NULL IDENTITY, -- surrogate key
UNIQUE CLUSTERED(lvl, n) -- Index will be used to filter lvl
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0; -- Initialize level counter with 0
-- Insert root node to @.PartsExplosion
INSERT INTO @.PartsExplosion(partid, qty, unit, lvl)
SELECT partid, qty, unit, @.lvl
FROM dbo.BOM
WHERE partid = @.root;
WHILE @.@.rowcount > 0 -- while previous level had rows
BEGIN
SET @.lvl = @.lvl + 1; -- Increment level counter
-- Insert next level of subordinates to @.PartsExplosion
INSERT INTO @.PartsExplosion(partid, qty, unit, lvl)
SELECT C.partid, P.qty * C.qty, C.unit, @.lvl
FROM @.PartsExplosion AS P -- P = Parent
JOIN dbo.BOM AS C -- C = Child
ON P.lvl = @.lvl - 1 -- Filter parents from previous level
AND C.assemblyid = P.partid;
END
RETURN;
END
GO
-- Parts Explosion
SELECT P.partid, P.partname, PE.qty, PE.unit, PE.lvl
FROM dbo.fn_partsexplosion(2) AS PE
JOIN dbo.Parts AS P
ON P.partid = PE.partid;
Output:
partid partname qtyn unit lvl
-- -- -- -- --
2 White Tea 1.00n EA 0
6 Cup Cover 1.00n EA 1
7 Regular Cup 1.00n EA 1
10 Tea Shot 1.00n EA 1
14 Water 205.00n mL 1
11 Milk 25.00n mL 1
13 Tea Leaves 5.00n g 2
14 Water 20.00n mL 2
-- Parts Explosion, Aggregating Parts
SELECT P.partid, P.partname, PES.qty, PES.unit
FROM (SELECT partid, unit, SUM(qty) AS qty
FROM dbo.fn_partsexplosion(2) AS PE
GROUP BY partid, unit) AS PES
JOIN dbo.Parts AS P
ON P.partid = PES.partid;
Output:
partid partname qty unit
-- -- -- --
2 White Tea 1.00 EA
6 Cup Cover 1.00 EA
7 Regular Cup 1.00 EA
10 Tea Shot 1.00 EA
13 Tea Leaves 5.00 g
11 Milk 25.00 mL
14 Water 225.00 mL
In SQL Server 2005 it would look like this:
-- CTE Solution for Parts Explosion
DECLARE @.root AS INT;
SET @.root = 2;
WITH PartsExplosionCTE
AS
(
-- Anchor member returns root part
SELECT partid, qty, unit, 0 AS lvl
FROM dbo.BOM
WHERE partid = @.root
UNION ALL
-- Recursive member returns next level of parts
SELECT C.partid, CAST(P.qty * C.qty AS DECIMAL(8, 2)),
C.unit, P.lvl + 1
FROM PartsExplosionCTE AS P
JOIN dbo.BOM AS C
ON C.assemblyid = P.partid
)
SELECT P.partid, P.partname, PE.qty, PE.unit, PE.lvl
FROM PartsExplosionCTE AS PE
JOIN dbo.Parts AS P
ON P.partid = PE.partid;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Soren S. Jorgensen" <nospam@.nodomain.com> wrote in message
news:ObORLLiXGHA.3936@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Does anyone got a really good/fast example on how to do a BOM with
> unlimited levels in SQL2000
> - without using cursors :)
> 2 tables (one for items, one for parent/child relations)
> In SQL2005 we of course are blessed with the new CTE, not in SQL2000 :-/
> In advance, thanks...
> Kr. Soren
>
>|||Get a copy of TREES & HIERARCHIES IN SQL for details then Google Nested
Sets model.

Bigint vs int

I had set up my tables using BigInts for my Surrogate keys (identity) to
make sure I could never run out of numbers.
I am thinking maybe that was overkill and my slow my system down (I know it
takes up more space, but that is not really an issue).
Are the Bigints going to cause me a problem down the line?
Also, if I start changing the BigInts to Ints in my Database and Stored
Procedure and miss some of the Stored procedures, would that cause me any
problems. I also have the ASP.Net pages that call the Stored Procedure set
to BigInts - would there be a problem if I miss some of these. In
otherwords, would there be a problem with mixing and matching the types
until I get them all taken care of?
Thanks,
Tom>I had set up my tables using BigInts for my Surrogate keys (identity) to
>make sure I could never run out of numbers.

> Are the Bigints going to cause me a problem down the line?
Problems? No. The only issue is that all of your keys are twice as large
as for an int. This will not "hurt" anything, but it is a waste of
resources. Tables with GUIDs as keys can perform just fine also, and they
take 16 byts. It is all into how much disk space and memory you need. It
will be slightly more for bigints.
The real question is whether you really have a need for 2 billion (well, 4
billion if you don't mind negatives after you exhaust the positive values)
values in your tables? I only have had two tables in my life where this was
possible (and only one has reached this amount) and they were both used to
track clicks on a pretty large web click tracking software table. I am
actually more likely to end up with small- and tiny- int keys than I am
bigint. But you have to ask yourself, if there is a possibility of that
kind of data, what do you want to do?
If this is an OLTP system, I would suggest you probably will want to delete
old data before doing this. A billion of anything is quite a bit.

> Also, if I start changing the BigInts to Ints in my Database and Stored
> Procedure and miss some of the Stored procedures, would that cause me any
> problems. I also have the ASP.Net pages that call the Stored Procedure
> set to BigInts - would there be a problem if I miss some of these. In
> otherwords, would there be a problem with mixing and matching the types
> until I get them all taken care of?
Not "big" problems, unless you try to pass a value that fits in a bigint and
not in an int. If you aren't using stored procs, you may have some issues
with type mismatches cause funky plans, but not enough to avoid switching.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uI2qqGhmFHA.1372@.TK2MSFTNGP10.phx.gbl...
>I had set up my tables using BigInts for my Surrogate keys (identity) to
>make sure I could never run out of numbers.
> I am thinking maybe that was overkill and my slow my system down (I know
> it takes up more space, but that is not really an issue).
> Are the Bigints going to cause me a problem down the line?
> Also, if I start changing the BigInts to Ints in my Database and Stored
> Procedure and miss some of the Stored procedures, would that cause me any
> problems. I also have the ASP.Net pages that call the Stored Procedure
> set to BigInts - would there be a problem if I miss some of these. In
> otherwords, would there be a problem with mixing and matching the types
> until I get them all taken care of?
> Thanks,
> Tom
>|||You can actually find an answer to your question using a calculator. Conside
r
the number of rows inserted daily and you can predict when you 'run out of
numbers'.
My guess: not in your life-time - even with integer.
ML|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OWXwVvhmFHA.3312@.tk2msftngp13.phx.gbl...
>
> Problems? No. The only issue is that all of your keys are twice as large
> as for an int. This will not "hurt" anything, but it is a waste of
> resources. Tables with GUIDs as keys can perform just fine also, and they
> take 16 byts. It is all into how much disk space and memory you need. It
> will be slightly more for bigints.
>
But if they are not used in any of my indexes, would there be any
performance problems?

> The real question is whether you really have a need for 2 billion (well, 4
> billion if you don't mind negatives after you exhaust the positive values)
> values in your tables? I only have had two tables in my life where this
> was possible (and only one has reached this amount) and they were both
> used to track clicks on a pretty large web click tracking software table.
> I am actually more likely to end up with small- and tiny- int keys than I
> am bigint. But you have to ask yourself, if there is a possibility of
> that kind of data, what do you want to do?
Actually, I don't know if I would come close, but I don't want to build this
system - which could take a couple of years to finish, only to find out that
the numbers are filling up faster than I expected.

> If this is an OLTP system, I would suggest you probably will want to
> delete old data before doing this. A billion of anything is quite a bit.
I would definately be deleting the records as I go, but the identity field
would still be increasing by one even if I dumped half the records.

>
> Not "big" problems, unless you try to pass a value that fits in a bigint
> and not in an int. If you aren't using stored procs, you may have some
> issues with type mismatches cause funky plans, but not enough to avoid
> switching.
If the issues aren't bad issues than I could just start making the changes
and deal with the differences as I go along.
Thanks,
Tom.

> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:uI2qqGhmFHA.1372@.TK2MSFTNGP10.phx.gbl...
>|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:EB9108DE-3804-46E8-A4FF-D31A58D48666@.microsoft.com...
> You can actually find an answer to your question using a calculator.
> Consider
> the number of rows inserted daily and you can predict when you 'run out of
> numbers'.
> My guess: not in your life-time - even with integer.
>
Probably true.
Which is why I am in the process of looking closer at my tables and making
the changes now, before I go much further.
Thanks,
Tom
> ML|||> But if they are not used in any of my indexes, would there be any
> performance problems?
If you are not indexing an identity value, then what are you using it for?
Identity value are pretty much only good as keys to go and fetch a single
row.

> Actually, I don't know if I would come close, but I don't want to build
> this system - which could take a couple of years to finish, only to find
> out that the numbers are filling up faster than I expected.
This is a valid point. I would suggest that you do some math to see how
long this will last. Like if you do 5 million new rows a year, you have
well over 100 years before you reach 500 million, so 400 years (400
*5million = 2 billion, right? I have worked a lot this w!) before you
will have to switch to use the negative values for the next 400 years :)
But it is up to you to decide just how many rows you will need and size
appropriately ;)

> If the issues aren't bad issues than I could just start making the changes
> and deal with the differences as I go along.
If you actually don't need them in indexes (UNIQUE and PRIMARY KEY
constraints use indexes also) then it really won't make very much difference
at all.

> I would definately be deleting the records as I go, but the identity field
> would still be increasing by one even if I dumped half the records.
True, but you might be able to start your values over to fill in the gaps
once you hit the max value if the data that gets deleted is in increasing
identity value. Especially if you are using this as a surrogate key. Usage
will dictate. If you post your structures and how these values are actually
being used it might help... Good luck :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23vCQUQimFHA.3568@.TK2MSFTNGP10.phx.gbl...
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:OWXwVvhmFHA.3312@.tk2msftngp13.phx.gbl...
> But if they are not used in any of my indexes, would there be any
> performance problems?
>
> Actually, I don't know if I would come close, but I don't want to build
> this system - which could take a couple of years to finish, only to find
> out that the numbers are filling up faster than I expected.
>
> I would definately be deleting the records as I go, but the identity field
> would still be increasing by one even if I dumped half the records.
>
> If the issues aren't bad issues than I could just start making the changes
> and deal with the differences as I go along.
> Thanks,
> Tom.
>
>|||On Fri, 5 Aug 2005 18:13:38 -0700, tshad wrote:

>"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
>news:OWXwVvhmFHA.3312@.tk2msftngp13.phx.gbl...
>But if they are not used in any of my indexes, would there be any
>performance problems?
Hi Tom,
Using a surrogate key without including it in an index is pointless.
The reason to add a surrogate key, is to serve as a surrogate for the
business key. This can be useful if other tables link to the table and
the business key is long - instead of duplicating the business key in
all refering tables, you duplicate the surrogate key. This of course
requires that the surrogate key is declared either PRIMARY KEY or
UNIQUE.
All PRIMARY KEY and UNIQUE constraints auto-generate an index. This is
not the case for FOREIGN KEY constraints, but in most cases, it makes
sense to manually add an index on the FOREIGN KEY column(s).
The surrogate key will be in the index for it's own PRIMARY KEY or
UNIQUE constraint, in all refering tables, and in all indexes you define
for the FOREIGN KEY constraints. If the PRIMARY KEY or UNIQUE for the
surrogate key is clustered, there'll be a copy of the surrogate key in
each nonclustered index for the same table as well.
Extra bytes in the surrogate key result in more space taken for all the
tables and all the indexes where the surrogate key is used. That means
that less rows and/or less index entries fit on a page, which will
affect the performance (more logical reads, and a lower cache-hit ratio,
resulting in more physical reads as well).
How much this will affect your total performance is hard to tell. Adding
two bytes to a 3000-byte row is not quite as significant as adding two
bytes to a 20-byte row.
Don't make your surrogate keys longer than they need to be, "just to be
on the safe side", as this WILL affect performance. On the other hand,
don't make your surrogate keys too short in order to save some
performance, as the amount of work it takes you to grow the keys later
is disproportional to the performance saved.
And if you use IDENTITY, then always start numbering at the lowest value
the datatype permits. The standard setting (starting at one) disregards
half the available values. Sure, you can roll over to the negatives once
the positivies are exhausted - but why would you?
Just my 0,02.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> And if you use IDENTITY, then always start numbering at the lowest value
> the datatype permits. The standard setting (starting at one) disregards
> half the available values. Sure, you can roll over to the negatives once
> the positivies are exhausted - but why would you?
This has always been hard to do in my experience, and I have fought with so
many programmers/dba's about this. I would agree that if you calulate that
you will need over 2 billion rows before like twice your expected system
life expectancy it might be possibl, but most of the time the large number
of digits are too much to try to type while the system is in it's infancy
and people are troubleshooting problems.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mkk9f19ss8ge7igqclbpmb4gvkmi2e1p85@.
4ax.com...
> On Fri, 5 Aug 2005 18:13:38 -0700, tshad wrote:
>
> Hi Tom,
> Using a surrogate key without including it in an index is pointless.
> The reason to add a surrogate key, is to serve as a surrogate for the
> business key. This can be useful if other tables link to the table and
> the business key is long - instead of duplicating the business key in
> all refering tables, you duplicate the surrogate key. This of course
> requires that the surrogate key is declared either PRIMARY KEY or
> UNIQUE.
> All PRIMARY KEY and UNIQUE constraints auto-generate an index. This is
> not the case for FOREIGN KEY constraints, but in most cases, it makes
> sense to manually add an index on the FOREIGN KEY column(s).
> The surrogate key will be in the index for it's own PRIMARY KEY or
> UNIQUE constraint, in all refering tables, and in all indexes you define
> for the FOREIGN KEY constraints. If the PRIMARY KEY or UNIQUE for the
> surrogate key is clustered, there'll be a copy of the surrogate key in
> each nonclustered index for the same table as well.
> Extra bytes in the surrogate key result in more space taken for all the
> tables and all the indexes where the surrogate key is used. That means
> that less rows and/or less index entries fit on a page, which will
> affect the performance (more logical reads, and a lower cache-hit ratio,
> resulting in more physical reads as well).
> How much this will affect your total performance is hard to tell. Adding
> two bytes to a 3000-byte row is not quite as significant as adding two
> bytes to a 20-byte row.
> Don't make your surrogate keys longer than they need to be, "just to be
> on the safe side", as this WILL affect performance. On the other hand,
> don't make your surrogate keys too short in order to save some
> performance, as the amount of work it takes you to grow the keys later
> is disproportional to the performance saved.
> And if you use IDENTITY, then always start numbering at the lowest value
> the datatype permits. The standard setting (starting at one) disregards
> half the available values. Sure, you can roll over to the negatives once
> the positivies are exhausted - but why would you?
> Just my ? 0,02.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||That helps a lot.
I will probably start moving some of my bigints to ints as everyone is
probably correct in the overkill causing a little more performance problems
just to "be safe".
As you say, the surrogate key would really have to be in the indexes or what
would the point of the surrogate be. I hadn't started putting my indexes
together yet, so didn't notice that I would have to use them in then
indexes. So it would be better for the surrogates to be smaller if
possible.
Thanks,
Tom

> Hi Tom,
> Using a surrogate key without including it in an index is pointless.
> The reason to add a surrogate key, is to serve as a surrogate for the
> business key. This can be useful if other tables link to the table and
> the business key is long - instead of duplicating the business key in
> all refering tables, you duplicate the surrogate key. This of course
> requires that the surrogate key is declared either PRIMARY KEY or
> UNIQUE.
> All PRIMARY KEY and UNIQUE constraints auto-generate an index. This is
> not the case for FOREIGN KEY constraints, but in most cases, it makes
> sense to manually add an index on the FOREIGN KEY column(s).
> The surrogate key will be in the index for it's own PRIMARY KEY or
> UNIQUE constraint, in all refering tables, and in all indexes you define
> for the FOREIGN KEY constraints. If the PRIMARY KEY or UNIQUE for the
> surrogate key is clustered, there'll be a copy of the surrogate key in
> each nonclustered index for the same table as well.
> Extra bytes in the surrogate key result in more space taken for all the
> tables and all the indexes where the surrogate key is used. That means
> that less rows and/or less index entries fit on a page, which will
> affect the performance (more logical reads, and a lower cache-hit ratio,
> resulting in more physical reads as well).
> How much this will affect your total performance is hard to tell. Adding
> two bytes to a 3000-byte row is not quite as significant as adding two
> bytes to a 20-byte row.
> Don't make your surrogate keys longer than they need to be, "just to be
> on the safe side", as this WILL affect performance. On the other hand,
> don't make your surrogate keys too short in order to save some
> performance, as the amount of work it takes you to grow the keys later
> is disproportional to the performance saved.
> And if you use IDENTITY, then always start numbering at the lowest value
> the datatype permits. The standard setting (starting at one) disregards
> half the available values. Sure, you can roll over to the negatives once
> the positivies are exhausted - but why would you?
> Just my ? 0,02.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:ObhzBZimFHA.708@.TK2MSFTNGP09.phx.gbl...
> If you are not indexing an identity value, then what are you using it for?
> Identity value are pretty much only good as keys to go and fetch a single
> row.
You're right. I would have to use the surrogate in the index.

>
> This is a valid point. I would suggest that you do some math to see how
> long this will last. Like if you do 5 million new rows a year, you have
> well over 100 years before you reach 500 million, so 400 years (400
> *5million = 2 billion, right? I have worked a lot this w!) before you
> will have to switch to use the negative values for the next 400 years :)
> But it is up to you to decide just how many rows you will need and size
> appropriately ;)
I will need to do that as I haven't really worked that out.

>
changes
> If you actually don't need them in indexes (UNIQUE and PRIMARY KEY
> constraints use indexes also) then it really won't make very much
difference
> at all.
>
field
> True, but you might be able to start your values over to fill in the gaps
> once you hit the max value if the data that gets deleted is in increasing
> identity value. Especially if you are using this as a surrogate key.
Usage
> will dictate. If you post your structures and how these values are
actually
> being used it might help... Good luck :)
Here is a stripped down version of some of my tables.
What I have is mulitple companies. Each company can have many users.
There would be many positions available. The JobTitle cannot be unique as
different companies could have the same JobTitles - so I need to use an
PositionID as my surrogate key and then index the JobTitle + PositionID.
There are many Applicants and each Applicant can have multiple positions
(ApplicantPosition table). And each ApplicantPosition table can have many
skills (ApplicantSkills table).
The tables could be set up as follows (except I will probably be changing
many of the bigints to ints):
CREATE TABLE [dbo].[Companies] (
[CompanyID] [bigint] IDENTITY (1, 1) NOT NULL Primary Key,
[Name] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Name_ID_Company_Idx on Companies(Name,CompanyID)
CREATE TABLE [dbo].[logon] (
[UserID] [bigint] IDENTITY (1, 1) NOT NULL Primary Key ,
[CompanyID] [bigint] NULL ,
[FirstName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateCreated] [datetime] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Name_ID_Logon_Idx on Companies(UserName,UserID)
CREATE NONCLUSTERED INDEX Name_Password_Logon_Idx on
Companies(UserName,Password)
CREATE TABLE [dbo].[Position] (
[CompanyID] [bigint] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PositionID] [int] IDENTITY (1, 1) NOT NULL Primary Key,
[CompanyID] [bigint] NULL ,
[JobTitle] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DatePosted] [datetime] NULL ,
[DateUpdated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Name_ID_Position_Idx on
Companies(JobTitle,PositionID)
CREATE TABLE [dbo].[Applicant] (
[CompanyID] [bigint] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UserID] [bigint] NOT NULL ,
[ApplicantID] [bigint] IDENTITY (1, 1) NOT NULL Primary Key,
[PositionID] [int] NOT NULL
[Name] [varchar] (45)
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Name_ID_Applicant_Idx on
Companies(Name,ApplicantID)
CREATE TABLE [dbo].[ApplicantPosition] (
[CompanyID] [bigint] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserID] [bigint] NOT NULL ,
[ApplicantPositionID] [bigint] IDENTITY (1, 1) NOT NULL
ApplicantPositionID,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Name_ID_ApplicantPosition_Idx on
ApplicantPosition(LastName,FirstName,App
licantPositionID)
CREATE TABLE [dbo].[ApplicantSkills] (
[CompanyID] [bigint] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UserID] [bigint] NULL ,
[ApplicantSkillsID] [bigint] IDENTITY (1, 1) NOT NULL Primary Key,
[ApplicantID] [bigint] NOT NULL ,
[Skill] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Level] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Skill_ID_ApplicantSkillsID on
ApplicantSkills(Skill,ApplicantSkillsID)
Thanks,
Tom

> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23vCQUQimFHA.3568@.TK2MSFTNGP10.phx.gbl...
to
of
(well,
tiny-
bit.
field
Stored
matching
bigint
changes
>
--
to
know
Stored
matching
>

Big table(?) or split between tables?

Hi Guys

I have an application that runs on several sites that has a table with 36 columns mostly ints och small varchars.

I currently have only one table that stores the data and five indexes and since the table on one location (and others soon) has about 18 million rows I have been trying to come up with a better solution (but only if needed, I dont think I have to tell you that I am a programmer and not an dba).
The db file size with all the indexes is more then 10gb, in it self is not an problem but is it a bad solution to have it that way?

The questions are:

Are there any big benefits if i split it into several smaller tables or even smaler databases and make the SPs that gets the data aware that say 2006 years data is in table a and so on?
Its quite important that there are fast SELECTS and that need is far more important then to decrease the size of the database file and so on.

How many rows is okay to have in one table (with 25 columns) before its too big?

Thanks in advance.

Best regards
Johan, Sweden.

CREATE TABLE [dbo].[Cdr](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Abandon] [varchar](7) NULL,
[Bcap] [varchar](2) NULL,
[BlId] [varchar](16) NULL,
[CallChg] [varchar](6) NULL,
[CallIdentifier] [uniqueidentifier] NULL,
[ChgInfo] [varchar](5) NULL,
[ClId] [varchar](16) NULL,
[CustNo] [smallint] NULL,
[Digits] [varchar](32) NULL,
[DigitType] [varchar](1) NULL,
[Dnis1] [varchar](6) NULL,
[Dnis2] [varchar](6) NULL,
[Duration] [int] NULL,
[FgDani] [varchar](13) NULL,
[HoundredHourDuration] [varchar](3) NULL,
[Name] [varchar](40) NULL,
[NameId] [int] NOT NULL,
[Npi] [varchar](2) NULL,
[OrigAuxId] [varchar](11) NULL,
[OrigId] [varchar](7) NULL,
[OrigMin] [varchar](16) NULL,
[Origten0] [varchar](3) NULL,
[RecNo] [int] NULL,
[RecType] [varchar](1) NOT NULL,
[Redir] [varchar](1) NULL,
[TerId] [varchar](7) NOT NULL,
[TermAuxId] [varchar](11) NULL,
[TermMin] [varchar](16) NULL,
[Termten0] [varchar](3) NULL,
[Timestamp] [datetime] NOT NULL,
[Ton] [varchar](1) NULL,
[Tta] [int] NULL,
[Twt] [int] NULL,
[DateValue] [int] NULL,
[TimeValue] [int] NULL,
[Level] [varchar](50) NOT NULL CONSTRAINT [DF_Cdr_Level] DEFAULT ('x:'),
CONSTRAINT [PK_Cdr] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]http://databases.about.com/od/specificproducts/a/firstnormalform.htm

http://en.wikipedia.org/wiki/First_normal_form|||Thanks for the quick reply.
Okay Ive read the articles, but the data in this table is records from a machine and are not duplicate, the name column is duplicated but I figured that it was a overkill to move one column out, all the other fields are diffrent from time to time. And all column always only has one value or null, so there is no need to have a parent-child relation to another table, or?

The acctual question was not if I would split the columns in multiple tables but if I should split the rows in multiple tables.|||What's the justification for splitting the table data into multiple tables of the exact structure?
EDIT: That should probably read - "What are your thoughts behind doing this?"|||it's called horizontal table partitioning and it can be used to create partitioned views and this works best to increase performance if you are doing it over multiple file groups on multiple disks.|||The question is does that increase performance in a single disk/filegroup scenario.

If I ask it like this, is it any problems related with a table with 18 million rows?
If not I am quite happy with the current solution, but if I were to split the data in several tables maby one for the last months data in one "active" table (since most querys are on the most recent data) and all the other in an "archive" table.

And If a split would be a good chooise, when to query a several months whats the best way to look in the two tables? Is it to have a SP that handels all this or should my data layer handle the access. Ex two SPs one for active and one for historic data and combine them with two calls if needed?|||If I ask it like this, is it any problems related with a table with 18 million rows?No - the number of rows is irrelevent. You need to see how things perform and act accordingly based on that. And although partitioning is an option there would be hundreds of things you'd want to consider first. Just to give you perspective, I work with non-partitioned tables with 1/2 billion rows and I expect a lot of the other posters here do also.

Also Don is right, your table would benefit from normalisation and not just first normal form. Remember that it is the number of pages read\ written from disk not the number of rows that count. So if you want a performance justification (rather than logical justification) for normalising then a normalised database will distribute your data across multiple tables, reducing the row size and leading to more rows per page. YMMV of course depending on your queries.

What sort of queries are running on it? Single row lookups or reports pulling in lots of data or a combination?|||Woah - this is probably terrible:
CONSTRAINT [PK_Cdr] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]Unless your rows get a lot of updates making the data in the columns larger after the initial insert then the fillfactor on a monotonically increasing clustered index should be approaching 100. All your clustered index leaf pages are 90% empty (depending on data modifications).

This is the sort of thing I mean by partitioning being one of your last options - lots of things to consider first :)|||Fancy scripting out the other indexes?|||Thanks guys seem like I have to really need to sit down and study some database architecture.

I currently only have one SP that handels the querys that always are used in reporting, soo when a row is in the db its "never" changes. But since this tables store telephone call data records that are collected at runtime, it gets alot of small inserts quite often. Say 10-200 records every five minutes. The insert interval is different at diffrent sites. But as a I said once its in there its "never" gonna change.
So the queries gets everyting from a couple of million rows to about 100 based on how long period your report is covering.

How would you suggest I should normalize this table then, or point me to some good resource where I can find out.

I'm sorry that this maby is newbe questions but thats really what I am so:)
Really appreciates your help.

Here are the rest of the indexes:

/****** Object: Index [IX_DateValue] Script Date: 11/21/2007 15:22:20 ******/
CREATE NONCLUSTERED INDEX [IX_DateValue] ON [dbo].[Cdr]
(
[DateValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

/****** Object: Index [IX_Level] Script Date: 11/21/2007 15:22:38 ******/
CREATE NONCLUSTERED INDEX [IX_Level] ON [dbo].[Cdr]
(
[Level] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

/****** Object: Index [IX_OrigId] Script Date: 11/21/2007 15:22:48 ******/
CREATE NONCLUSTERED INDEX [IX_OrigId] ON [dbo].[Cdr]
(
[OrigId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

/****** Object: Index [IX_TerId] Script Date: 11/21/2007 15:22:56 ******/
CREATE NONCLUSTERED INDEX [IX_TerId] ON [dbo].[Cdr]
(
[TerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

/****** Object: Index [IX_TimeValue] Script Date: 11/21/2007 15:23:08 ******/
CREATE NONCLUSTERED INDEX [IX_TimeValue] ON [dbo].[Cdr]
(
[TimeValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]|||Bump fillfactor for the clustered index to 100.

This is my fave db design article at the mo:
http://www.tonymarston.net/php-mysql/database-design.html

Please post your proc. If that is literally all you do with this table then we can tune it rather well. I suspect we will want to change your clustered index....|||I have some reading to do:)

This is the quite simple SP the table function SplitDelimitedVarChar takes a carchar and a split char and creates a table that I use to get the correct dates and [level]'s (called exchanges in the SP).

And the way that I have come up with this code is by testing diffrent solutions and this has been the fastest, and as you can se its a quite simple select query so the columsn [DateValue] and [TimeValue] are ints that are created at insert based on the DATETIME column [timestamp] and are just the int representation of the date and time, and is there because its easier.

CREATE PROCEDURE [dbo].[spStudio_Get_Cdr]
@.beginDate DATETIME,
@.endDate DATETIME,
@.beginTime INT,
@.endTime INT,
@.subscribers VARCHAR(MAX),
@.exchanges VARCHAR(MAX) = '1:'
AS
BEGIN
SET NOCOUNT ON;

DECLARE @.exch TABLE(Item Varchar(50))
INSERT INTO @.exch
SELECT Item FROM [SplitDelimitedVarChar] (@.exchanges, '|') ORDER BY Item

DECLARE @.subs TABLE(Item Varchar(19))
INSERT INTO @.subs
SELECT Item FROM [SplitDelimitedVarChar] (@.subscribers, '|') ORDER BY Item

SELECT [id]
,[Abandon]
,[Bcap]
,[BlId]
,[CallChg]
,[CallIdentifier]
,[ChgInfo]
,[ClId]
,[CustNo]
,[Digits]
,[DigitType]
,[Dnis1]
,[Dnis2]
,[Duration]
,[FgDani]
,[HoundredHourDuration]
,[Name]
,[NameId]
,[Npi]
,[OrigAuxId]
,[OrigId]
,[OrigMin]
,[Origten0]
,[RecNo]
,[RecType]
,[Redir]
,[TerId]
,[TermAuxId]
,[TermMin]
,[Termten0]
,[Timestamp]
,[Ton]
,[Tta]
,[Twt]
,[Level]
FROM
[dbo].[Cdr] AS C
INNER JOIN @.exch AS E
ON
C.[Level] = E.[Item]
WHERE
(C.[DateValue] BETWEEN FLOOR(CAST(@.beginDate AS FLOAT)) AND FLOOR(CAST(@.endDate AS FLOAT)))
AND
(C.[TimeValue] BETWEEN @.beginTime AND @.endTime)
AND
(EXISTS(SELECT * FROM @.subs WHERE [Item] = C.[OrigId])
OR
EXISTS(SELECT * FROM @.subs WHERE [Item] = C.[TerId]))

END

thanks in advance|||Changing from a datetime to 2 different float values is NOT easier than doing a datediff or a dateadd.|||Agreed.

Jeff has some pertinent comments here - at least two or three articles apply. http://weblogs.sqlteam.com/jeffs/category/283.aspx
More reading ;)|||The idea of your sproc then is that you don't search for a range of "from 2:00pm 1st of Jan 2007 to 4:00pm 8th of April" but "1st of Jan 2007 to 8th of April, between the times of 2:00pm to 4:00 pm only". Correct?|||Yes that correct, the report can be say the whole year of 2006 but only between 07:00 and 16:00, not 20060101 07:00 o 20061231 16:00.
Exactly as you described.

Is there a better way to get this result Im ofcource going to try it.|||You can use Display Estimated Execution Plan to analyze your query performance.

cheers
iful|||if you do decide to partition (which probably is not necessary) and you are on 2005, have a look at partition functions and partition schemes. you don't need to use partitioned views anymore.

This approach to horizontal partitioning is more convenient IMO because you only have one set of indexes, etc, to manage. It behaves as a single table spread among multiple filegroups.

Big problem connecting SQLEXPRESS within a vb net application

Hello.
I've an application that has to do a bunch of "SELECT" and INSERT into a
couple of tables. On my machine everything works fine, but if I try to
deploy the app on a VM for a test i have many probs.
The most common is "(Named Pipes Provider, error: 40 - Could not open a
connection to SQL
Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
type
'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
Catalog=Clienti;Integrated
Security=True", where "Clienti" is the DB, but i tried the string I found on
http://www.connectionstrings.com/?carrier=sqlserver2005 too.
It's being absurd...please give me an help...thanks...Ah, did you enable TCP/IP on the target server? Why are you using named
pipes? If you do, you must enable additional ports in the firewall (if there
is one).
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"THE JOK3R" <a@.a.a> wrote in message
news:%23GL$eDNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> Hello.
> I've an application that has to do a bunch of "SELECT" and INSERT into a
> couple of tables. On my machine everything works fine, but if I try to
> deploy the app on a VM for a test i have many probs.
> The most common is "(Named Pipes Provider, error: 40 - Could not open a
> connection to SQL
> Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
> type
> 'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
> connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
> Catalog=Clienti;Integrated
> Security=True", where "Clienti" is the DB, but i tried the string I found
> on http://www.connectionstrings.com/?carrier=sqlserver2005 too.
> It's being absurd...please give me an help...thanks...|||"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
news:OpGW39NjHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Ah, did you enable TCP/IP on the target server? Why are you using named
> pipes? If you do, you must enable additional ports in the firewall (if
> there is one).
I both did everything...enabled TCP/IP and Remote Connection, opened port
1431 default port...|||I suggest you walk through the list of issues posted in my blog--there is a
whitepaper there that discusses these
issues.http://betav.com/blog/billva/2006/0...com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"THE JOK3R" <a@.a.a> wrote in message
news:O8v9ZKOjHHA.4936@.TK2MSFTNGP03.phx.gbl...
>
> "William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
> news:OpGW39NjHHA.1624@.TK2MSFTNGP06.phx.gbl...
> I both did everything...enabled TCP/IP and Remote Connection, opened port
> 1431 default port...

Big problem connecting SQLEXPRESS within a vb net application

Hello.
I've an application that has to do a bunch of "SELECT" and INSERT into a
couple of tables. On my machine everything works fine, but if I try to
deploy the app on a VM for a test i have many probs.
The most common is "(Named Pipes Provider, error: 40 - Could not open a
connection to SQL
Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
type
'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
Catalog=Clienti;Integrated
Security=True", where "Clienti" is the DB, but i tried the string I found on
http://www.connectionstrings.com/?carrier=sqlserver2005 too.
It's being absurd...please give me an help...thanks...
Ah, did you enable TCP/IP on the target server? Why are you using named
pipes? If you do, you must enable additional ports in the firewall (if there
is one).
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"THE JOK3R" <a@.a.a> wrote in message
news:%23GL$eDNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> Hello.
> I've an application that has to do a bunch of "SELECT" and INSERT into a
> couple of tables. On my machine everything works fine, but if I try to
> deploy the app on a VM for a test i have many probs.
> The most common is "(Named Pipes Provider, error: 40 - Could not open a
> connection to SQL
> Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
> type
> 'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
> connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
> Catalog=Clienti;Integrated
> Security=True", where "Clienti" is the DB, but i tried the string I found
> on http://www.connectionstrings.com/?carrier=sqlserver2005 too.
> It's being absurd...please give me an help...thanks...
|||"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
news:OpGW39NjHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Ah, did you enable TCP/IP on the target server? Why are you using named
> pipes? If you do, you must enable additional ports in the firewall (if
> there is one).
I both did everything...enabled TCP/IP and Remote Connection, opened port
1431 default port...
sql

Big Mistake? - Indexes with includes on partition tables

I recently tried adding include columns to two indexes, one on each of two
partitioned tables. The tables are modest-sized, fact tables used for
aggregation. Each week, we slide the partitions.
The slide process is normally very fast. However, after adding include
columns to the indexes, the command to split the range in the partition
functions resulted in what appears to be runaway processes that gobbled up
system resources and maxxed out the transaction log. In fact, even after
emptying the tables of data and dropping the indexes, this happened. It was
only after I deleted the index partition scheme that the problem went away.
So... is there a problem with creating indexes with include columns on
partitioned tables where the partitions will slide? Is there something
exponential that goes on?
Thanks
Thanks,
CGW
I can't think of why simply including columns would show down the split.
Can you please post the DDL, including partition schemes and functions?
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>I recently tried adding include columns to two indexes, one on each of two
> partitioned tables. The tables are modest-sized, fact tables used for
> aggregation. Each week, we slide the partitions.
> The slide process is normally very fast. However, after adding include
> columns to the indexes, the command to split the range in the partition
> functions resulted in what appears to be runaway processes that gobbled up
> system resources and maxxed out the transaction log. In fact, even after
> emptying the tables of data and dropping the indexes, this happened. It
> was
> only after I deleted the index partition scheme that the problem went
> away.
> So... is there a problem with creating indexes with include columns on
> partitioned tables where the partitions will slide? Is there something
> exponential that goes on?
> Thanks
> --
> Thanks,
> CGW
|||That could ba a lot of work for both of us. Are you just mildly curious, or
do you think you could really puzzle it out?
Thanks,
CGW
"Dan Guzman" wrote:

> I can't think of why simply including columns would show down the split.
> Can you please post the DDL, including partition schemes and functions?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>
|||> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
I'm quite interested in this problem and would like to try to help you
figure out what's going on. However, I'll need DDL to do so since I can't
think of anything obvious based on the information given. The only causes I
can think of isn't related to included columns.
One gotcha I've seen is that splitting a partition function will change all
the related partition schemes. If you have multiple tables using the same
scheme, all corresponding partitions will be split. I've run into an issue
where a staging table (with same scheme) wasn't empty when the function was
split so a lot of data needed to be moved in the staging table partitions
even though no data needed to be moved into the newly created partition of
the main table.
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:00984490-298D-43AD-B820-BAA95680012F@.microsoft.com...[vbcol=seagreen]
> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
> --
> Thanks,
> CGW
>
> "Dan Guzman" wrote:

Big Mistake? - Indexes with includes on partition tables

I recently tried adding include columns to two indexes, one on each of two
partitioned tables. The tables are modest-sized, fact tables used for
aggregation. Each week, we slide the partitions.
The slide process is normally very fast. However, after adding include
columns to the indexes, the command to split the range in the partition
functions resulted in what appears to be runaway processes that gobbled up
system resources and maxxed out the transaction log. In fact, even after
emptying the tables of data and dropping the indexes, this happened. It was
only after I deleted the index partition scheme that the problem went away.
So... is there a problem with creating indexes with include columns on
partitioned tables where the partitions will slide? Is there something
exponential that goes on?
Thanks
--
Thanks,
CGWI can't think of why simply including columns would show down the split.
Can you please post the DDL, including partition schemes and functions?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>I recently tried adding include columns to two indexes, one on each of two
> partitioned tables. The tables are modest-sized, fact tables used for
> aggregation. Each week, we slide the partitions.
> The slide process is normally very fast. However, after adding include
> columns to the indexes, the command to split the range in the partition
> functions resulted in what appears to be runaway processes that gobbled up
> system resources and maxxed out the transaction log. In fact, even after
> emptying the tables of data and dropping the indexes, this happened. It
> was
> only after I deleted the index partition scheme that the problem went
> away.
> So... is there a problem with creating indexes with include columns on
> partitioned tables where the partitions will slide? Is there something
> exponential that goes on?
> Thanks
> --
> Thanks,
> CGW|||That could ba a lot of work for both of us. Are you just mildly curious, or
do you think you could really puzzle it out?
--
Thanks,
CGW
"Dan Guzman" wrote:
> I can't think of why simply including columns would show down the split.
> Can you please post the DDL, including partition schemes and functions?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
> >I recently tried adding include columns to two indexes, one on each of two
> > partitioned tables. The tables are modest-sized, fact tables used for
> > aggregation. Each week, we slide the partitions.
> >
> > The slide process is normally very fast. However, after adding include
> > columns to the indexes, the command to split the range in the partition
> > functions resulted in what appears to be runaway processes that gobbled up
> > system resources and maxxed out the transaction log. In fact, even after
> > emptying the tables of data and dropping the indexes, this happened. It
> > was
> > only after I deleted the index partition scheme that the problem went
> > away.
> >
> > So... is there a problem with creating indexes with include columns on
> > partitioned tables where the partitions will slide? Is there something
> > exponential that goes on?
> >
> > Thanks
> > --
> > Thanks,
> >
> > CGW
>|||> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
I'm quite interested in this problem and would like to try to help you
figure out what's going on. However, I'll need DDL to do so since I can't
think of anything obvious based on the information given. The only causes I
can think of isn't related to included columns.
One gotcha I've seen is that splitting a partition function will change all
the related partition schemes. If you have multiple tables using the same
scheme, all corresponding partitions will be split. I've run into an issue
where a staging table (with same scheme) wasn't empty when the function was
split so a lot of data needed to be moved in the staging table partitions
even though no data needed to be moved into the newly created partition of
the main table.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:00984490-298D-43AD-B820-BAA95680012F@.microsoft.com...
> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
> --
> Thanks,
> CGW
>
> "Dan Guzman" wrote:
>> I can't think of why simply including columns would show down the split.
>> Can you please post the DDL, including partition schemes and functions?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "CGW" <CGW@.discussions.microsoft.com> wrote in message
>> news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>> >I recently tried adding include columns to two indexes, one on each of
>> >two
>> > partitioned tables. The tables are modest-sized, fact tables used for
>> > aggregation. Each week, we slide the partitions.
>> >
>> > The slide process is normally very fast. However, after adding include
>> > columns to the indexes, the command to split the range in the partition
>> > functions resulted in what appears to be runaway processes that gobbled
>> > up
>> > system resources and maxxed out the transaction log. In fact, even
>> > after
>> > emptying the tables of data and dropping the indexes, this happened. It
>> > was
>> > only after I deleted the index partition scheme that the problem went
>> > away.
>> >
>> > So... is there a problem with creating indexes with include columns on
>> > partitioned tables where the partitions will slide? Is there something
>> > exponential that goes on?
>> >
>> > Thanks
>> > --
>> > Thanks,
>> >
>> > CGWsql

Big Mistake? - Indexes with includes on partition tables

I recently tried adding include columns to two indexes, one on each of two
partitioned tables. The tables are modest-sized, fact tables used for
aggregation. Each week, we slide the partitions.
The slide process is normally very fast. However, after adding include
columns to the indexes, the command to split the range in the partition
functions resulted in what appears to be runaway processes that gobbled up
system resources and maxxed out the transaction log. In fact, even after
emptying the tables of data and dropping the indexes, this happened. It was
only after I deleted the index partition scheme that the problem went away.
So... is there a problem with creating indexes with include columns on
partitioned tables where the partitions will slide? Is there something
exponential that goes on?
Thanks
--
Thanks,
CGWI can't think of why simply including columns would show down the split.
Can you please post the DDL, including partition schemes and functions?
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>I recently tried adding include columns to two indexes, one on each of two
> partitioned tables. The tables are modest-sized, fact tables used for
> aggregation. Each week, we slide the partitions.
> The slide process is normally very fast. However, after adding include
> columns to the indexes, the command to split the range in the partition
> functions resulted in what appears to be runaway processes that gobbled up
> system resources and maxxed out the transaction log. In fact, even after
> emptying the tables of data and dropping the indexes, this happened. It
> was
> only after I deleted the index partition scheme that the problem went
> away.
> So... is there a problem with creating indexes with include columns on
> partitioned tables where the partitions will slide? Is there something
> exponential that goes on?
> Thanks
> --
> Thanks,
> CGW|||That could ba a lot of work for both of us. Are you just mildly curious, or
do you think you could really puzzle it out?
--
Thanks,
CGW
"Dan Guzman" wrote:

> I can't think of why simply including columns would show down the split.
> Can you please post the DDL, including partition schemes and functions?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>|||> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
I'm quite interested in this problem and would like to try to help you
figure out what's going on. However, I'll need DDL to do so since I can't
think of anything obvious based on the information given. The only causes I
can think of isn't related to included columns.
One gotcha I've seen is that splitting a partition function will change all
the related partition schemes. If you have multiple tables using the same
scheme, all corresponding partitions will be split. I've run into an issue
where a staging table (with same scheme) wasn't empty when the function was
split so a lot of data needed to be moved in the staging table partitions
even though no data needed to be moved into the newly created partition of
the main table.
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:00984490-298D-43AD-B820-BAA95680012F@.microsoft.com...[vbcol=seagreen]
> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
> --
> Thanks,
> CGW
>
> "Dan Guzman" wrote:
>

Big Log file that won't shrink....

I have a fairly simple DB, One Log file, one data file, its got half a dozen tables in it that are used for a web based app that allows users to look each other up (think online telephone directory with a few extra bits and you are there), there's a data feed that adds new lines each day as users data changes or new people get added. But this is a couple of hundred or so lines a day, the tables hold about 40,000 data lines as we never delete data just retire it.

The problem is the main data file is about 200Mb, the Log file however is 3.8Gb. Now we did import and re-import various bits initially and clear down tables and such. But I don't understand why i can't shrink the log file. I've tried everything I can think of. I've even detached and re-attached the DB. Still no joy.

The file size/usage is

data file 200Mb size in use 117Mb

log file 3749.99Mb size in use 3719.89Mb

Anyone have any suggestions for things to look at?

many thanks

Steve

Hi,

look here:

http://www.aspfaq.com/show.asp?id=2471

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Bi-Directional Updating Databases on same SQL server

I am trying to separate two applications sharing the same database and a few tables(like t_employee) in a phased approach. How can I best setup a bi-directional update of this t_employee table when updated by either application?
Thanks,
Memphodave
Message posted via http://www.sqlmonster.com
Have a look at bi-directional transactional replication.
it is covered in SP3 books online.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"David Walpole via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be07a9381051493195afb35c1f13161d@.SQLMonster.c om...
> I am trying to separate two applications sharing the same database and a
few tables(like t_employee) in a phased approach. How can I best setup a
bi-directional update of this t_employee table when updated by either
application?
> Thanks,
> Memphodave
> --
> Message posted via http://www.sqlmonster.com

Bi-Directional replication without changing scema on subscriber

Hello - I am a newbie to Replication.
Can I setup Bi-Directional replication without introducing the guid column
on the subscriber? All the tables have Primary Keys.
Thanks.
-A
Adam - yes - columns are only added for updatable subsriptions and merge.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

BI Accelerator Error Table Question

I just have a question about interpreting the error tables that BI Accelerator creates. In a couple of the tables in the Staging database I have rows that are rejected during the update process. There is a field called Error_Reason that says 'Missing' for each row. How can I tell which column is actually 'Missing' in the row?

Thanks.

Best Regards,
JimBI Accelerator

That's a new one...what is it?

Better way to build a stored proc for an INSERT...

I've built a stored procedure where I'm inserting a row into two tables.
Both tables have a number of columns - and so I have to pass a rather larger
number of parameters to the stored proc. Like follows
INSERT INTO MyTable1 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm1, @.cParm2,
... @.cParm25)
INSERT INTO MyTable2 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm26,
@.cParm27, ... @.cParm50)
For any one row, however, at least a third of the columns are going to be
NULL, based on the value of one of the columns (a category column).
There's no opportunity to modify the table structure - it is what it is.
What I have "works", but I'm curious if there's a way that doesn't involve
as many parameters.
Thanks,
KevinKevin@.test.com wrote:
> I've built a stored procedure where I'm inserting a row into two
> tables.
> Both tables have a number of columns - and so I have to pass a rather
> larger number of parameters to the stored proc. Like follows
> INSERT INTO MyTable1 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm1,
> @.cParm2, ... @.cParm25)
> INSERT INTO MyTable2 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm26,
> @.cParm27, ... @.cParm50)
>
> For any one row, however, at least a third of the columns are going
> to be NULL, based on the value of one of the columns (a category
> column).
> There's no opportunity to modify the table structure - it is what it
> is. What I have "works", but I'm curious if there's a way that
> doesn't involve as many parameters.
>
> Thanks,
> Kevin
Write separate stored procedures for each "insert" type. So, let's say
your table has three logical implementations (design-issues aside), you
can write three separate insert SPs that only require the user pass
those that are asked.
The other option is to use defaults on the parameters, so if they are
not passed they default to an appropriate value:
Create Proc Test
@.Param1 INT = NULL
@.Param2 INT = NULL
as
Exec dbo.Test @.Param2 = 5
Exec dbo.Test @.Param1 = 3
Exec dbo.Test 1, 3
Exec dbo.Test
You may have to add some validation to the SP in the case where a user
leaves out a logically incorrect number of columns.
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks!...you've given me some good ideas to seriously consider, especially
having 3 stored procs.
Kevin