Showing posts with label mssql. Show all posts
Showing posts with label mssql. Show all posts

Wednesday, May 15, 2013

Using SQL Mail Part 2

Yesterday we talked about setting up SQL Mail.  Today we are going to talk about how we can take advantage of SQL Mail.  To do so, we need to be familiar with the sp_send_dbmail stored procedure.  This stored procedure allows for sending emails to a given list of recipients and many other options:

The format for the stored procedure looks something like this:

exec msdb.dbo.sp_send_dbmail @profile_name = 'profile_name'
    ,@recipients = 'recipients'
    ,@copy_recipients = 'copy_recipient'
    ,@blind_copy_recipients = 'blind_copy_recipient'
    ,@from_address = 'from_address'
    ,@reply_to = 'reply_to'
    ,@subject = 'subject'
    ,@body = 'body'
    ,@body_format = 'body_format'
    ,@importance = 'importance'
    ,@sensitivity = 'sensitivity'
    ,@file_attachments = 'attachment'
    ,@query = 'query'
    ,@execute_query_database = 'execute_query_database'
    ,@attach_query_result_as_file = attach_query_result_as_file
    ,@query_attachment_filename = query_attachment_filename
    ,@query_result_header = query_result_header
    ,@query_result_width = query_result_width
    ,@query_result_separator = 'query_result_separator'
    ,@exclude_query_output = exclude_query_output
    ,@append_query_error = append_query_error
    ,@query_no_truncate = query_no_truncate
    ,@query_result_no_padding = @query_result_no_padding
    ,@mailitem_id = mailitem_id OUTPUT

As you can see, there are many different options available when using this stored procedure.  Here are some items of note on the variables:
  • @profile_name is the Profile Name used in the previous post.  The email will not go out if the profile_name is not spelled correctly.
  • @recipients is a comma separated list of recipients.
  • @body_format is the format to use for the body of the email.  The options are "Text" and "HTML".  The HTML options allows you to use HTML formatting for the email (which includes line breaks, etc.)
  • @file_attachment is a comma separated list of files to attach to the email.
  • @query (query to execute), @execute_query_database (database to execute the query on), @attach_query_result_as_file (selected whether to put the results as an attachment or in the body of the email, and @query_attachment_filename (the name for the attached query results) can all work together to give you different options when sending your email.
Once you have decided on your different options, you can then format your email message and include the SQL stored procedure when scheduling jobs or directly into stored procedures you have written.  You can then attach any results you want into the email.

As an example, let's say that I set up a stored procedure that emails customers once they have made a purchase.  I have variables that I would use in the stored procedure to perform this.  Those variables could include: @customernumber (customers account number), @ordernumber (order number), @customeremail (customers email address).  Using these variables, I could then send out an email on order like so:

declare @query1 varchar(4000) = 'select * from CustomerOrder where custombernumber = ' + @customernumber

exec msdb.dbo.sp_send_dbmail @profile_name = 'Test Profile'
    ,@recipients = @customeremail
    ,@from_address = 'myemail@gmail.com'
    ,@reply_to = 'myemail@gmail.com'
    ,@subject = 'Customer Order'
    ,@body = 'Attached is the information for your order'
    ,@body_format = 'HTML'
    ,@query = @query1
    ,@execute_query_database = 'MyDB'
    ,@attach_query_result_as_file = 1

To read more on SQL Mail, visit the following: SQL Mail

Monday, May 13, 2013

Using DATEDIFF() in SQL

Let's say that you wanted to know the difference between two dates.  Do you want to know the difference in months, days, years, minutes, or seconds?  SQL has a built-in function that allows you to do this.  The name of this functions is DATEDIFF (or date difference).  The function works as follows:

DATEDIFF ( DatePart, StartDate, EndDate)

DatePart is the part of the date that you want to see the difference on.  For example, to see the difference in years, your DatePart would be yy or yyyy.  Some other examples as below:

  • Month = mm or m
  • Hour = hh or h
  • Minute = n or mi
  • Second = s or ss
  • Day = d or dd
So, where can a function like this be valuable?  I work in the telecom industry and sometimes we have to calculate how long a customer was on a call using the raw records.  In the records, there is a start time and an end time.  I could use DATEDIFF() to find out how many seconds they were on the call, as follows:

SELECT DATEDIFF(ss, StartTime, EndTime) 
FROM CDRTable

As another example, let's say that you are an online sales company and you want to see how many days it took for a package to get to a customer:

SELECT DATEDIFF(dd, ShipDate, ReceivedDate) 
FROM ShippingTable

Make sure that you have the correct date in the correct columns.  If we reversed our date columns (like below), we would end up with a negative number:

SELECT DATEDIFF(dd, ReceivedDate, ShipDate) 
FROM ShippingTable

Where do you think you could use this in your organization?

To read up more on the DATEDIFF function, go here: DATEDIFF()

Wednesday, May 8, 2013

SQL Functions

Let's say that in SQL you want to add values in a single column for all rows returned in your query.  Maybe you want to count the number of rows that are returned from your query.  Or maybe you want to find the average of the values for all rows using the same column.  It is possible to do every one of these situations using something called SQL Functions.

What is a SQL Function? A function is a routine that takes the parameters you provide, performs a certain set of actions, and then returns the results from those actions.  For example, using the SUM(), COUNT() and AVG() functions, you can accomplish the situations above.

Now, there are two types of functions in SQL Server.  They are as follows:
  • Built-in functions
    • Created by SQL Server
  • User defined functions
    • Created by the user
Built-in functions include those mentioned above, as well as many others, such as
  • UPPER()
    • Shows all letters in uppercase
  • LOWER()
    • Shows all letters in lowercase
  • MAX()
    • Maximum value in a column
  • MIN()
    • Minimum value in a column
  • And many more
A user can also create a function for his or her needs.  The syntax looks like this:

--Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH  [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

For example, let's say that I need a function that calculates the square footage.  My function would look like this:


CREATE FUNCTION sqFeet
(@SideOne decimal(4,1), @SideTwo decimal(4,1))

RETURNS decimal(14,3)

AS

BEGIN

RETURN (@SideOne * @SideTwo)

END

To call your function, you would then run the following query:

SELECT sqFeet(2, 2)

Which would return the result: 4

Now you might be asking: what are the benefits of using SQL Functions instead of using something else, like SQL Stored Procedures? Well, SQL Functions allow you to return results inline, instead of having to execute a stored procedure and returning the results as an output parameter.  Also, a SQL Function can be used at different parts within a T-SQL statement, whether in the SELECT statement, WHERE or HAVING clauses.

Where can you see using a SQL Function in your organization?

Thursday, May 2, 2013

SQL Indexes and Their Benefits

As the data in your database grows, you may notice that your querying becomes slower.  This occurs because SQL Server needs to search through each record until it finds all the records you are looking for.  So, when this happens to your database, how do we approach the problem?

The answer to this lies in something called indexes.  An index is described as a structure on a table or view that speeds up the retrieval of data.  In SQL Server, there are two major types of indexes:
  1. Clustered Index
    • Clustered indexes sort and store the data based on key values (specified by you).
    • Each table can only contain one clustered index.
  2. Nonclustered index
    • Nonclustered indexes do not sort and store the data like clustered indexes do.  They basically have a pointer to the data based on the key values selected.
    • The pointer is actually called a row locator.
    • Depending on your version of SQL Server, you can have quite a few nonclustered indexes.
When performing a search on table, SQL Server will use the least labor intensive method.  Indexes will be the least labor intensive (finds storage location for the data) than a non-indexed table (table scan), which needs to search through each row to find a match and can be resource intensive.

Now, it is very important to put in time considering what should be included in your indexes.  For example, clustered indexes should be fields that have unique data (primary key).  Nonclustered indexes could be created on other columns that you search on frequently and multiple columns that are searched together could be included on the same nonclustered index.  Be careful, though, because adding too many indexes (one of every column) will cause too much overhead.

Don't be afraid to try out different indexes on your data.  Put an index in place, query the table, and see if your querying improves.  If not, try a different index and keep trying until you feel like you have a couple that fit your needs.

Wednesday, May 1, 2013

Database Files, Recovery Models and Backups Part 2

Yesterday we talked about the .MDF and .LDF files and what their purpose is.  We also talked about the different recovery models available in Microsoft SQL Server and which model to select for your database.

Now, I want to cover database backups.

First, two questions you might ask are:
  1. What kind of backups should I do?
  2. How often should I do them?
There are three major types of backups in SQL Server:
  • Full Backup
    • A data backup that contains all data within the given database.  It also contains enough log backup to allow for recovering all data
  • Differential Backup
    • A backup that contains all data that has changed since the last full or differential backup
    • With differential backups, you will be required to restore each backup in order for the data to be in sync.
  • Log Backup
    • A backup of the transaction log since the last log backup.
    • Transaciton log (.LDF file) can be shrunk once a log backup has been performed.  If you find your .LDF file is growing, run a transaction log backup and then shrink your .LDF file (Shrinking the Transaction Log)
For more information, review this Backup Overview

Deciding which backup (or combination of backups) should be selected, ask yourself this: how much data am I willing to lose?  Can you go a full 24 without backing up your data?  Do you need to have as much real time data as you can get?

Most applications will need a backup at least every 24 hours (whether full or differential).  If you can afford to lose 30 minutes to an hour of data, a full backup each day accompanied by a differential backup every 30 minutes might suffice what you need.  If you need a solution that is more real-time then thirty minutes, there are other options that we will not cover in this article (non-Microsoft applications, Replication, and Mirroring [which has been deprecated]).

To read more about backup and restore strategies, go here: Backup and Restore Strategies

Tuesday, April 30, 2013

Database Files, Recovery Models and Backups Part 1

In your Microsoft SQL Server database, there are a minimum of two database files: an .MDF and .LDF file.  The .MDF file contains the data for your database, while the .LDF file is the transaction log file for your database.  Each of these files will grow as your data grows, but depending on your recovery model, your .LDF file may fill up at a slightly quicker pace.

There are three recovery models in Microsoft SQL Server 2012.  They are as follows:
  • Simple
    • The .LDF space is automatically reclaimed when new transactions have been entered.
    • No transaction log backups available.  Since no transaction log backups are created, if your database goes down for any reason, any transaction changes made on the database since last full backup would be lost and need to be re-created.
  • Bulk Logged
    • Transaction log backups are created, which allows for data recovery from last backup, but not point in time.
    • Fully logs regular transactions, but minimally logs bulk transactions.
  • Full
    • The .LDF file continues to grow as you have more transactions in the database.
    • Log backups are available.  Data can be restored from the last log backup, which would first need to be accompanied by a restoration from a full database backup.
For more info on the different recovery models, visit here: Recovery Models and for more info on bulk logging, visit here: Bulk Logs

To know which recovery model you should select, first review the type of data contained within the database.  For example, if your company manages online transactions, you will more than likely want to make sure your data is backed up regularly (Full Recovery Model).  If, on the other hand, your database pulls data from other servers and runs reports on the data, you could get by with the Simple Recovery Model (granted that you have a backup copy of the database schema).

A company I used to work for had our main database, which housed our real time data, and a second server that used SSIS packages to create tables, pull the data and then summarize it.  We could get by with the Simple Recovery Model on the report server, but we needed the Full Recovery Model on the real-time servers.

Tomorrow we will cover what to do about growing database logs and backups.