How to attach AdventureWorks 2012 to SQL Server

Have you ever wanted to install the sample database (AdventureWorks )provided by Microsoft which a lot of the tutorials and books refer to? Well one of my colleagues has started to learn SQL Server so I thought I would knock up a quick guide for him.

The correct classical way is to use T-SQL would be to use the below script which you should adjust for wherever you have located the data/ MDF file.


CREATE DATABASE AdventureWorks2012

ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf')

FOR ATTACH_REBUILD_LOG ;

Or you could use the simple GUI method and Right click on databases and select Attach.

1

Then go to the correct path and select the data / MDF file and press OK.

2

If you use the T-SQL method ensure that you do not have the LDF / Log file in the directory of the data / MDF file otherwise you may encounter an error. Also, when recreating the database from just the data / MDF file you will get the following error message.

File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf” may be incorrect.

New log file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf’ was created.

 

 

Change a database state in SQL

Have you ever wanted to change a databases state from read only to read write or vice versa? Well I am going to show you how using some easy to remember T-SQL which you will be using repeatedly over time.

 

We received a database from a client the other day and noticed one of my System Admin was struggling with the restore. He thought he was doing something wrong with the restore and I said he hadn’t done anything wrong and that the database was likely put into that state prior to being backed up and sent to us.

 

I showed him the below T-SQL which enabled him to change the state to read write and then change it to read only if required.

 

Make Database Read Only

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_ONLY WITH NO_WAIT

GO

 

Or

 

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_ONLY

GO

 

Make Database Read/Write

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_WRITE WITH NO_WAIT

GO

 

Or

 

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_WRITE

GO

 

List SQL databases in SQL Server

Have you ever wanted to list out all the databases you have on a server? Well I am going to show you how to do it quickly and easily.

The other day I had completed a migration from a creaky old dinosaur server to a brand new powerful server which always brings a smile on my face as I like new stuff. I stopped all jobs, put the databases into offline, updated my documentation and diarised a week in the future to finish my clean-up work.

The next day my colleague asked me where I had moved the databases as they did not appear to still be on the server. When he listed the databases using the two following procedures it showed that we were 8 databases down which was the same amount I had put into offline mode.

EXEC sp_databases
EXEC sp_helpdb

I took a step back and did a quick manual search on the server which when you have a few thousand databases can take a while. I found all eight databases and came to the realisation that those two stored procedures can’t find offline databases.

I used the below query to get an accurate list of the databases.

SELECT name
FROM sys.sysdatabases

Find todays date & time in SQL Server part 2

Have you ever wanted to return the time found in GETDATE or CURRENT_TIMESTAMP to a higher level off accuracy in SQL? Well I am going to show you how.

The other day I needed to get the current time to greater accuracy due to the time stamp given to some records and I had to use the following functions which give the time to the millisecond.

SELECT SYSDATETIME ()             —     System time and date

SELECT SYSDATETIMEOFFSET ()       —     The time is included.

SELECT SYSUTCDATETIME ()          —     The date and time is returned as UTC time

1

Have you ever wanted to convert a US date to UK format using SQL?

Have you ever come across the situation where you have data in US format and need it changed to UK format? Well I am going to show you how to use SQL to easily reformat the date.

I have had this problem several times in the past dealing with international clients who supply databases with the incorrect date format and use the below query to change the format.

SELECT CONVERT(VARCHAR(30), CONVERT(DATETIME, ‘6/1/2009 12:00:00 AM‘, 101), 103);

1

Find the current day, month and year in SQL Server

Have you ever wanted to return the value of the current day? Month? Or year? Well its easy in SQL and I am going to show you how.

The query for the current day is below.

SELECT DAY(GETDATE())

The query for the current month is below.

SELECT MONTH(GETDATE())

The query for the current year is below.

SELECT YEAR(GETDATE())

Each query will return a number which will correspond to the information required. For example a value of 2 returned is February in the month query.

1

Find todays date and time in SQL Server

Have you ever wondered how to get the current time and date within SQL Server? Well I am going to show you two easy methods of getting this information.

The first method is my preferred method as it’s slightly shorter than the second method.

SELECT GETDATE()

SELECT CURRENT_TIMESTAMP

1

Use SQL to search a column for a range of values

Have you ever wanted to search for a column for a range of values? For example if you were searching for Product ID which ranges from 1 to 300, or look for a range of names starting A to D.

Well I can show you how to do these quickly and easily by using the power of SQL.

The below query shows there are 504 rows in the Production.Product table


USE AdventureWorks2012

GO

SELECT *

FROM [Production].[Product]

1

 

 

If you wanted to search for ProductID’s which were 1 to 4 you could do it using the below query.


USE AdventureWorks2012

GO

SELECT *

FROM [Production].[Product]

WHERE ProductID = 1

OR ProductID = 2

OR ProductID = 3

OR ProductID = 4

2

 

Whilst the above query works it is a very ugly and inefficient query as you are writing way too much code when you could use search a range which takes less time to type and is more easily read and understood.


SELECT *

FROM [Production].[Product]

WHERE ProductID BETWEEN 1 AND 4

As you can see the above query is much more readable , easily understood and more importantly is shorter which saves you time.

I would recommend using the syntax on different values and tables to see how many different  types of data you can retrieve.

 

 

 

Search for a list of values using the IN command in SQL

Have you ever been presented with a list of values which need to be found in a column or wondered how to search for a list of exact names, addresses, numbers without using wildcard searches or ranges.

Well SQL has the answer and it is the IN command.


USE AdventureWorks

GO

SELECT *

FROM HumanResources.Department

The above query produces 16 rows of information when there are no clauses filtering the results.

If you wanted to know how many departments were in the Manufacturing group you would use the below query.


SELECT *

FROM HumanResources.Department

WHERE GroupName ='Manufacturing'

If you wanted to know how many departments were in the Inventory Management group you would use the below query.


SELECT *

FROM HumanResources.Department

WHERE GroupName ='Inventory Management'

You could combine both queries as show below using the OR command to filter for more than one value.


SELECT *

FROM HumanResources.Department

WHERE GroupName ='Inventory Management'

OR GroupName ='Manufacturing'

However, though it does achieve our desired result there are is a better method which I shall show you below now using the IN command.


SELECT *

FROM HumanResources.Department

WHERE GroupName IN ('Inventory Management','Manufacturing')

The above query produces the same results but is far superior in its readability and ease of use. We can easily see the filter and there are fewer commands which measns less to go wrong and less to type.

Experiment with numbers, dates and words on the database to familiarise your self with this new query.

NULL Values – How to filter out NULLS and how to find NULLS using SQL

Have you ever wanted to find a NULL value or filter out NULL values within a column?

Well SQL has the solution and our saviour is the ‘IS NULL’ and ‘IS NOT NULL’ commands. Using them in a ‘WHERE’ clause will allow us to do the filtering out appropriately.


USE AdventureWorks

GO

SELECT COUNT(*)

FROM [AdventureWorks].[Person].[Address]

The above query bring us a result of 19,614 rows contained within the AddressLine2 where no values have been filtered.


USE AdventureWorks

GO

SELECT COUNT(*)

FROM [AdventureWorks].[Person].[Address]

WHERE AddressLine2 IS NULL

Using the ‘IS NULL’ clause we find that there are 19,252 rows where AddressLine2 has a Null value.


USE AdventureWorks

GO

SELECT COUNT(*)

FROM [AdventureWorks].[Person].[Address]

WHERE AddressLine2 IS NOT NULL

Using the ‘IS NOT NULL’ clause we find that there are 362 rows where AddressLine2 has a value which does not contain a Null.

A quick check shows  total count of ‘IS Null’ ( 19,252) + total count of ‘IS NOT NULL’(362) equal 19,614 which shows that we have returned the correct figures and have not been tripped up by any kind of data anomalies.