views

it is easy, or relatively so, to query a database table.  

Select * from table where something ....

If you need to query multiple tables, it becomes more complex and if you have to join 5 tables, it can be annoying.  And if you do it from scratch each time?

Or you can create a view of the join of the five tables (on time) and then do the query on the one table, or rather the one view.

Create VIEW vMyView with schemabinding as (Select * from whatever, join however where however)

then

select * from vMyVIew where condition...

You don’t have to Select * to create the view,  you can hide\not show whatever you want and let other people use the view for their purposes.  

Each base table in the view will be involved each time the view is queried.  They should have indexes on them, just like always.  Because a table is involved in a view doesn’t affect where or how it is used everywhere else for maintenance, indexes, backups, et al.

Now, a view is defined as a virtualized table created from a saved query.  We can add indexes (clustered, then nonclustered) to a view,  However if we made a CI, the view becomes is physical,  not virtual and is “materialized’ to disk .  Also, depending on the fields referenced in the index, it might be updated each time any of the base tables in the view change.   

Indexed views are especially valuable for calculating complex aggregations of data.  Instead of running them on the base tables directly, we use the view to determine the aggregations.  

Advertisements

move the log file

We have a tradition, no, a best practice of putting logs in one location and data files in another.  But if the data or logs get too large, or in one case, they are colocated, the physical location of the files can be moved.

You can find where the files are, or more correctly, where SQL thinks they are (the configuration of the database) in SSMS or using sp_help.

Typically, you would detach the database, move the ldf or mdf file(s), and reattach the database.  This can be done in SSMS or via t-sql scripts.  You can also take the database off-line, edit the database properties and change the path to the new location, then put the database back on-line.

Locate the files

USE Database
GO
sp_helpfile
GO
Use MASTER
GO
ALTER DATABASE Database
SET SINGLE_USER
GO

sp_detach_db 'Database'
GO

At this point, the database is detached, and we can move the file to the new location.  We can do this is SSMS by selecting the Database and other tasks.

sp_attach_DB 'DataBase', 
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\Database_Data.mdf',
'E:\Logs\Database_Log.ldf'
GO

Reattach the database referencing the file locations. (This can be done in SSMS at the Databases level, with Attach… and [ADD] to locate the files.  Check that the database is back on-line and working properly.