Use SQL to search a column for a range of values — Parv The IT Geek

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]

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

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.


Originally published at https://parvtheitgeek.com on January 24, 2014.

Search for a list of values using the IN command in SQL — Parv The IT Geek

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.


Originally published at https://parvtheitgeek.com on January 23, 2014.

NULL Values — How to filter out NULLS and how to find NULLS using SQL — Parv The IT Geek

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.


Originally published at https://parvtheitgeek.com on January 22, 2014.

AS — Use Aliases / Shorthand in SQL to make more readable queries — Parv The IT Geek

Have you ever wondered how I can make my SQL queries look more readable, manageable and simpler?

Well the answer is to use the ‘AS’ command as this can alias tables.

For example the below query can seem quite daunting with long table names.

USE AdventureWorks 
GO
SELECT HumanResources.Department.DepartmentID, HumanResources.Department.Name, HumanResources.Department.GroupName, HumanResources.Department.ModifiedDate
FROM HumanResources.Department

However, the below query using the ‘AS’ command to alias the HumanResources.Department makes it look better, simpler and is quicker to read.

USE AdventureWorks
GO
SELECT HRDept.DepartmentID, HRDept.Name, HRDept.GroupName, HRDept.ModifiedDate
FROM HumanResources.Department AS HRDept

As you become more proficent in SQL you will find yourself aliasing tables all the time and probably won’t even notice yourself doing it


Originally published at https://parvtheitgeek.com on January 21, 2014.

Intro to SQL Server Reporting Services 2008 : Report Builder 2.0 — Parv The IT Geek

This shipped after SQL Server Reporting Services 2008 and is available as a separate download. I have always run a search to download the latest installation file.

Report Builder 2.0 is a too design to allow the easy and quick development of Reporting Services Reports and was created with the non-developer in mind. This is reflected by the fact that it looks like MS Office.

It can be started by selecting Start > All Programs > SQL Server 2008 Report Builder > Report Builder 2.0.

Once you have opened Report Builder 2.0 you will notice there are 4 tabs Run,Home, Insert and View.

The Home tab contains Clipboard, Font Paragraph, Border, Number and Layout.

Insert tab has Data regions, Report Items, Sub reports, Header & Footer.

View tab has Show and Hide.

The Run tab has Views, Zooms, Navigation, Print, Export, Options and Find.


Originally published at https://parvtheitgeek.com on January 20, 2014.

SQL Server Reporting Services 2008 (SSRS) Databases- ReportServer & ReportServerTempDB.

When reporting service is set up there are two default databases created which are

1. ReportServer

2. ReportServerTempDB.

Reportserver database contains a wide range of information which is briefly listed below.

1. Subscriptions

2. Schedules

3. Data sources

4. Folder

5. Keys

6. History

7. Notifications and much much more.

I would suggest looking at the tables which are contained in the ReportServer database to get a good understanding about all the data which is stored and within and I have listed below.

ActiveSubscriptions

Batch

CachePolicy

Catalog

ChunkData

ChunkSegmentMapping

ConfigurationInfo

DataSets

DataSource

DBUpgradeHistory

Event

ExecutionLogStorage

History

Keys

ModelDrill

ModelItemPolicy

ModelPerspective

Notifications

Policies

PolicyUserRole

ReportSchedule

Roles

RunningJobs

Schedule

SecData

Segment

SegmentedChunk

ServerParametersInstance

ServerUpgradeHistory

SnapshotData

Subscriptions

SubscriptionsBeingDeleted

UpgradeInfo

Users

I would always recommend setting a maintenance plan to regularly backup this database due to all the important data that is held within. Do not attempt to try and modify the table structure within these tables as you will likely cause an error.

ReportServerTempDB stores temporary data from the ReportServer DB which includes cached reports, session information….. This data is still important and it should still be backed up to aid speedy recovery.


Originally published at https://parvtheitgeek.com on January 19, 2014.

Adding SQL Server Reporting Services 2008 — Parv The IT Geek

Adding SQL Server Reporting Services 2008

Before installing SQL Server Reporting Services 2008 it is important that you check whether your operating system is supported ( Windows Server 2003,2008,2012, some editions of XP, Vista, 7 and 8), you have sufficient RAM ( 512mb free), your CPU is powerful enough ( 1.0GHZ ) and if you have sufficient hard disk ( about 2gb). The installation checks will check all of this but a bit of prior research could prevent any time being wasted.

Always refer to Book Online to check the required specifications as these are the most up to date available on the internet.

1. Goto Start Menu > All Programs > Microsoft SQL Server 2008 R2, SQL Server Installation Centre

2. Click on New Installation or add new features to an existing installation

3. The SQL server setup wizard will then check your environment to see whether it meets the requirement to install reporting services. As long as there are no failures click OK.

4. As long as there are no problems click Install on the next screen.

5. The setup rules will check the system against another set of rules and will highlight any problems.

6. Select Full Text Search Reporting Services , Business Intelligence Development Studio, Client Tools Connectivity, Management Tools — both Complete and Basic.

7. Another check will be done against the features selected. Press next if there are not any problems.

8. Disk space of your setup will be checked.

9. Please use whatever accounts you have assigned for the account. I will be using the local service account as I haven’t properly setup my VM.

10. Press next

11. Press next. Generally we don’t send back the rror reports.

12. The final checks will be done and press next.

13. There will be a comprehensive list of the options chosen. Please review this list and then if your happy press install.

14. When the installation is completed successfully then you will get the final screen.


Originally published at https://parvtheitgeek.com on January 18, 2014.

How to order results in SQL? ORDER BY — Parv The IT Geek

A basic query of the HumanResources.Department table bring s the following results.

If you wanted to reverse the order of the DepartmentID then you would need to user the ‘ORDER BY’ clause.

The default sort is always ascending ( ASC) but it does not have to be explicitly written. If you want the order to be reversed then you would need to use DESC ( descending).

Sorting of results can also be done on numbers, dates, letters……


Originally published at https://parvtheitgeek.com on January 17, 2014.

Easily Check Database Integrity (DBCC CHECKDB) For All Databases On A Server — Parv The IT Geek

Today I was doing my daily checks on one of my biggest SQL instances which has over 1,000 databases and noticed that SQL Agent was stopped. I couldn’t see any errors and was able to simply start it without any problems but I was concerned that the daily integrity check jobs had not run and I am way too cautious to get caught by not doing it manually.

Initially I wasn’t too sure how to do it very quickly without creating a script using DBCC CHECKDB, querying sys.databases with some dynamic T-SQL but decided to give the below script a try and luckily it worked.

EXEC sp_MSforeachDB ‘DBCC CHECKDB (?)’


Originally published at https://parvtheitgeek.com on January 16, 2014.

What are the SQL Server System databases? — Parv The IT Geek

SQL Server System databases

SQL Server has 5 very default system databases which are

Master

As the name implies this is one of the most important databases on the system and without it SQL Server will not start. It stores all the configuration of the instance and all other databases which are subsequently created. The information is stored into tables known as system tables.

MSDB

The MSDB database is used by SQL Agent and contains data about jobs, job schedules, alerts, log shipping, database mail, recovery of damaged pages….. This information is retrievable through the tables stored within this database.

Model

This is basically a template database for the creation of new databases.

TempDB

TempDB is recreated every time SQL is restarted and is basically used for storing of information temporarily when SQL Server doesn’t have sufficient memory available. This could be a sorting query, temporary tables, holding variables…..

Resource

This is a hidden read-only database which contains information on all objects within SQL server. If you look in the directory of your SQL Server installation you will find two files named mssqlsystemresource.mdf and mssqlsystemresource.ldf which contain the resource database. The hidden nature of the database makes it easy to upgrade via hot-fixes and service packs.


Originally published at https://parvtheitgeek.com on January 16, 2014.