SQL Server supports the notification of administrators through operators. Operators are aliases for users or groups that can receive notifications when jobs complete or alerts have been raised. Each SQL Server instance can have its own set of defined operators. Today, I am going to walk you thru the following tasks in Transaction SQL. Adding an operator Updating an operator Deleting an operator Defining a fail-safe operator To define an operator, one needs to know the email address of the user and what time he is responsible for the SQL…
Tag: free code
DBA – Introduction
Today, I am going to start writing another topic thread for Database Administration (DBA). I do alot of DBA work among many other things at my current job. I am going to cheat a little by copying a list from a renown SQL Server MVP named Paul Randal. This list was published in a TechNet Magazine article in November, 2010. To be a succesful database administrator, you should start working your way through the following tasks (secrets) to manage choas at your organization.
|
1 2 3 4 5 6 7 8 9 10 |
10. Take Inventory 9. Standardize Configurations 8. Understand the I/O Subsystem 7. Create a Customized Maintenance Plan 6. Ensure the Security of Your System 5. Get on Good Terms with Your Developers 4. Develop a Comprehensive Disaster Recovery Strategy 3. Take and Test Regular Backups 2. Monitor and Maintain Performance 1. Know Where to Find Information |
In the coming weeks, I am…
Selecting Data – Part 4
Today, I am continuing our exploration of the SELECT reserved word by talking about sub-queries. A sub-query is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub-query. Sub-queries can be used anywhere an expression is allowed or can be nested inside another sub-query. I will be using the AdventureWorks Sample database supplied by Microsoft during this talk.
|
1 2 3 4 |
<span style="color: #008000; font-family: Lucida Console; font-size: small;">-- Use the correct database USE AdventureWorks2008R2 GO </span> |
One usage a sub-query is with comparison operators in the where clause. The example below returns all the sales people by name and id…
Selecting Data – Part 3
The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. Today, I am continuing our exploration by reviewing SELECT queries that aggregate data. Data aggregation is the process of converting many records into a few records with special meaning. Many of the functions used in aggregation have statistical meaning such as minimum value, maximum value,…
Selecting Data – Part 2
The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. Today, I am continuing our exploration of this reserved word by reviewing queries that merge data using joins and unions. Six known join types exist in SQL Server. The definitions are based upon joining TABLE A (left) with TABLE B (right). Inner Join – Include…
Crafting Databases – Part 4
The ALTER TABLE statement is part of the Data Definition Language (DDL). The statement is used to modify a table after it has been created. Today, I would like to talk about the three integrity concerns that data modelers deal with. Integrity Concerns Entity Data Integrity – Adding primary keys to makes sure rows are unique. Referential Data Integrity – Adding foreign keys to make sure relationships are maintained. Domain Data Integrity – Creating constraints or triggers enforce the range of data values. Again, we will be working with the…
Crafting Databases – Part 3
The CREATE TABLE statement is part of the Data Definition Language (DDL). It is used to define a structure to hold rows of data or tuples if we use Relational Algebra concepts. Each table is defined by a set of fields of a certain data type and precision. Data Types can be categorized as the following. Exact numerics Approximate numerics Character strings Unicode character strings Binary strings Date and time Other data types The power of a database comes from creating user defined datatypes (UDT). Some of the benefits of…
Crafting Databases – Part 2
The CREATE LOGIN and CREATE USER statements are part of the Data Definition Language (DDL) defined by Codd. Basically, a server login has to be defined so that person can connect to the server and a user login has to be created to map the server login to a particular database. I am going to continue my talk with the BSA hypothetical business problem from Part 1. Server logins allow access at the server level which can be defined using Windows Authentication or a SQL Server Authentication. Windows Authentication assumes…
Crafting Databases – Part 1
The CREATE DATABASE SQL statement is part of the Data Definition Language (DML) defined by Codd and is used define (CREATE), modify (ALTER) or remove (DROP) databases along with there associated files. Before I talk about crafting a database, I want to classify SQL statements by four known groups. Data Definition Language (DDL) statements are SQL statements that support the definition or declaration of database objects. CREATE ALTER DROP Data Manipulation Language (DML) is a vocabulary used to add, modify, query, or remove data from a SQL Server database. SELECT…
Selecting Data – Part 1
The SELECT reservered word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database as recordsets. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. All reserved words that I introduce below will be in CAPS below. Today, I would like to review simple SELECT queries using the AdventureWorks Sample database supplied by Microsoft. The most basic use of the SELECT statement is to return a constant as…