Schema Suggestions

Nov 20, 2007

I am working with an application currently that stores the majority of its data in the following schema.

factid1 int,
factid2 int,
factid3 int,
factid4 int,
Value decimal 14,4

To extract data from this table we are using a 4 table join to each of the factid's

Our where clause in this query is based on (where factid1 = something)

So we have a composite clustered index led by factid1.

Our plan is to reduce the size of this table by introducing the kind of schema, we would like to introduce this to keep the table size to a minimum and hopefully increase the performance of our extracts from this table.

factid4 int
intersectid int
value decimal 14,4

And then the intersect table with fact2,fact2,fact3 combinations

factid1 int
factid2 int
factid3 int
intersectid

This kind of schema reduces the size of this table substantially but performance of our extract is very poor.

Does anyone have any suggestions on schemas that will give us high performance?

Or does anyone think that the original schema will outperform any alternative schema.

View 4 Replies

I've Build A Database Schema-Open For Suggestions

Apr 18, 2007

Hello Guys:I have included the schema that i build for sql serve 2005 below, My concern is with creating views and cursors.I open for any suggestions. Please try out the scheme;

/*
Created4/5/2007
Modified4/14/2007
Project
Model
Company
Author
Version
DatabaseMS SQL 2005
*/

use master
if exists (select name from sys.databases where name='clubDataBase')
drop database clubdataBase
go
create database clubDataBase
go
use clubDatabase

Create table [Role]
(
[Role_Id] Char(3) NOT NULL,
[Role_Type] Char(30) NOT NULL,
[Description] Char(30) NOT NULL,
Primary Key ([Role_Id])
)
go

Create table [Employee]
(
[employee_id] Char(20) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[Role_Id] Char(3) NOT NULL,
[Manager_Id] Char(20) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[HireDate] Datetime NOT NULL,
[PayRate] Real NOT NULL,
[Status] Varchar(30) NOT NULL,
[SSN] Char(12) NOT NULL,
[Fname] Char(30) NOT NULL,
[Lname] Char(30) NOT NULL,
[Gender] Char(1) NOT NULL,
[DOB] Datetime NOT NULL,
[LastUpdated] Datetime NOT NULL,
Primary Key ([employee_id])
)
go

Create table [Address]
(
[Add_Id] Char(10) NOT NULL,
[AddressLine1] Varchar(30) NOT NULL,
[AddressLine2] Varchar(30) NULL,
[City] Varchar(20) NOT NULL,
[State] Char(2) NOT NULL,
[ZipCode] Char(10) NOT NULL,
[Region] Char(20) NOT NULL,
Primary Key ([Add_Id])
)
go

Create table [Club]
(
[Club_Id] Char(5) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[RegionId] Char(5) NOT NULL,
[ClubName] Varchar(30) NOT NULL,
[PhoneNumber] Char(13) NOT NULL,
[FaxNumber] Char(13) NULL,
[Email] Varchar(30) NOT NULL,
[WebSite] Varchar(30) NOT NULL,
[Description] Varchar(30) NOT NULL,
Primary Key ([Club_Id])
)
go

Create table [Equip_Inven]
(
[Equip_Inven_Id] Char(5) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Equip_Id] Char(5) NOT NULL,
[Purchase_Date] Datetime NOT NULL,
[Purchase_Cost] Money NULL,
Primary Key ([Equip_Inven_Id])
)
go

Create table [Equipment]
(
[Equip_Id] Char(5) NOT NULL, UNIQUE ([Equip_Id]),
[Equip_Name] Varchar(30) NOT NULL,
[Equip_Type] Varchar(20) NOT NULL,
[Equip_Desc] Varchar(30) NULL,
Primary Key ([Equip_Id])
)
go

Create table [CustomerClass]
(
[Club_Id] Char(5) NOT NULL,
[Class_Id] Char(5) NOT NULL,
[StartDate] Datetime NOT NULL,
[Member_Id] Char(10) NOT NULL,
[MemClassFees] Money NOT NULL,
[Description] Varchar(30) NULL,
Primary Key ([Club_Id],[Class_Id],[StartDate],[Member_Id])
)
go

Create table [Classes]
(
[Class_Id] Char(5) NOT NULL,
[ClassName] Varchar(30) NOT NULL,
[ClassDescription] Varchar(30) NOT NULL,
Primary Key ([Class_Id])
)
go

Create table [Member]
(
[Member_Id] Char(10) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Plan_Id] Char(5) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[TotalFees] Money NOT NULL,
[MemberFname] Char(20) NOT NULL,
[MemberLname] Char(20) NOT NULL,
[Phonenumber] Char(12) NOT NULL,
[DOB] Datetime NOT NULL,
[Gender] Char(1) NOT NULL,
[Email] Char(20) NULL,
[LastUpdated] Datetime NOT NULL,
Primary Key ([Member_Id])
)
go

Create table [Health_Info]
(
[Member_Id] Char(10) NOT NULL,
[Doctor_Name] Varchar(20) NOT NULL,
[Allergy] Varchar(30) NULL,
[Diseases] Varchar(30) NULL,
[Emergency_Contact1] Char(20) NOT NULL,
[Emergency_Phone1] Char(12) NOT NULL,
[Contact_Relation1] Char(20) NOT NULL,
[Emergency_Contact2] Char(20) NULL,
[Emergency_Phone2] Char(12) NULL,
[Contact_Relation2] Char(20) NULL,
Primary Key ([Member_Id])
)
go

Create table [ClubClasses]
(
[StartDate] Datetime NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Class_Id] Char(5) NOT NULL,
[employee_id] Char(20) NOT NULL,
[ClassAgeRange] Char(10) NULL,
[ClubClassCost] Money NOT NULL,
[ClubClassSchedule] Varchar(30) NOT NULL,
Primary Key ([StartDate],[Club_Id],[Class_Id])
)
go

Create table [Plans]
(
[Plan_Id] Char(5) NOT NULL,
[SuggestedPrice] Money NOT NULL,
[PlanName] Varchar(30) NOT NULL,
[PlanDuration] Varchar(20) NOT NULL,
[Description] Varchar(30) NOT NULL,
Primary Key ([Plan_Id])
)
go

Create table [ClubPlans]
(
[Club_Id] Char(5) NOT NULL,
[Plan_Id] Char(5) NOT NULL,
[Discount] Numeric(2,2) NULL,
[Description] Varchar(30) NULL,
Primary Key ([Club_Id],[Plan_Id])
)
go

Create table [MemberClubUsage]
(
[StartDate] Datetime NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Member_Id] Char(10) NOT NULL,
[EndDate] Datetime NOT NULL,
Primary Key ([StartDate],[Club_Id],[Member_Id])
)
go

Create table [Facility]
(
[Facility_Id] Char(5) NOT NULL, UNIQUE ([Facility_Id]),
[FacilityName] Varchar(30) NOT NULL,
[FacilityType] Varchar(30) NOT NULL,
[Description] Varchar(30) NULL,
Primary Key ([Facility_Id])
)
go

Create table [FacilityUsage]
(
[StartDate] Char(1) NOT NULL,
[Member_Id] Char(10) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Facility_Id] Char(5) NOT NULL,
[EndDate] Datetime NULL,
Primary Key ([StartDate],[Member_Id])
)
go

Create table [ClubFacilites]
(
[Club_Id] Char(5) NOT NULL,
[Facility_Id] Char(5) NOT NULL,
[OpenDate] Datetime NOT NULL,
[CloseDate] Datetime NOT NULL,
[Description] Varchar(30) NULL,
Primary Key ([Club_Id],[Facility_Id])
)
go

Create table [Equip_Maintanence]
(
[Equip_Maintanence_Id] Char(5) NOT NULL,
[Equip_Inven_Id] Char(5) NOT NULL,
[Main_Date] Datetime NULL,
[Main_Cost] Money NULL,
[Comment] Varchar(30) NULL,
Primary Key ([Equip_Maintanence_Id])
)
go

Create table [Guest]
(
[GuestId] Char(5) NOT NULL,
[Member_Id] Char(10) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[GuestName] Char(20) NOT NULL,
[GuestDOB] Datetime NOT NULL,
Primary Key ([GuestId])
)
go

Create table [RegionalOffice]
(
[RegionId] Char(5) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[RegionName] Nvarchar(30) NOT NULL,
Primary Key ([RegionId])
)
go

Alter table [Employee] add foreign key([Role_Id]) references [Role] ([Role_Id]) on update no action on delete no action
go
Alter table [Employee] add foreign key([Manager_Id]) references [Employee] ([employee_id]) on update no action on delete no action
go
Alter table [ClubClasses] add foreign key([employee_id]) references [Employee] ([employee_id]) on update no action on delete no action
go
Alter table [Employee] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Club] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Member] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Guest] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [RegionalOffice] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Equip_Inven] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [Employee] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [ClubClasses] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [ClubPlans] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [MemberClubUsage] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [ClubFacilites] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [Equip_Maintanence] add foreign key([Equip_Inven_Id]) references [Equip_Inven] ([Equip_Inven_Id]) on update no action on delete no action
go
Alter table [Equip_Inven] add foreign key([Equip_Id]) references [Equipment] ([Equip_Id]) on update no action on delete no action
go
Alter table [ClubClasses] add foreign key([Class_Id]) references [Classes] ([Class_Id]) on update no action on delete no action
go
Alter table [CustomerClass] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [MemberClubUsage] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [FacilityUsage] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [Health_Info] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [Guest] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [CustomerClass] add foreign key([StartDate],[Club_Id],[Class_Id]) references [ClubClasses] ([StartDate],[Club_Id],[Class_Id]) on update no action on delete no action
go
Alter table [ClubPlans] add foreign key([Plan_Id]) references [Plans] ([Plan_Id]) on update no action on delete no action
go
Alter table [Member] add foreign key([Club_Id],[Plan_Id]) references [ClubPlans] ([Club_Id],[Plan_Id]) on update no action on delete no action
go
Alter table [ClubFacilites] add foreign key([Facility_Id]) references [Facility] ([Facility_Id]) on update no action on delete no action
go
Alter table [FacilityUsage] add foreign key([Club_Id],[Facility_Id]) references [ClubFacilites] ([Club_Id],[Facility_Id]) on update no action on delete no action
go
Alter table [Club] add foreign key([RegionId]) references [RegionalOffice] ([RegionId]) on update no action on delete no action
go

--create procedures and views
--the procedure to randomly add members visit the club
create procedure getMemberClubUsage
as
declare @memID char(10),
@clubID char(10)
declare @begDate datetime
declare mem_club_cursor cursor
--randamly to add the member to visit clubs
for
select member_ID, c.club_ID
from member, club c
open mem_club_cursor
fetch mem_club_cursor into
@memID, @clubID

while @@fetch_status=0
begin
set @begDate=dateadd(day, rand()*-365, getdate())
insert into memberClubUsage
select @begDate, @clubID, @memID, dateadd(minute, rand()*300, @begDate)
fetch mem_club_cursor into
@memID, @clubID
end

close mem_club_cursor
deallocate mem_club_cursor

go
--the procedure randomly add the member to classes
create procedure getMemberClubClass
as
insert into customerClass (startDate, club_ID, class_ID, Member_ID, memclassFees)
select startDate, c.club_ID, class_ID, Member_ID, 150
from member m, clubClasses c
go

create view MembershipClubRegion_VW
as
select member_ID, memberFName, memberLName, C.Club_ID, C.ClubName, c.RegionID, r.RegionName
from member m join club c on m.club_ID=c.Club_ID
join regionaloffice r on c.regionID=r.regionID
go

create view VistorbyMonthClub_VW
as
select clubName,
datename(month, startdate)+'-'+datename(year, startdate) 'Month',
count(*) TotalVistors
from club c join memberclubusage mc on c.club_ID=mc.club_ID
group by clubName, datename(month, startdate)+'-'+datename(year, startdate)

go

create view classClub_View
as
select c.class_ID,
className,
clubName,
startDate
from classes c join clubClasses cc on c.class_ID=cc.class_ID
join club cl on cc.club_ID=cl.club_ID
go
--add testing data into database
--insert data to table Address
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0001', '100 Washington St', 'Salem', 'NH', '01123', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0002', '180 Porland Ave', 'Providence', 'RI', '03123', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0003', '36 Huntington Ave', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0004', '45 Marine St', 'August', 'ME', '31245', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0005', '400 Tel St', 'Vermont', 'VT', '11234', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0006', '100 Abc Ave', 'George Town', 'CT', '1111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('B0001', '180 Matthew Ave', 'Worchest', 'NH', '01145', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('B0002', '1000 Elm St', 'Braintree', 'MA', '02184', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0001', '24 Edwin St', 'Quincy', 'MA', '02171', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0002', '57 Sterling Street', 'Malden', 'MA', '02148', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0003', '26 Royal St', 'Quincy', 'MA', '02171', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0004', '439 Newport Ave', 'Quincy', 'MA', '02171', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0005', '41 Old Brattleboro', 'August', 'ME', '31245', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0006', '110 N Central Ave', 'Montpelier', 'VT', '11234', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0007', '67 Marjoril Rd', 'George Town', 'CT', '1111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0008', '1 Holden Row #2', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0009', '67 Marjoril Rd', 'Braintree', 'MA', '02421', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0010', '253 Cambridge St Apt#2', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0011', '30 Hill St', 'Randolph', 'MA', '02134', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0012', '87 Ocean St', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0013', '7A Park Terrace', 'Boston', 'MA', '02112', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0014', '8 FoxRun #12', 'Boston', 'MA', '02113', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0015', '45 Upland Road', 'Boston', 'MA', '02114', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0016', '378 Medford St', 'Boston', 'MA', '02115', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0017', '46 Wheatland St', 'Boston', 'MA', '02116', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0018', '177 North Street', 'Boston', 'MA', '02117', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0019', '280 Breadway #4', 'Boston', 'MA', '02118', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0020', '106 Chester St', 'Boston', 'MA', '02119', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0021', '45 Upland Rd', 'Boston', 'MA', '02120', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0022', '47 Malvern St', 'Boston', 'MA', '02121', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0023', '49 Dwight St #1', 'Boston', 'MA', '02122', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0024', '97 LAWN STREET', 'Boston', 'MA', '02123', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0025', '418 Revere Beach Pkwy #74', 'Boston', 'MA', '02124', 'NE')
go
--insert data to table RegionalOffice
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0001', 'C0001', 'New Hampshire')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0002', 'C0002', 'Rhode Island')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0003', 'C0003', 'Massachussetts')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0004', 'C0004', 'Marine')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0005', 'C0005', 'Vermont')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0006', 'C0006', 'Connecticut')
go
--insert data to table Club
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0001', 'C0001', 'R0001', 'NH CLub1', '8952645456', 'mail@ClubNH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0002', 'B0001', 'R0001', 'NH CLub2', '8953566889', 'mail@ClubNH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0003', 'C0003', 'R0003', 'MA Club1', '6174561787', 'mail@MANH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0004', 'B0002', 'R0003', 'MA Club2', '6175688989', 'mail@MANH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0005', 'C0002', 'R0002', 'RI Club1', '4264567898', 'mail@ClubRI', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0006', 'C0004', 'R0004', 'Marine Club 1', '8915678989', 'mail@clubME', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0007', 'C0005', 'R0005', 'Vermont Club1', '7325641234', 'mail@clubVT', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0008', 'C0006', 'R0006', 'Connecticut Club1', '7888989999', 'mail@clubCT', 'http://www.Club1NH.org', 'club')
go
--insert data to table Plans
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0001', 156.38, 'Family Plan', '1 year', 'plan')
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0002', 90, 'Single Plan', '1 Year', 'plan')
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0003', 70, 'Senior Plan', '1 Year', 'plan')
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0004', 30, 'promote plan', '1 year', 'plan')
go
--insert data to table ClubPlans
insert into ClubPlans ( Club_id, Plan_id) values ('C0001', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0002', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0003', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0004', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0005', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0006', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0007', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0008', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0001', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0002', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0003', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0004', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0005', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0006', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0007', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0008', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0003', 'P0004')
go
--insert data to table Classes
insert into Classes ( Class_id, className, classDescription) values ('A0001', 'Beginning Yoga', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0002', 'Advance Yoga', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0003', 'Swimming', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0004', 'Dancing', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0005', 'Introduction to Tennis', 'desc')
go
--insert data to table Role
insert into Role ( Role_id, Role_Type, Description) values ('R01', 'Teacher', 'Class teacher')
insert into Role ( Role_id, Role_Type, Description) values ('R02', 'Manager', 'club manager')
go
--insert data to table Employee
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0001', 'A0001', 'R01', 'E0001', 'C0001', '1/1/1999', 15, ' ', '123456789', 'John', 'Joyce', 'M', '1/3/1968', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0002', 'A0002', 'R02', 'E0001', 'C0002', '12/1/2004', 25, ' ', '123456789', 'MacDonld', 'Harris', 'M', '10/12/1975', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0003', 'A0003', 'R02', 'E0001', 'C0003', '3/1/2006', 20, ' ', '123456789', 'Joane', 'Li', 'F', '11/5/1960', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0004', 'A0004', 'R02', 'E0001', 'C0004', '5/1/2006', 22, ' ', '123456789', 'Tony', 'Green', 'M', '8/12/1970', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0005', 'A0005', 'R02', 'E0001', 'C0005', '1/1/2007', 20, ' ', '123456789', 'Lisa', 'White', 'F', '11/10/1975', '4/18/2007')
go
--insert data to table ClubClasses
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('1/1/2007', 'C0001', 'A0001', 'E0002', 1500, 'Mon, Fri 7:30PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('2/1/2007', 'C0002', 'A0002', 'E0003', 1500, 'Mon, Fri 7:30PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('3/1/2007', 'C0003', 'A0003', 'E0004', 1500, 'Tue, Th 8:00PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('4/1/2007', 'C0004', 'A0004', 'E0005', 1500, 'Tue, Th 8:00PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('5/1/2007', 'C0005', 'A0005', 'E0002', 1500, 'Wed, Fri 7:00PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('4/5/2007', 'C0006', 'A0004', 'E0004', 1500, 'Sat, 8:00AM')
go
--insert data to table Member
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0001', 'C0001', 'P0001', 'A0006', 153.38, 'John', 'He', '7812671567', '1/15/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0002', 'C0002', 'P0001', 'A0007', 153.38, 'Joane', 'Good', '5678945689', '1/16/1975', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0003', 'C0003', 'P0001', 'A0008', 153.38, 'Sharon', 'Spears', '8123456789', '1/17/1985', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0004', 'C0004', 'P0001', 'A0009', 153.38, 'Sandy', 'Green', '9874567898', '2/18/1965', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0005', 'C0005', 'P0001', 'A0010', 153.38, 'Lisa', 'White', '7185642356', '1/19/1967', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0006', 'C0006', 'P0001', 'A0011', 153.38, 'William', 'Black', '5689741235', '1/20/1973', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0007', 'C0007', 'P0001', 'A0012', 153.38, 'Ollie', 'Green', '7812671567', '1/21/1975', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0008', 'C0008', 'P0001', 'A0013', 153.38, 'Louis', 'Brien', '5678945689', '1/15/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0009', 'C0001', 'P0002', 'A0014', 90, 'Barbara', 'Stone', '8123456789', '1/16/1975', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0010', 'C0002', 'P0002', 'A0015', 90, 'Jenat', 'Jean', '9874567898', '1/17/1985', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0011', 'C0003', 'P0002', 'A0016', 90, 'Orlande', 'Moore', '7185642356', '2/18/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0012', 'C0004', 'P0002', 'A0017', 90, 'Jerry', 'Dee', '5689741235', '1/19/1967', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0013', 'C0005', 'P0002', 'A0018', 90, 'Jim', 'Smith', '7812671567', '1/20/1973', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0014', 'C0006', 'P0002', 'A0019', 90, 'Jeff', 'Smith', '5678945689', '1/21/1975', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0015', 'C0007', 'P0002', 'A0020', 90, 'Michael', 'Sam', '8123456789', '1/15/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0016', 'C0008', 'P0002', 'A0021', 90, 'Michelle', 'Lee', '9874567898', '1/16/1975', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0017', 'C0003', 'P0004', 'A0022', 30, 'Judy', 'Andy', '7185642356', '1/17/1985', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0018', 'C0001', 'P0001', 'A0023', 153.38, 'Pierre', 'Pierre', '5689741235', '2/18/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0019', 'C0002', 'P0001', 'A0024', 153.38, 'Tony', 'Kelliher', '7812671567', '1/19/1967', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0020', 'C0003', 'P0001', 'A0025', 153.38, 'Antonia', 'Sheeran', '5678945689', '1/20/1973', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0021', 'C0004', 'P0001', 'A0007', 153.38, 'Scott', 'Brown', '8123456789', '1/21/1975', 'M', '4/18/2007')
go

exec getMemberClubUsage
exec getMemberClubClass
Set quoted_identifier on
go

Set quoted_identifier off
go

View 5 Replies View Related

The 'System.Web.Security.SqlMembershipProvider' Requires A Database Schema Compatible With Schema Version '1'.

Sep 27, 2007

Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!

View 4 Replies View Related

Transferring Objects Form Schema A To Schema B In One Shot....!

May 27, 2008

I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.

I know how to do one by one...!

alter schema SchemaB transfer
SchemaA.TableA

but it will take long time...!

Thanks,

View 3 Replies View Related

Database Schema Compatible With Schema Version '1'

Apr 12, 2008

Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?

View 2 Replies View Related

Moving Data From One DB Schema To Another DB Schema Using SSIS

May 8, 2007

Hello,

I would like to use SSIS tool to move the data from one database schema to another database schema.

For example:

Source table has

1. UserName (varchar 20) (no null)

2. Email (varchar 50) (can be null)

Destination table has

1. UserID (uniqueidentifier - GUID)

2. UserName (varchar 50) (no null)

3. EmailAddress (nvarchar 50) (can be null)

4. DateTime

Questions:

1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?

OLE DB Source, OLE DB Destination, Data Converson and .....

How do I insert Guid and Date at the same time?

2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.

Please give me some advices here.

Thanks.

View 3 Replies View Related

Adding A XML Schema To XML Schema Collection

Apr 19, 2006

I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin

View 3 Replies View Related

Copy Objects From One Schema To Another Schema?

Nov 21, 2011

I am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.

In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.

View 5 Replies View Related

Need Help...any Suggestions??

Jul 2, 2007

here is my schema...

Board of Zoning Appeals

Parcel#
BZACase#
ApplicantID
OwnerID
DateFiled
Size
Zoning

VU (Variance of Use)

BZACase#
ProposedUse
Comments

VDS (Variance of Developmental Standard)

BZACase#
OrdinanceReq
RequestedDim
ProposedUse
Comments

SE (Special Exception)

BZACase#
CurrentUse
ProposedUse
OrdinanceReq
RequestedDim
Comments

Applicant

ApplicantID
FirstName
LastName
CompanyName
Line1
Line2
City

State
Zip
PhoneNum

Owner

OwnerID
FirstName
LastName
CompanyName
Line1
Line2
City

State
Zip
PhoneNum

Now i know what im doing with the applicantID and ownerID...but the BZAcase# is a number/unique identifier that looks like this....2007-VU-000, 2007-VU-001, 2007-VU-003....so my question is
1. how do i get the last three numbers to increment each time a new application is created?
2. how do i retrieve the last record in the table???
3. Do you have any other suggestions?? i have to have the number and what type of form they applied for in the "case#"???

View 11 Replies View Related

Suggestions Please

Mar 26, 2003

I am requesting suggestions to solve my problem.

Background: We are changing the way we pay commissions to our rep groups. We used to pay when the order was placed, now we want to pay when the invoice is paid.

Problem: The commision information is currently stored in the customer order, not in the invoice. These orders get deleted a couple weeks after the order was completed (shipped).

I want to create another, rather dynamic, table/structure that will store the order number and the commission percentage.

This info in this table should:

Be deleted: if the order has been deleted and the invoice either does not exist or was payed some period of time ago (maybe 6 months)

Be updated: if the customer order has been updated (i.e. the commission was changed)

Be inserted: if the order exists but the order number is not in the new table.

That is it in a nutshell.

Thanks,
Brian

View 1 Replies View Related

Need Suggestions

Sep 25, 2006

hi
i have written a procedure for stock report.
its working fine. please go through the sp and give me some Suggestions. please tell me where i need to improve my code. thanks

Note: User is required to execute this procedure daily.
i am taking the sum of issues,purchases,returns,physical adjustments for each and every product from last updated date to today's date and storing it in a table i,e stock_Dump. from this table i generate the date wise stock report

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.spUpdateStock
@strReturn varchar(70) output
AS
BEGIN
declare @maxDt smalld atetime
if exists(Select * from Stock_Dump where Txn_Date=
Convert(varchar,Getdate(),101))
BEGIN
set @strReturn='Stock Table already generated
for the day. cannot generate it again'
END

ELSE
BEGIN
TRUNCATE TABLE Stock_Dump_Temp
select @maxDt=max(Txn_Date) from Stock_Dump
/* insert (Opening stock) Closing stock for all
all the products from last max Date*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,
Closing_Stock as Opening_Stock ,
0,0,0,0,0,0,0 from Stock_Dump Where
Txn_Date=Convert(varchar,@maxDt,101)
/* Issues*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,
Sum(Qty) as Issue_Qty,0,0,0,0,0,0 from Issue_Details
Where Issue_No IN(Select Issue_No from Issue_Hdr
Where Issue_Date > Convert(varchar,@maxDt,101) and
Issue_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Goods receipt*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
Sum(Qty) as Purchase,0,0,0,0,0 from Dlv_note_Details
Where Dlv_Note_No IN(Select Dlv_Note_No from
Dlv_Hdr Where Dlv_Note_Date > Convert(varchar,@maxDt,101) and
Dlv_Note_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Rejection after receipt*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,Sum(Qty) as Rejected,0,0,0,0 from
Rejection_Details Where Rejection_No IN
(Select Rejection_No from Rejection_Hdr Where
Rejection_Date > Convert(varchar,@maxDt,101) and
Rejection_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Issues returns*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,0,Sum(Qty) As Issue_Returns,0,0,0 from
Issue_Return_Details Where Issue_R_No
IN(Select Issue_R_No from Issue_Return_Hdr
Where Return_Date > Convert(varchar,@maxDt,101) and
Return_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Physical Stock + */
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,0,0,Sum(Var_Qty) as Phy_Qty_P,0,0 from
Physical_Details Where Var_Qty>0 and Txn_No
IN(Select txn_No from Physical_Hdr Where
Txn_Date > Convert(varchar,@maxDt,101) and
Txn_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Physical -*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,0,0,0,Sum(Var_Qty) as Phy_Qty_M,0 from
Physical_Details Where Var_Qty<0 and Txn_No
IN(Select txn_No from Physical_Hdr Where
Txn_Date > Convert(varchar,@maxDt,101) and
Txn_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* insert all the records into actual table i,e Stock_dump from Stock_dump_temp (temporory table)*/
INSERT INTO Stock_Dump Select Product_code,Txn_Date,
Sum(Opening_Stock) as Opening_Stock,Sum(Issue_Qty) as
Issue_Qty,Sum(purchase) as Purchase,Sum(Rejected) as
Rejected,Sum(Issue_Returns) as Issue_returns,
Sum(Phy_Qty_P) as Phy_Qty_P,Sum(Phy_Qty_M) as
Phy_Qty_M,0 as Closing_Stock from Stock_Dump_Temp
Group By ProducT_Code,Txn_Date
/* update closing stock*/
UPDATE Stock_Dump Set
Closing_Stock=abs((Opening_Stock+Purchase+Issue_Returns+Phy_Qty_P)-(Issue_Qty+Rejected+Phy_Qty_M))
Where Txn_Date=Convert(varchar,Getdate(),101)
/* delete unwanted records */
DELETE From Stock_Dump Where Opening_Stock=0 and
Issue_Qty=0 and Purchase=0 and Rejected=0
and Issue_Returns=0 and Phy_Qty_M=0 and Phy_Qty_P=0

set @strReturn='Stock Table Update Successfully'
return
END

END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

suji

View 1 Replies View Related

Any Suggestions??

Nov 21, 2006

I have a database which contains more than 20000 stored procedureswhich were created withansi nulls off. This i found out using the querySELECT name,AnsiNullsOn FROM(SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOnFROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0Is there any way that i can set this property to 1 for all the storedprocedures i have??I know the alternate method is to drop the procedure and execute thescripts again with AnsiNullsOn = 1.Is there any other simple ways?? It will be very helpful for me..

View 2 Replies View Related

Suggestions

Dec 8, 2006

I want to transform textfiles to sql server set based and not row based.what would be the best way to transfer.

let me know.

View 10 Replies View Related

Need Some Suggestions.

May 4, 2008

Hello all!

I have this simple sp.

SELECT VisName
FROM tblVis
WHERE (VisID = 1)

Now I have lets say VISID 1 to 50. I'm using this SP to change the text on a button. Now I have 50 buttons. So I run this SP, then I run this in my vb.net code

Code Snippet
Dim constr As New SqlConnection(PVDBConn)
Try
'Variable to hold the results
Dim results As String = String.Empty
cmdUpd = New SqlCommand("SelVis1Name", constr)
cmdUpd.CommandType = CommandType.StoredProcedure
constr.Open()
'Set results to the value returned from ExecuteScalar()
results = CType(cmdUpd.ExecuteScalar(), String)
constr.Close()
'Set our buttons text to that value
Button1.Text = results
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try

At any time, when I start my program, I may need to label 10 buttons, or up to 50. Now I will have this number in a text file. Can I grab that number from a text file, and pass it into a SP?

And can I write this SP only once, to work for more than one label per time. Or do I have to write this sp 50 times?

TIA!

Rudy

View 5 Replies View Related

Looking For Suggestions

Feb 9, 2008

I have a database that will be used by two or more organizations. I would like to use pass phrase encryption to encrypt a couple of columns.

I'm looking for suggestions on how I might set up the db to let the organization change the pass phrase that is used for their encryption?

I don't really want to hard code it into stored procedures or select statements with parameters. I will be using SSL if that should make a difference with what you suggest.

Any thoughts are appreciated.

Thank you

View 4 Replies View Related

Looking For Suggestions

Sep 5, 2006

I have two stored procedures (l'll call them P1 & P2). P1, after a lot of processing, creates a temporary table that is used by P2 after an "exec P1" is done. I've separated the logic into two stored procedures because, ultimately, other sprocs will need the output of P1.

I get an error if I use #tempTable as the output table in P1 because it no longer exists after P1 finishes. ##tempTable works, but I'm concerned about concurrency issues. Any suggestions on what construct(s) I should be using?

Thanks in advance!

View 2 Replies View Related

Book Suggestions On T-SQL

Oct 4, 2005

Hi All,
I am new to SQL Server but have been doing database programming since last 3 years. I recently attended MOC (Microsfot Official Curriculum) training on SQL Server and have started to use at my company. I am comfortable with SQL but want to dig deeper into T-SQL side. I searched on the Internet but not many good books available in that either they are ranked very low or are very old i.e. written around 1999/2000 or covers SQL Server 2000 as a whole. Can anybody suggest me any T-SQL book which was written recently and focuses purely or majorly on T-SQL?

Thanks to all for your time and advice in advance.

Regards:
Prathmesh

View 3 Replies View Related

Indexing Suggestions

Jun 29, 2006

I'm looking for some help on how i should index this table.

current table has about 500k records in it.
the fields in the table are:
member_num (varchar(12), not null)
first_name (varchar(20), null)
last_name (varchar(20), null)
ssn (varchar(50), null)
address1 (nvarchar(200), null)
address2 (nvarchar(200), null)
city (nvarchar(200), null)
state (nvarchar(200), null)
zip (nvarchar(100), null)
phone1 (nvarchar(50), null)

all of the fields are searchable through an asp.net webform.

my first stab at this consisted of creating a clustered index on member_num and then creating a separate index for each of the remaining fields.

View 4 Replies View Related

Suggestions Needed

Sep 7, 2007

What I have.
I have a spreadsheet that is used in 4 or more locations on a daily basis by 1-3 ppl per locations. The spreadsheet is used to gather Quality Control information. So everyday there are a couple of spreadsheets from each system that is used to generate weekly and monthly reports. This is becoming to much work and I would like to automate the process.

What I have access to.
I currently run a Sharepoint 2007 Server for all our collaboration and document needs.
I also have the ability to setup any sql server.

What I want.
I want the QC techs in each system to be able to upload the data at the end of each day and be done with it. This way they do not have email or do a weekly report.
I would prefer to use Sharepoint and create reports weekly and monthly that can be pulled just by going to a site.

I'm knowledgeable in Sharepoint and Excel. I have some skills in VBA. I haven't dealt with SQL any, but willing to learn.
Also I'm knowledgeable in Microsoft Access as well.

Any suggestions on how I could accomplish this would be appreciated.

TIA

View 1 Replies View Related

Learning To Be A DBA--suggestions?

Apr 26, 2004

Hi Guys,

Well, as a VB/VBA applications developer I'm not well prepared for this, but it looks like I will be riding herd on a production SQL Server.

TSQL I know well enough to get along, but where can I get a fast fix on all the logins, security, and process management info? Today we had a DTS package crash overnight and it took me forever to figure out that it had left half a dozen tables locked. (Note that the scripts for the DTS package are being re-written as we speak with use of transactions and NOLOCK.) Meanwhile tech support was handling a whole mess of grumpy users.

Are there any books you would recommend as resources/references? Is there a particular author who is good at writing the stuff you really need to know in English that can be read by a mere mortal like I? I am fond of the Microsoft resources/help files but I'd like to have somthing that holds highlighter and post-it flags a bit better. Not to mention something that focuses more on the beast as a whole rather than the minutia at length.

Thanks for any suggestions!

View 14 Replies View Related

Indexing Suggestions

Jul 20, 2007

I have a set of tables with about the same structure

dataID, recordID, 15 other columns

dataID is unique but is never referenced in queries

recordID is one of the most referenced columns but only has a cardinality of about 30%

The current structure has a clustered PK on (dataID,recordID)

Someone suggested reversing the clustered PK to (recordID,dataID) because of the number of references to recordID but that didn't seem to boost performance any

After staring at this for a while I came up with something but I'd like some advice whether it makes sense or not.

create a non-clustered PK on dataID
create a non-unique clustered index on recordID

Let me know if any other information is needed. Thanks

View 5 Replies View Related

Suggestions For Books

Aug 15, 2006

I would be teaching an applied database course to buisness majorundergrads. I'm looking for a book that introduces database conceptsusing SQLServer as the database. I would really appreciate if you couldrecommend me a few such books.ThanksNemo

View 4 Replies View Related

SQL Query Suggestions/help

Jul 20, 2005

I'm trying to count the number of records in 'game_dates' where thecolumns home_team_id or away_team_id have the same value. E.g., iwant to know the number of records for each team_id where team_id ishome_team_id or away_team_id.I'm doing this in two separate select statements now. Example:SELECT count(home_team_id),home_team_id FROM gamesWHERE league_id = 218 and ((home_score IS NOT NULL OR away_score ISNOT NULL) OR (home_score <> 0 OR away_score <> 0))GROUP BY home_team_idandSELECT count(away_team_id),away_team_id FROM gamesWHERE league_id = 218 and ((home_score IS NOT NULL OR away_score ISNOT NULL) OR (home_score <> 0 OR away_score <> 0))GROUP BY away_team_idand then combining the results. Is there anyway to combine these toqueries into one query? ...and have a single result set returned withtwo columns (count,team_id)?Thanks,Glenn

View 3 Replies View Related

Suggestions Required!

Jul 10, 2007

The Background:

I have a SQL Serever with more than 10 million records.

I have to update/delete/insert records on a daily basis.

I am using the standard edition of SQL Server.

The tables are constantly having data inserted into them and the server has different jobs running all day long.

My Problem:

I cannot create index on my database and the database is getting really slow as each month/year passes.

Any/All suggestions are welcomed.

View 10 Replies View Related

DR Suggestions Required

Aug 16, 2007

Hi All,

Need some suggestions for senior management for DR Purposes:

Background:

WSS/MOSS2007 is being used as a Document Management solution.

17 Servers geographically dispersed around the UK. Each server runs WSS 3, SQL Server 2005 and IIS. Each server is linked into a PiP cloud via 2MB MPLS.

At each location; We are looking at 20 core databases; each pre-sized to 10GB. If I take one site as an example, the previous nights backup totalled 135GB.

The company has taken a centralised view on backup's, so SQL Server Data and Log files are replicated using Double-Take to a central location where by the files are taken onto tape daily (Full backup of all files).

As a precaution, I take a Full SQL Server backup daily and also Tran Logs every 4 hours locally and keep it there for 2 days; however if the site goes boom I loose those, so for this purpose; please forget they exist.

As I expect; when I restore the mdf and ldf files from tape, I will get errors when I attach those files into SQL Server for transactional inconsistencies which I'm well aware of.

Other options I've considered are:

1) DB Mirroring. Not a bad option, but still have to get the DB to the Mirror Server in the first place. Also DB Mirroring is not recommended for more than 10 mirrored databases.

2) Log Shipping. Same issue as above; Have to get the data here in the first place. Then once Log Shipping is setup; if I have a failure; I need to start the whole lot off again.

3) Transactional Replication. Issue is with the initial replication getting the data from A to B, then if I need to use it in a DR situation; I will get issues saying this table is being used for replication. This can be worked around, but it's a not a quick process...

4) 3rd Party Backup Compression. E.G. Litespeed; Redgate SQL Backup, etc. Good; Tests have shown a 42% compression for us, however if I refer to the earlier example of 135GB, this compresses to 81GB. Throw in the theoretical max for a 2MB link of 19GB / 24 Hours, this would take 4 Days to copy.

Other thoughts I've come up with are:

A) Split the tables into different file groups; not sure how easy this would be as the DB's and Tables already exist.

B) Full/Diff/Tran. Still have the issue of scheduling the full backup over the weekend and taking 4 days to get here.

C) Local Tape Backups. Issue is relying on someone to change the tape on a daily basis. It's not centrally managed and how do we restore in a DR situation ?

Could someone give me some pointers please?

Thanks

Steve

SQL DBA.

View 6 Replies View Related

I Need Suggestions From Some Expert

Jan 28, 2008

Hi folks, I have a very typical database for an ASP.net application. There is a table which will contain a hierarchical data..much like files-folders structure of a file system.
But we know that the table will be a giant one in production. There will be a huge collection of data need to persist in it. we are already facing some performance problem with some queries during the QA/test machine.
Currently there is a table which is keeping all file and folder information and another table maintaing their hierarchy relation using two column namely, parentID and childID.
My first question is, would it be better to keep this hierarchy relation into the same table rather using a different one? (much like managerID and empID in AdventureWorks sample?)
My Second question, what is the best way to design this kind of structure to get the highest performance benifit?

All kind of thoughts will be appreciated much! thanks

View 26 Replies View Related

Security Suggestions?

Mar 26, 2007

Greetings all,
I'm a developer tasked with securing up a SQL Server 2005 SP2 database. I'm not exactly a DBA but I'm giving it my best shot. I was hoping someone could offer some suggestions/tips on how I could approach this task. The amount of documentation on this type of thing is somewhat overwhelming. I'm a little pressed for time and was hoping someone could offer some help. Maybe even provide some feedback as if I'm in the "weeds" or not.

Ok, here's the deal...
At the moment I am using Windows authentication. From what I have read this is the preferred method over SQL authentication. I'd like to continue using this approach if possible.

The database can be has 3 principals
1. ASP.NET (Network Service on Windows Server 2003)
2. Windows Service running on the host server
3. A Data Access Layer assembly running on some other server

All the principals access the db using stored procedures only. Each uses a subset of all the stored procedures, some of them overlap.

My initial though was this:
For the ASP.NET I would perform the following:
1. sp_grantlogin [NT AUTHORITYNETWORK SERVICE]
2. sp_grantdbaccess [NT AUTHORITYNETWORK SERVICE]
3. Grant Execute on [For each sproc used] to [NT AUTHORITYNETWORK SERVICE]

For The Windows Service and the Data Access Layer principal, I was thinking something like this:
1. Create a separate windows login for each principal
2. Create a db login for each principal login From Windows
3. Grant execute on each of the sprocs used for each role

Question: How do I Deny Select, Insert, Update and Delete privs for all tables regardless of the principal (public user)?

Again, any help and or suggestions would greatly be appreciated.
Thank!

View 5 Replies View Related

Formatting Suggestions - Please Help

Jun 27, 2007

Hello,

I have a matrix with a dynamic number of columns (1-10). The trouble is that hiding one ore more columns still leaves space reserved for all 10 columns, which is ugly. This is because the size of the TextBox that oversees the columns is not dynamic, and it is set to the size of all 10 text boxes.

In other words, a matrix with 5 columns looks like this:

Item Total

Col1 Col2 Col3 Col4 Col5 Total

5 5 5 5 5 25

While a matrix with 2 columns (the last 3 have visibility set to false) looks like this:

Item Total

Col1 Col2 Total

5 5 25

I am going crazy trying to solve this one. Does anyone have any ideas at all that can help me? Merging all the columns into a single column would not work well for me, as each column is a drill-down for the others. And making each column small (.1in), doesn't work either because there is no "no-wrap" property.

ANY suggestions would be appreciated. How have others dealt with this issue?

Michael

View 2 Replies View Related

SQL Problem - Any Suggestions. Thx In Advance.

Jul 20, 2006

Hi
Any help with this would be greatly appreciated.
I have two tables
First Table is called "Team" see columns and data below
TeamId, TeamName, MemberId
1, White Team, 1
2, Grey Team, Null

Second Table is called "Members" see columns and data below
MemberId, Name
1, Jim Smith

I want to display both tables in a gridview as follows
TeamId, TeamName, MemberId, Name
1, White Team, 1, Jim Smith
2, White Team , Null, Null

I'm using the following sql procedure to do this

Select
Team.TeamId,
Team.TeamName,
Team.MemberId,
Meember.Name
From Team
Inner Join Members on Members.MemberId = Team.MemberId

My Problem is that this select statement returns the first row but not the second row. The reason for this is the second row's memberId is Null. However, I still need to display this row even if the data is some of the data is null.
Can anyone point out the correct sql statement for this?

View 1 Replies View Related

Any Solution For This??Please Give Some Suggestions

Dec 11, 2001

I have started working with MS SQL Server 2000 recently. I have a scenario in which I require to know within the given period of time ( say 5 mins) , which all tables from a particular database got modified. I do not want to write a trigger for each and every table for all the 13 databases , my application deals with. I have even tried the following query:
declare @curdate datetime
select @curdate=getdate()
select name, refdate from sysobjects
where xtype = 'U' and refdate =@curdate

But nope it does not help me, since refdate is something else. Can anybody tell me how can you figure out from sysobjects when was a particular object last accessed , even this would serve my purpose.

I have trying to get this done via profiler. My applications api's connect to the database under some credentials which i do not know since I do not have access to source code ( i am doing black box testing). So I can't even put a trace on one particular user account. What I am doing currently is trapping all store procedure events..., but then its too much of work...

Hence I wanted to know , is there any way out for the situation where given a database name and a time span we can find out the tables modified/accessed within that time span from that database ???

View 3 Replies View Related

Monsterous SQL Query... Suggestions?

Jan 17, 2008

Hi All,

I work for a telco. We've got a table in a database which shows phone calls made by customers and when they made them.

I need to generate a list of customers who have made phonecalls last month and have NOT had a five days in a row without making any calls.

Can any of you help? I'm not sure how to tackle this one without getting a very bloated and inelligent solution. Basically, the only solution I can think of is generating 31 tables, one for each day and then just checking calls made on each day.

Has anyone got a better idea?

I'm using SQL Server 2000.

Thanks

Dave

View 14 Replies View Related

Suggestions For Rewriting This Query

Oct 24, 2007

Does anybody have any suggestions to rewrite the 2nd WHEN part of the query??? Thank you.

------------------------------------------------------------
update t_pgba_hdr
set HCFA_PLACE_TRMT_CD2 =
case when (select max(b.HCFA_PLACE_TRMT_CD)
from t_pgba_hdr as b
where t_pgba_hdr.clm_id2 = b.clm_id2) like '[A-Z]%'
then '99'
when (select ltrim(rtrim(max(b.HCFA_PLACE_TRMT_CD)))
from t_pgba_hdr as b
where t_pgba_hdr.clm_id2 = b.clm_id2) in '[0-9]'
then '0' + (select ltrim(rtrim(max(b.HCFA_PLACE_TRMT_CD)))
from t_pgba_hdr as b
where t_pgba_hdr.clm_id2 = b.clm_id2)
end

------------------------------------------------------------

-soumya

View 4 Replies View Related

Suggestions On Design Of Table

Oct 30, 2006

Hello and thanks for your help in advance.

So what I need to do is store applications info, such as application name, path, server it's installed on, etc., into a table.

I thinking of designing the table like this but not sure if this is a good design:

ApplicationInfo
---------------
ID
Application -- pk
Path
ServerName

There are apps that are installed on all servers and there are some apps installed only on a few servers. I was thinking of making the "Application" field unique so that only 1 instance of the application name exists and then comma delimit the "ServerName" field values.

So with this approach records would look like this:

Field Value
-----------------------------------
ID "1"
Application "Adobe"
Path "C:Program FilesAdobe"
Server "ServerA,ServerB,ServerC"

ID "2"
Application "Microsoft Office"
Path "C:Program FilesMicrosoft Office"
Server "ServerB,ServerC"

What would you recommend doing?

View 3 Replies View Related