I'd recommend Ola's scripts for SQL Server maintenance - backups, re-indexing, checkDB, house-keeping, everything! It takes care of naming conventions too! They are very clever, and award winning too!!!! :)
Personally, if your databases are small, I'd keep to a full daily backup schedule. I believe Microsoft recommend that as well - simplicity is best approach.
Ola's scripts:
http://ola.hallengren.com/
ok. It looks like you have 4 tables here with nothing to join them together. Perhaps the easiest solution at this point would be to create a mapping table called something like CarRental which would have the columns Carpoolid, cilentsid, dateinid, and dteoutid.
I think the way I would go would be to drop the datein and dateout table and put them on a table called car rental.
CREATE TABLE [dbo].[CarRental](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CarpoolID] [int] NOT NULL,
[ClientID] [int] NOT NULL,
[DateIN] [datetime] NULL,
[DateOut] [datetime] NOT NULL,
CONSTRAINT [PK_CarRental] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CarRental] WITH CHECK ADD CONSTRAINT [FK_CarRental_CarPool] FOREIGN KEY([CarpoolID])
REFERENCES [dbo].[CarPool] ([Id])
GO
ALTER TABLE [dbo].[CarRental] CHECK CONSTRAINT [FK_CarRental_CarPool]
GO
ALTER TABLE [dbo].[CarRental] WITH CHECK ADD CONSTRAINT [FK_CarRental_Clients] FOREIGN KEY([ClientID])
REFERENCES [dbo].[Clients] ([Id])
GO
ALTER TABLE [dbo].[CarRental] CHECK CONSTRAINT [FK_CarRental_Clients]
GO
Car rental now stores the relationship between the client and the car that they rented and for each record stores the datein and dateout.
If you dont want to script all of this out you can use the table designer to create the table by right clicking on tables and then choosing new table. You can use the database diagram to drag and drop columns in order to create your foreign key relationships by right clicking database diagrams and chosing New Database diagram.
If you use this structure then You would leave the DateIn field as NULL untill the car was returned. Your query to find out which clients and cars have not been returned would be
SELECT carpool.brand, clients.name, clients.lastname
from carrental
join carpool on carpool.id = carrental.carpoolid
join clients on clients.id = carretnal.cientid
where carretnal.datein IS NULL
In this case you dont need to update the records for people who havent returned a car. You only update the record to contain the date when they have returned the car.
UPDATE CarRental
SET Datein = GETDATE()
WHERE Clientid = @CLientid
AND Carpoolid = @Carpoolid
Best Answer
Indexes are rebuilt to remove fragmentation. There a thousand and one articles and blog posts on the nature of index fragmentation but @BrentOzar recently posted a particularly concise explanation in Stop Worrying About SQL Server Fragmentation.
Brent's recent article offers an updated perspective on one of his previous series, Index Fragmentation Findings. The older article highlights statistics from much older studies on the damaging effects of fragmentation, the more recent makes a case for mitigating a large percentage of the performance downsides by ensuring your database is fully cached.
Ram is now so comically cheap that it is probably the cheapest, easiest, lowest risk solution to a heavily fragmented database. Especially if the nature of the database design is such that it will naturally become fragmented despite maintenance efforts.