Best Interview Picks...

The Common Language Runtime

The CLR is a runtime execution engine of .NET that provides an environment to execute programs that are targeted at the .NET platform. It provides memory allocation, security, code verification, type verification, exception handling and garbage collection in the managed environment.

Strong Name

A Strong Name is a unique identifier given to an assembly using cryptography and a digital signature that is used to identify a particular assembly. An assembly is provided a strong name using the utility called sn.exe.

A strong name consists of the following.

· Name of the Assembly

· Digital Signature

· Version Number

· Culture Information


Global Assembly Cache

The Global Assembly Cache is a system wide storage of shared assemblies. Assemblies can be stored or moved to and from the Global Assembly Cache using a tool called GacUtil.


Satellite Assembly

When you wish to localize the application, it can be written in culture-neutral code and can distribute the localized modules in separate assemblies called satellite assemblies.


Assembly Manifest

The Assembly Manifest contains the information about an assembly. This includes the version number of the assembly, the list of the files that comprise the assembly, etc. An assembly manifest is contained in the dll file or the exe file itself.

Caching

Caching is a feature that stores the data in the memory for serving the incoming requests from the memory itself. Caching in ASP.NET can be of three types.

· Page Output Caching

· Page Fragment Caching

· Data Caching


Session State

A session is the period of a connection between a server and a client. The Session State allows storage of objects in a user’s session. A session can be stored in one of the following ways.

· InProc

· State Server

· SQL Server




Remoting

Remoting allows two processes, a Server and a Client, to inter-communicate in the same system, the same network or across networks. In Remoting, a server and client communicate using a Channel.


Biztalk Server

This is a set of Microsoft Server Applications that allow integration, automation and management of different server applications.




Read Users' Comments (0)

::: SharePoint Concepts :::

What is the concept of ghosting and unghosting in SharePoint?

ghosted pages :::
In SharePoint most of the site pages derive from templates. The custom pages only store the difference between them. The template is loaded in memory and applied to the custom pages on fly. In other words the template is stored in a cache. This definitely brings in performance and flexibility. Flexibility is in terms that when we change the template page it's applied to all custom pages. These pages are loaded from the file system. So pages which are loaded from the file system are termed as ghosted pages.

If the page data is loaded from the content database it's termed as unghosted pages.

Read Users' Comments (0)

Steps to building custom service application

Development tasks

create service application
create service application endpoint(WCF)
create service application installers
create service application proxy
create service application proxy installers
create service application consumers

Read Users' Comments (0)

ConfigureBCS

Steps to Configure BCS in sharepoint2010
========================================

1. Configure the site in sharepoint designer2010
2. select external content type from site objects
3. press add connection button and set up the external data source and select whatever the operation types which related to the datatable
4. press save button to upload content to the sharepoint server
5. from the site select external list under the list collection

Read Users' Comments (0)

Backup & Restore site collection

Backup & Restore site collection
================================

Backup ->
Can use -> Perform a site collection backup under Granular Backup or SharePoint 2010 management shell

Restore ->
SharePoint2010 management shell Only.

Cmd:
restore-spsite -identity "http://win-b9m88cn17s2" -path "C:\_backUPs\siteCollection.bak" -force

Read Users' Comments (0)

Choosing a binding in wcf service...

Read Users' Comments (0)

Facts

*** Can't nest aggregate function - error

Select Y,Q, Max(Sum) as MaxSum
FROM ( -- Calculate Sums
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, max(Sum(Amount)) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
) sq
GROUP BY Y,Q
ORDER BY Y,Q

*** -- Solution: Including Detail description

SELECT MaxQuery.Y, MaxQuery.Q, AllQuery.Category, MaxQuery.MaxSum as sales
FROM (-- Find Max Sum Per Year/Quarter
Select Y,Q, Max(Sum) as MaxSum
FROM ( -- Calculate Sums
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, Sum(Amount) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
) sq
GROUP BY Y,Q
) MaxQuery
JOIN (-- All Amount Query
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, Sum(Amount) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
)AllQuery
ON MaxQuery.Y = AllQuery.Y
AND MaxQuery.Q = AllQuery.Q
AND MaxQuery.MaxSum = AllQuery.Sum
ORDER BY MaxQuery.Y, MaxQuery.Q

Read Users' Comments (0)

Group All

looks like left outer join

Read Users' Comments (0)

Using Statistical functions & how affect null values when calculating Avg

Read Users' Comments (0)

Using the New Windowing Functions

1. SELECT ProductID, TransactionDate,
Row_Number() OVER(ORDER BY TransactionDate) as RowNumDate,
Row_Number() OVER(ORDER BY ProductID) + 5000 as RowNumiD
FROM Production.TransactionHistory

-- not permitted
Where Row_Number() OVER(ORDER BY TransactionDate) Between 10001 AND 20000
ORDER BY TransactionDate;

New feature in sql 2005, here, using Row_Number in where close is not permitted.

2. So if we want to use mid range of rownumbers use this way

-- Select mid-range of rownumbers & every 5th row
SELECT RowNum, ProductID, TransactionDate
FROM (SELECT ProductID, TransactionDate,
Row_Number() OVER(ORDER BY TransactionDate) as RowNum
FROM Production.TransactionHistory) sq
WHERE RowNum Between 10001 AND 20000
-- AND RowNum%5 = 0
ORDER BY RowNum ;

In here use sub query as a table and get back the result set

3. Ranking and Windowing - these are very helpful when we are reporting

SELECT SalesOrderId, CustomerId, TerritoryID,
ROW_NUMBER() OVER(ORDER BY CustomerId) AS RowNum,
RANK() OVER(ORDER BY CustomerId) AS [Rank],
DENSE_RANK() OVER(ORDER BY CustomerId) AS [DenseRank],
NTILE(5) OVER(ORDER BY CustomerId) AS NTile5,
RANK() OVER(PARTITION BY TerritoryID ORDER BY CustomerId) AS [Partition]
FROM Sales.SalesOrderHeader;

4. Sample Data - randomly select data


SELECT
SalesOrderId, CustomerId
FROM Sales.SalesOrderHeader TABLESAMPLE (10000 rows)

return random 8000, 11400, 12000 rows. it's not a bug at all and here this method is more powerful than Select Top 1000 way.

SELECT
SalesOrderId, CustomerId
FROM Sales.SalesOrderHeader TABLESAMPLE (10 percent)
ORDER BY CustomerId






Read Users' Comments (0)

Working with Correlated Subqueries

Read Users' Comments (0)

Common table expression

This is new feature to Sql Server 2005

Why we should use Common table expression :-

1. Can use multiple sub queries in user friendly manner
2. Avoid complexity
3. Initially define the sub query and after call this sub query again and again in base query multiple time like a table name

Read Users' Comments (0)

Best Practices in MVC - Performance aspect also

1. DO consider using asynchronous controllers for long running requests.

ASP.NET’s threading pool has a default limit of 12 concurrent worker threads per CPU. When the requests overload the server’s ability to process these requests, a queue is built up of requests. For example, any request which takes a considerable amount of time waiting for external resources, such as database or large file operations. These external requests block the thread they occupy for the entire wait period. When this queue gets too large (5000 requests pending), the server starts responding with 503 (server too busy) errors.

In ASP.NET 4 the number of concurrent threads is set by default to 5000. While it is possible to increase the default limits, there is a better way to mitigate long running requests from tying up threads, modifying the long running requests to run asynchronously. ASP.NET MVC enables you to implement asynchronous controllers for this purpose. For more information about how to implement an asynchronous controller, see Using an Asynchronous Controller in ASP.NET MVC.

2. DO use an OutputCache filter for static pages.

Use OutputCache attribute when you are returning less frequently updated data; a good candidate may be your home page. You can use this technique for both HTML and JSON data types. When using it, only specify the cache profile name; do not specify anything else. If you need to fine tune caching, use the output cache section of the Web.config file.

For example, the OutputCache attribute is attached to Dashboard action method in the following code.

[AcceptVerbs(HttpVerbs.Get), OutputCache(CacheProfile = "Dashboard")]
public ActionResult Dashboard(string userName, StoryListTab tab, OrderBy orderBy, int? page)
{
...
}
In the Web.config file, the duration is fine tuned to 15 seconds.

3. DON'T overuse session, instead use TempData for short lived (intra-request) storage.

4. DO consider partial page updates using AJAX for reducing bandwidth.

5. Performance Recommendations

Performance is a multi-faceted problem for web-sites, as a myriad of bottlenecks can affect performance including:

Database
--------

Inefficient queries
Incorrectly placed indexes
Non-normalized design

Bandwidth problems
------------------

Large request size (affected by individual large images, .css, .js, .html, etc.)
Content that references many other items, such as multiple script, CSS, or image files
Slow connection speed

Processing power
----------------

Server: expensive operations
Client: poorly written javascript


6. DO use ASP.NET special resource folders and resource files.

While writing your web pages add an ASP.NET project folder for globalized content (App_GlobalResources) and for localized content for a given view (App_LocalResources). In each of these folders, you should add a resource (.resx) file that you should name according to the controller name. In other words, if your controller is named SubmissionPipeline, the resource file should be named SubmissionPipeline.resx.

7. Localization and Globalization Recommendations

Globalization is the process of making a product multi-lingual, where localization is the process of adapting a global product for a particular language and country. To develop a web application that supports globalization and localization, keep at least one rule in mind. Do not use hard-code strings in views.


8. DO guard against common attack vectors.

Website security needs to concern all web developers writing enterprise class websites and services. There are a host of well known attack vectors that you should know about. These attack vectors include (but are not limited to):

Cross-site scripting (XSS) attacks
SQL injection
Cross-site Request Forgery (XSRF)
Improperly implementing model binding

Read Users' Comments (0)

Complex Join

Explain using family twin scenario:

These are the conditions(in dbo.Person table- Self join with multiple conditions) -
1. different personID so have to use not equal both two person ids
2. twins mother and father same
3. and date of birth not equal to null


Read Users' Comments (0)

Self Join - Using in hierarchy scenarios

Join with the same table again and again recursively. Following images illustrate this joining concept using family scenario.




Read Users' Comments (0)

Cross Join

Read Users' Comments (1)comments

Select Top Vs Select Top 5 WITH TIES

Read Users' Comments (0)

Working with Nulls & Coalesce

we cannot use (=) to evaluate some value to null

Have to use IS NULL


**** Coalesce Function ***

Ex :

Select coalesce(null, 1+null, 1+2, 'xx') -> output=3

Read Users' Comments (0)

convett date time

string dateTime = DateTime.Now.ToShortDateString();


DateTime dtdateTime = Convert.ToDateTime(dateTime);

CultureInfo currentCulture = (CultureInfo)Thread.CurrentThread.CurrentCulture.Clone();

if (System.Configuration.ConfigurationSettings.AppSettings["ClientDateFormat"] != null)
{
currentCulture.DateTimeFormat.ShortDatePattern =
System.Configuration.ConfigurationSettings.AppSettings["ClientDateFormat"];
}
else
{
currentCulture.DateTimeFormat.ShortDatePattern =
CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
}

string stringdtdateTime = dtdateTime.ToString(currentCulture.DateTimeFormat.ShortDatePattern);
DateTime dtdateTimeOutPut = Convert.ToDateTime(stringdtdateTime, currentCulture);

string dt = dtdateTimeOutPut.ToString("MM/dd/yy", DateTimeFormatInfo.InvariantInfo);

string xcv = "";

Read Users' Comments (0)

Get Trade scheme

SELECT ProductCriteriaLevel, ProductCriteria, QualifierType1, Qualifier1, JoinLogic, QualifierType2, Qualifier2, DateFrom, DateTo, TradeSchemeMode,
TradeScheme, ProductCode
FROM RD.TradeSchemeDetail

Read Users' Comments (0)

cal control







Enabled="True" TargetControlID="TextBox1">

Read Users' Comments (0)

cal control

Read Users' Comments (0)

for the calendar control - select year month

Read Users' Comments (0)

Transactins

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 3 - Developing Solutions
-- Lesson 5 - Transactions, Locking and Blocking
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Dirty Read Transactional Fault

-- Transaction 1
USE CHA2

go
BEGIN TRANSACTION
UPDATE Customer
SET Nickname = 'Transaction Fault'
WHERE CustomerID = 1

-- Transaction 2
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
USE CHA2
SELECT NickName
FROM Customer
WHERE CustomerID = 1

-- Transaction 1
COMMIT TRANSACTION


go

-----------------------------------------------------------
-- Non-Repeatable Read Transactional Fault

-- Transaction 1
USE CHA2

UPDATE Customer
SET Nickname = 'Non-Repeatable Read'
WHERE CustomerID = 1

-- Transaction 2
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED

USE CHA2
BEGIN TRANSACTION
SELECT NickName
FROM Customer
WHERE CustomerID = 1

COMMIT TRANSACTION





-----------------------------------------------------------
-- Phantom Row Transactional Fault

-- Transaction 1
USE CHA2;

UPDATE Customer
SET NickName = 'Missy'
WHERE CustomerID = 3;


-- Transaction 2
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;

USE CHA2;

BEGIN TRANSACTION;

SELECT CustomerID, FirstName, LastName, Nickname
FROM Customer
WHERE NickName = 'Missy';

COMMIT TRANSACTION;




-----------------------------------------------------------
-- Read Committed - Default Behavior

-- Transaction 1
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED

USE CHA2
BEGIN TRANSACTION

SELECT NickName
FROM Customer
WHERE CustomerID = 1

UPDATE Customer
SET Nickname = 'Pepper'
WHERE CustomerID = 1

COMMIT TRANSACTION;

-- Transaction 2
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED

USE CHA2
BEGIN TRANSACTION

SELECT NickName
FROM Customer
WHERE CustomerID = 1

UPDATE Customer
SET Nickname = 'Pepper'
WHERE CustomerID = 1

COMMIT TRANSACTION;








-------------------------------------------------
-- Controlling SQL Server Locking

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

DBCC USEROPTIONS

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

DBCC USEROPTIONS


--Locking Hints

USE OBXKites
UPDATE Product WITH (RowLock)
SET ProductName = ProductName + ' Updated'

EXEC sp_help ProductCategory

EXEC sp_indexoption 'ProductCategory.PK__ProductCategory__79A81403', 'AllowRowlocks', FALSE
EXEC sp_indexoption 'ProductCategory.PK__ProductCategory__79A81403', 'AllowPagelocks', FALSE





--------------------------------------------------
-- DeadLocks!

--Transaction 1
-- Step 1
USE OBXKites
BEGIN TRAN;
UPDATE Contact
SET LastName = 'Jorgenson2'
WHERE ContactCode = '101'

--Transaction 2
--Step 2
USE OBXKites
BEGIN TRAN
UPDATE Product
SET ProductName
= 'DeadLock Repair Kit'
WHERE Code = '1001'
UPDATE Contact
SET FirstName = 'Neals'
WHERE ContactCode = '101'
COMMIT

-- Step 3
UPDATE Product
SET ProductName = 'DeadLock Identification Tester'
WHERE Code = '1001'

COMMIT


---------------------------------------------
-- Deadlock with error handling

Use OBXKites
TranStart:

Begin Tran

Update Contact
Set LastName = '2Jorgenson2'
Where ContactCode = '101'

WaitFor Delay '00:00:05'

Update Product
Set ProductName = '2DeadLock Identification Tester'
Where ProductCode = '1001'
IF @@ERROR = 1205
BEGIN
PRINT 'Deadlock'
GOTO TranStart
END

COMMIT



----------------------------------------------------------------------------
-- Using Snapshot Isolation

USE Aesop;
ALTER DATABASE Aesop
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE Aesop
SET READ_COMMITTED_SNAPSHOT ON

BEGIN TRAN
UPDATE Fable
SET Title = 'Rocking with Snapshots test'
WHERE FableID = 2;

SELECT Title FROM FABLE WHERE FableID = 2

COMMIT TRAN
---Transaction 2
USE Aesop;

SELECT Title
FROM FABLE
WHERE FableID = 2

Read Users' Comments (0)

Indexing

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 3 - Developing Solutions
-- Lesson 4 - Indexing for Performance
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Examining Query Execution Plans

USE AdventureWorks

-- 1 Clustered Index Seek
SELECT *
FROM Production.TransactionHistory
WHERE TransactionID = 100777

-- 2 Bookmark Lookup
SELECT *
FROM Production.TransactionHistory
WHERE ReferenceOrderID = 55555

-- 3 Covering Index
SELECT TransactionID, ProductID, TransactionDate
FROM Production.TransactionHistory
WHERE ProductID = 319
AND Quantity >10 -- non SARG, uses Filter

Read Users' Comments (0)

UDF

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 3 - Developing Solutions
-- Lesson 3 - User Defined Functions
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Creating Scalar Functions

USE Tempdb
go
CREATE FUNCTION dbo.Multiply (@A INT, @B INT)
RETURNS INT
AS
BEGIN
RETURN @A * @B
End
go
SELECT dbo.Multiply (3,4)


USE ObxKites
SELECT dbo.fGetPrice('1006',GetDate(),DEFAULT)
SELECT dbo.fGetPrice('1001','5/1/2001',NULL)

---------------------------------------------------------
-- Working with Inline Table-Valued Functions


USE CHA2
go
CREATE FUNCTION fEventList ()
RETURNS Table
AS
RETURN(
SELECT dbo.CustomerType.Name AS Customer,
dbo.Customer.LastName, dbo.Customer.FirstName,
dbo.Customer.Nickname,
dbo.Event_mm_Customer.ConfirmDate, dbo.Event.Code,
dbo.Event.DateBegin, dbo.Tour.Name AS Tour,
dbo.BaseCamp.Name, dbo.Event.Comment
FROM dbo.Tour
INNER JOIN dbo.Event
ON dbo.Tour.TourID = dbo.Event.TourID
INNER JOIN dbo.Event_mm_Customer
ON dbo.Event.EventID = dbo.Event_mm_Customer.EventID
INNER JOIN dbo.Customer
ON dbo.Event_mm_Customer.CustomerID
= dbo.Customer.CustomerID
LEFT OUTER JOIN dbo.CustomerType
ON dbo.Customer.CustomerTypeID
= dbo.CustomerType.CustomerTypeID
INNER JOIN dbo.BaseCamp
ON dbo.Tour.BaseCampID = dbo.BaseCamp.BaseCampID)

SELECT LastName, Code, DateBegin
FROM dbo.fEventList()


-- Parameters
USE OBXKites
go

-- View with Where clause
CREATE VIEW vPricelist
AS
SELECT Code, Price.Price
FROM dbo.Price
JOIN dbo.Product P
ON Price.ProductID = P.ProductID
WHERE EffectiveDate =
(SELECT MAX(EffectiveDate)
FROM dbo.Price
WHERE ProductID = P.ProductID
AND EffectiveDate <= GetDate())

SELECT *
FROM vPriceList
WHERE Code = '1001'

-- UDF with Parameters
CREATE FUNCTION dbo.fPriceList (
@Code CHAR(10) = Null, @PriceDate DateTime)
RETURNS Table
AS
RETURN(
SELECT Code, Price.Price
FROM dbo.Price
JOIN dbo.Product P
ON Price.ProductID = P.ProductID
WHERE EffectiveDate =
(SELECT MAX(EffectiveDate)
FROM dbo.Price
WHERE ProductID = P.ProductID
AND EffectiveDate <= @PriceDate)
AND (Code = @Code
OR @Code IS NULL) )

SELECT * FROM dbo.fPriceList(DEFAULT, '2/20/2002')

SELECT * FROM dbo.fPriceList('1001', '2/20/2002')

------------------------------------------------------
-- Using Multi-Statement Table-Valued UDFs

CREATE FUNCTION fPriceAvg()
RETURNS @Price TABLE
(Code CHAR(10),
EffectiveDate DATETIME,
Price MONEY)
AS
BEGIN
INSERT @Price (Code, EffectiveDate, Price)
SELECT Code, EffectiveDate, Price
FROM Product
JOIN Price
ON Price.ProductID = Product.ProductID

INSERT @Price (Code, EffectiveDate, Price)
SELECT Code, Null, Avg(Price)
FROM Product
JOIN Price
ON Price.ProductID = Product.ProductID
GROUP BY Code
RETURN
END


SELECT *
FROM dbo.fPriceAvg()


---------------------------------------------------
-- Navigating a Hierarchy

Use Family

Select * from Person order by PersonID;

go
CREATE
-- alter
FUNCTION dbo.FamilyTree
(@PersonID CHAR(25))
RETURNS @Tree TABLE (PersonID INT, LastName VARCHAR(25), FirstName VARCHAR(25), Lv INT)
AS
BEGIN
DECLARE @LC INT
SET @LC = 1
-- insert the top level
INSERT @Tree
SELECT PersonID, LastName, FirstName, @LC
FROM dbo.Person with (NoLock)
WHERE PersonID = @PersonID

-- Loop through sub-levels
WHILE @@RowCount > 0
BEGIN
SET @LC = @LC + 1
-- insert the Class level
INSERT @Tree
SELECT Tree.PersonID, Tree.LastName, Tree.FirstName, @LC
FROM dbo.Person FamilyNode with (NoLock)
JOIN dbo.Person Tree with (NoLock)
ON FamilyNode.PersonID = Tree.MotherID
OR FamilyNode.PersonID = Tree.FatherID
JOIN @Tree CC
ON CC.PersonID = FamilyNode.PersonID
WHERE CC.Lv = @LC - 1
END
RETURN
END ;

-- end of function

Select * From dbo.FamilyTree(1);


-- Query With Recursive CTE (Common Table Expression)

WITH FamilyTree( LastName, FirstName, PersonID, lv)
AS (
-- Anchor
SELECT LastName, FirstName, PersonID, 1
FROM Person A
WHERE PersonID = 10

-- Recursive Call
UNION ALL
SELECT Node.LastName, Node.FirstName, Node.PersonID, lv + 1
FROM Person Node
JOIN FamilyTree ft
ON Node.MotherID = ft.PersonID
OR Node.FatherID = ft.PersonID
)
SELECT PersonID, LastName, FirstName, lv
FROM FamilyTree;

Select * From dbo.FamilyTree(1);


-----------------------------------------------------------------------
-- Having Fun with Apply

USE CHA2
go
CREATE FUNCTION fEventList2 (@CustomerID INT)
RETURNS Table
AS
RETURN(
SELECT dbo.CustomerType.Name AS Customer,
dbo.Customer.LastName, dbo.Customer.FirstName,
dbo.Customer.Nickname,
dbo.Event_mm_Customer.ConfirmDate, dbo.Event.Code,
dbo.Event.DateBegin, dbo.Tour.Name AS Tour,
dbo.BaseCamp.Name, dbo.Event.Comment
FROM dbo.Tour
INNER JOIN dbo.Event
ON dbo.Tour.TourID = dbo.Event.TourID
INNER JOIN dbo.Event_mm_Customer
ON dbo.Event.EventID = dbo.Event_mm_Customer.EventID
INNER JOIN dbo.Customer
ON dbo.Event_mm_Customer.CustomerID
= dbo.Customer.CustomerID
LEFT OUTER JOIN dbo.CustomerType
ON dbo.Customer.CustomerTypeID
= dbo.CustomerType.CustomerTypeID
INNER JOIN dbo.BaseCamp
ON dbo.Tour.BaseCampID = dbo.BaseCamp.BaseCampID
WHERE Customer.CustomerID = @CustomerID
)

SELECT C.LastName, Code, DateBegin, Tour
FROM Customer C
CROSS APPLY fEventList2(C.CustomerID)
ORDER BY C.LastName

Read Users' Comments (0)

T-Sql and Stored Procedures

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 3 - Developing Solutions
-- Lesson 1 - T-SQL and Stored Procedures
--
-- Paul Nielsen
-----------------------------------------------------------


-----------------------------------------------------------
-- Introducing T-SQL

-- the is an ANSI style comment
USE Family;

Select FirstName, LastName -- selects the columns
FROM Person -- the source table
Where LastName Like 'Hal%'; -- the row restriction

-- C style comments
/*
Order table Insert Trigger
Paul Nielsen
ver 1.0 July 21, 2006
Logic: etc.
ver 1.1: July 31, 2006, added xyz
*/

-- Debug Commands
Select 3;
Print 6;

Print 'Begining';
waitfor delay '00:00:02';
Print 'Done';

---------------------------------
-- Working with Variables

-- Variable Default and Scope
DECLARE @Test INT,
@TestTwo NVARCHAR(25);
SELECT @Test, @TestTwo ;

SET @Test = 1;
SET @TestTwo = 'a value';
SELECT @Test, @TestTwo;
go --terminates the variables

SELECT @Test as BatchTwo, @TestTwo;

-- Using the Set and Select Commands
USE Family

-- multiple rows & multiple columns
Declare @TempID INT,
@TempLastName VARCHAR(25);
SET @TempID = 99
SELECT @TempID = PersonID,
@TempLastName = LastName
FROM Person
ORDER BY PersonID
SELECT @TempID, @TempLastName;
-- No rows returned
Declare @TempID INT,
@TempLastName VARCHAR(25);
SET @TempID = 99;
SELECT @TempID = PersonID,
@TempLastName = LastName
FROM Person
WHERE PersonID = 100
ORDER BY PersonID;
SELECT @TempID, @TempLastName;

-- Using Variables Within SQL Queries
USE OBXKites;

DECLARE @ProductCode CHAR(10);
SET @ProductCode = '1001';

SELECT ProductName
FROM Product
WHERE Code = @ProductCode;



-- Multiple Assignment Variables
USE CHA2
DECLARE
@EventDates VARCHAR(1024)
SET @EventDates = ''

SELECT @EventDates = @EventDates
+ CONVERT(VARCHAR(15), a.d,107 ) + '; '
FROM (select DateBegin as [d]
from Event
join Tour
on Event.TourID = Tour.TourID
WHERE Tour.[Name] = 'Outer Banks Lighthouses') as a;

SELECT Left(@EventDates, Len(@EventDates)-1)
AS 'Outer Banks Lighthouses Events';

--------------------------------
-- Controlling Procedural Flow

-- If
IF 1 = 0
PRINT 'Line One';
PRINT 'Line Two';

-- If w/ begin/end block
IF 1 = 0
BEGIN
PRINT 'Line One';
PRINT 'Line Two';
END

-- IF Exists()
USE OBXKITES;
IF EXISTS(SELECT * FROM [ORDER] WHERE Closed = 0)
BEGIN;
PRINT 'Process Orders';
END;

-- While
DECLARE @Temp int;
SET @Temp = 0;

WHILE @Temp <3
BEGIN;
PRINT 'tested condition' + Str(@Temp);
SET @Temp = @Temp + 1;
END;

-- goto
GOTO ErrorHandler;
PRINT 'more code';
ErrorHandler:;
PRINT 'Logging the error';

-------------------------------
-- Examining SQL Server with Code

-- sp_help
USE OBXKites;
sp_help price;

-- Global Variables
Select @@Connections;
Select @@CPU_Busy;
Select @@Cursor_Rows;
Select @@DateFirst;
Select @@DBTS;
Select @@Error;
Select @@Fetch_Status;
Select @@Identity;
Select @@Idle;
Select @@IO_Busy;
Select @@LangID;
Select @@Language;
Select @@Lock_TimeOut;
Select @@Max_Connections;
Select @@Max_Precision;
Select @@Nestlevel;
Select @@Options;
Select @@Pack_Received;
Select @@Pack_Sent;
Select @@Packet_Errors;
Select @@ProcID;
Select @@RemServer;
Select @@RowCount;
Select @@ServerName;
Select @@ServiceName;
Select @@SPID;
Select @@TextSize;
Select @@TimeTicks;
Select @@Total_Errors;
Select @@Total_Read;
Select @@Total_Write;
Select @@TranCount;
Select @@Version;

-----------------------
-- Using Temp Tables

CREATE TABLE #ProductTemp (
ProductID INT PRIMARY KEY
);

INSERT INTO #ProductTemp VALUES ( 1);

SELECT *
FROM #ProductTemp

SELECT Name
FROM TempDB.dbo.SysObjects
WHERE Name Like '#Pro%'

-- Global Temp Table
IF NOT EXISTS(
SELECT * FROM Tempdb.dbo.Sysobjects
WHERE Name = '##TempWork')
CREATE TABLE ##TempWork(
PK INT,
Col1 INT
);

-- Table Variable
DECLARE @WorkTable TABLE (
PK INT PRIMARY KEY,
Col1 INT NOT NULL);

INSERT INTO @WorkTable (PK, Col1)
VALUES ( 1, 101);

SELECT PK, Col1
FROM @WorkTable;


--------------------------
-- Developing Dynamic SQL

-- Simple EXEC
USE Family;
EXEC ('Select LastName from Person Where PersonID = 12');

-- sp_executeSQL
EXEC sp_executeSQL
N'Select LastName from Person Where PersonID = @PersonSelect',
N'@PersonSelect INT',
@PersonSelect = 12;


-- Developing Dynamic SQL Code
USE OBXKites;

DECLARE
@SQL NVARCHAR(1024),
@SQLWhere NVARCHAR(1024),
@NeedsAnd BIT,

-- User Parameters
@ProductName VARCHAR(50),
@ProductCode VARCHAR(10),
@ProductCategory VARCHAR(50);

-- Initilize Variables
SET @NeedsAnd = 0;
SET @SQLWhere = '';

-- Simulate User's Requirements
SET @ProductName = NULL;
SET @ProductCode = 1001;
SET @ProductCategory = NULL;

-- Assembling Dynamic SQL

-- Set up initial SQL Select
IF @ProductCategory IS NULL
SET @SQL = 'Select ProductName from Product';
ELSE
SET @SQL = 'Select ProductName from Product
Join ProductCategory
on Product.ProductCategoryID
= ProductCategory.ProductCategoryID';

-- Build the Dynamic Where Clause
IF @ProductName IS NOT NULL
BEGIN;
SET @SQLWhere = 'ProductName = ' + @ProductName;
SET @NeedsAnd = 1;
END;

IF @ProductCode IS NOT NULL
BEGIN;
IF @NeedsAnd = 1
SET @SQLWhere = @SQLWhere + ' and ';
SET @SQLWhere = 'Code = ' + @ProductCode;
SET @NeedsAnd = 1;
END;

IF @ProductCategory IS NOT NULL
BEGIN;
IF @NeedsAnd = 1
SET @SQLWhere = @SQLWhere + ' and ';
SET @SQLWhere = 'ProductCategory = ' + @ProductCategory ;
SET @NeedsAnd = 1;
END;

-- Assemble the select and the where portions of the dynamic SQL
IF @NeedsAnd = 1
SET @SQL = @SQL + ' where ' + @SQLWhere;

Print @SQL;

EXEC sp_executeSQL @SQL
WITH RECOMPILE;


---------------------------------------------
-- Introducing Stored Procedures

-- Create, Alter, Drop
USE OBXKites;
go

CREATE PROCEDURE CategoryList
AS
SELECT ProductCategoryName, ProductCategoryDescription
FROM dbo.ProductCategory;
RETURN;
go

-- Executing the Sproc & Returning a Result Set
EXEC CategoryList;

-- Calling Remote Stored Procedures
EXEC [HPPRESENT\SECOND].OBXKites.dbo.pProductCategory_AddNew 'Food', 'Eat-ables'

-- Compiling Stored Procedures
CREATE PROCEDURE CategoryList
WITH RECOMPILE
AS
SELECT ProductCategoryName, ProductCategoryDescription
FROM dbo.ProductCategory;
RETURN;
go

EXEC sp_recompile CategoryList;

-------------------
-- Passing Parameters

-- Input Parameters
USE OBXKites;

go
CREATE PROCEDURE CategoryGet
(@CategoryName NVARCHAR(35))
AS
SELECT ProductCategoryName, ProductCategoryDescription
FROM dbo.ProductCategory
WHERE ProductCategoryName = @CategoryName;
go

EXEC CategoryGet @CategoryName='Kite';


-- Parameter Defaults
CREATE PROCEDURE pProductCategory_Fetch2(
@Search NVARCHAR(50) = NULL
)
-- If @Search = null then return all ProductCategories
-- If @Search is value then try to find by Name
AS
SET NOCOUNT ON;
SELECT ProductCategoryName, ProductCategoryDescription
FROM dbo.ProductCategory
WHERE ProductCategoryName = @Search
OR @Search IS NULL;
IF @@RowCount = 0
RAISERROR('Product Category ''%s'' Not Found.',14,1,@Search);
go

EXEC pProductCategory_Fetch2 'OBX';

EXEC pProductCategory_Fetch2;

--------------------------------------------
-- Returning Parameters

-- Output Parameters
USE OBXKites;
go
CREATE PROC GetProductName (
@ProductCode CHAR(10),
@ProductName VARCHAR(25) OUTPUT )
AS
SELECT @ProductName = ProductName
FROM dbo.Product
WHERE Code = @ProductCode;
go

--
USE OBXKITES;
DECLARE @ProdName VARCHAR(25);
EXEC GetProductName '1001', @ProdName OUTPUT;
PRINT @ProdName;

-- Using the Return Command
CREATE PROC IsItOK (
@OK VARCHAR(10) )
AS
IF @OK = 'OK'
RETURN 0
ELSE
RETURN -100;
go


DECLARE @ReturnCode INT;
EXEC @ReturnCode = IsITOK 'OK';
PRINT @ReturnCode;
EXEC @ReturnCode = IsItOK 'NotOK';
PRINT @ReturnCode;



----------------------
-- Iterating through rows


USE Family
DECLARE @Name VARCHAR(100)

-- Fast Forward Cursor / Function
-- 1
DECLARE cDetail CURSOR FAST_FORWARD READ_ONLY
FOR SELECT FirstName + ' ' + LastName
FROM Person
ORDER BY DateofBirth
-- 2
OPEN cDetail
-- 3
FETCH cDetail INTO @Name -- prime the cursor
PRINT @Name

WHILE @@Fetch_Status = 0
BEGIN
PRINT @Name
-- 3
FETCH cDetail INTO @Name -- fetch next
END

-- 4
CLOSE cDetail

-- 5
DEALLOCATE cDetail



----------------------
-- Handling Errors

-- Try...Catch
BEGIN TRY;
SET NOCOUNT ON;
SELECT 'Try One';
RAISERROR('Simulated Error', 16, 1);
Select 'Try Two';
END TRY
BEGIN CATCH;
SELECT 'Catch Block';
END CATCH ;
SELECT 'Try Three';


BEGIN TRY;
SET NOCOUNT ON;
SELECT 'Try One';
RAISERROR('Simulated Error', 16, 1) ;
Select 'Try Two';
END TRY

BEGIN CATCH
SELECT
ERROR_MESSAGE() AS [Message],
ERROR_PROCEDURE() AS [Procedure],
ERROR_LINE() AS Line,
ERROR_NUMBER() AS Number,
ERROR_SEVERITY() AS Severity,
ERROR_STATE() AS State;
END CATCH;
SELECT 'Try Three';

-- Without a Catch Block the Functions Return a Null
SELECT
ERROR_MESSAGE() AS [Message],
ERROR_PROCEDURE() AS [Procedure],
ERROR_LINE() AS Line,
ERROR_NUMBER() AS Number,
ERROR_SEVERITY() AS Severity,
ERROR_STATE() AS State;
-- Legacy @@Error Global Variable
USE Family;
UPDATE Person
SET PersonID = 1
Where PersonID = 2;
Print @@Error;
Print @@Error;

-- Saving @@Error to a Local Variable
USE Family;
DECLARE @err INT;

UPDATE Person
SET PersonID = 1
Where PersonID = 2;
SET @err = @@Error;

IF @err <> 0
Begin;
-- error handling code
Print @err;
End;

-- Legacy @@RowCount Global Variable
USE FAMILY;
UPDATE Person
SET LastName = 'Johnson'
WHERE PersonID = 100;

IF @@RowCount = 0
Begin;
-- error handling code
Print 'no rows affected';
End;

-- Raiserror

-- The Simple Raiserror Form
RAISERROR 5551212 'Unable to update Customer';

-- The Complete Raiserror Form
RAISERROR('Unable to update Customer', 14, 1);

-- Error Severity
RAISERROR('Print', 10,1);
RAISERROR('Info', 14,1);
RAISERROR('Warning', 15,1);
RAISERROR('Critical', 16,1);

-- Adding Variable Parameters to Message
RAISERROR ('Unable to update %s.', 14, 1, 'Customer');

-- Stored Messages
EXEC sp_addmessage 50001, 16, 'Unable to update %s';

EXEC sp_addmessage 50001, 16, 'Still unable to update %s', @Replace = 'Replace';


SELECT *
FROM sys.messages
WHERE message_id > 50000;

SELECT 'EXEC sp_addmessage '
+ Cast(message_id as VARCHAR(7))
+ ', ' + Cast(Severity as VARCHAR(2))
+ ', ''' + [text] + ''';'
FROM sys.messages
WHERE message_id > 50000;

-- code test
EXEC sp_addmessage 50001, 16, 'Still unable to update %s';

EXEC sp_dropmessage 50001;

-- With Log
RAISERROR ('Unable to update %s.', 14, 1, 'Customer')
WITH LOG;

-- Fatal Errors
SELECT Error, Severity, Description
FROM Master.dbo.SysMessages
WHERE Severity > 16;


---------------------------------
-- Controlling Transactions

/*
BEGIN TRANSACTION;
code;
COMMIT TRANSACTION; or ROLLBACK TRANSACTION;
*/

-- sample code only
BEGIN TRAN;
BEGIN TRY;
-- debit account 1
INSERT Account (AccountID, Amount, TransactionDate)
VALUES(123, 100, '12/11/2006');
-- credit account 1
INSERT Account (AccountID, Amount, TransactionDate)
VALUES(456, -100, '12/11/2006');
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
RAISERROR('Transaction incomplete - not committed to disk', 14, 1);
END CATCH;




Read Users' Comments (0)

Triggers

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 3 - T-SQL Programming
-- Lesson 2 - Triggers and Audit Trail
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Introducing After Triggers

USE FAMILY
go
CREATE TRIGGER TriggerOne ON Person
AFTER Insert -- old syntax FOR
AS
PRINT 'In the After Trigger'
go

INSERT Person(PersonID, LastName, FirstName, Gender)
VALUES (50, 'Flanigan', 'Brandie','F')

-----------------------------------------------------------------------
-- Using "Instead of" Triggers

USE FAMILY

CREATE TRIGGER TriggerTwo ON Person
INSTEAD OF Insert
AS
PRINT 'In the Instead-of Trigger'
go

INSERT Person(PersonID, LastName, FirstName, Gender)
VALUES (99, 'McDoogle', 'John','M')

SELECT *
FROM Person
WHERE PersonID = 99

DROP TRIGGER TriggerTwo

/*
Transaction Flow
1 - Identity Insert Check
2 - Nullability Constraint
3 - Data Type Check
4 - Instead of Trigger
5 - Primary Key Constraint
6 - Check Constraints
7 - Foreign Key Constraint
8 - DML execution and update to Transaction Log
9 - After Trigger
10 - Commit Transaction
*/

--------------------------------------------------------
-- Disabling Triggers

ALTER TABLE Person
DISABLE TRIGGER TriggerOne

SELECT OBJECTPROPERTY(OBJECT_ID('TriggerOne'),'ExecIsTriggerDisabled')


ALTER TABLE Person
ENABLE TRIGGER TriggerOne

SELECT OBJECTPROPERTY(OBJECT_ID('TriggerOne'),'ExecIsTriggerDisabled')


-- Viewing Trigger Status
SELECT SubString(S2.Name,1,30) as [Table],
SubString(S.Name, 1,30) as [Trigger],
CASE (SELECT -- Correlated subquery
OBJECTPROPERTY(OBJECT_ID(S.Name),
'ExecIsTriggerDisabled'))
WHEN 0 THEN 'Enabled'
WHEN 1 THEN 'Disabled'
END AS Status
FROM Sysobjects S
JOIN Sysobjects S2
ON S.parent_obj = S2.ID
WHERE S.Type = 'TR'
ORDER BY [Table], [Trigger]


-------------------------------------------------------
-- Using the Update() function

ALTER TRIGGER TriggerOne ON Person
AFTER Insert, Update
AS
IF Update(LastName)
PRINT 'You modified the LastName column'
ELSE
PRINT 'The LastName column is untouched.'


UPDATE Person
SET LastName = 'Johnson'
WHERE PersonID = 25

UPDATE Person
SET FirstName = 'Joe'
WHERE PersonID = 25


-----------------------------------------------
-- Selecting Transaction Data

USE Family

ALTER TRIGGER TriggerOne ON Person
AFTER Insert, Update
AS
SET NoCount ON
IF Update(LastName)
SELECT 'You modified the LastName column to ' + Inserted.LastName
FROM Inserted


UPDATE Person
SET LastName = 'Johnson'
WHERE PersonID = 32

ALTER TRIGGER TriggerOne ON Person
AFTER Insert, Update
AS
SELECT CAST(D.PersonID as VARCHAR(4)) + ': ' + D.LastName + ' changed to ' + I.LastName
FROM Inserted I
JOIN Deleted D
ON I.PersonID = D.PersonID

UPDATE Person
SET LastName = 'Carter'
WHERE LastName = 'Johnson'



-------------------------------------------------
-- Nesting and Recursion

-- Nested - Server wide setting
EXEC sp_configure 'Nested Triggers', 1
Reconfigure



-- Recursive Trigger - Database wide setting
USE OBXKites

ALTER DATABASE OBXKites SET RECURSIVE_TRIGGERS ON

ALTER TABLE Product
ADD Created DATETIME NOT NULL DEFAULT GetDate()
ALTER TABLE Product
ADD Modified DATETIME NOT NULL DEFAULT GetDate()



-- assumes created and modified date columns have been added to the Product table.
CREATE TRIGGER Products_ModifiedDate ON dbo.Product
AFTER UPDATE
AS
SET NoCount ON
-- Check nest level
PRINT Trigger_NestLevel() -- for demo purposes, remove from production code
If Trigger_NestLevel() > 1
RETURN
-- block manual update of created/modified columns
IF (UPDATE(Created) or UPDATE(Modified))
AND Trigger_NestLevel() = 1
BEGIN
RAISERROR('Update failed.', 16, 1)
ROLLBACK
RETURN
END
-- Update the Modified date
UPDATE Product
SET modified = getdate()
FROM Product
JOIN Inserted
ON Product.ProductID = Inserted.ProductID
-- end of trigger
go

UPDATE PRODUCT
SET ProductName = 'Modifed Trigger'
WHERE Code = '1002'

SELECT Code, ProductName, Created, Modified
FROM Product
WHERE Code = '1002'


--------------------------------------------------------
-- Enforcing Complex RI

-- Complex Business Rules Validation
-- Custom RI

-- ensure the guide's qual date is good and the revoke date is null
-- for the given guideID and TourID

USE CHA2
go
CREATE TRIGGER LeadQualied ON Event_mm_Guide
AFTER INSERT, UPDATE
AS
SET NoCount ON
IF EXISTS(
SELECT *
FROM Inserted
JOIN dbo.Event
ON Inserted.EventID = Event.EventID
LEFT JOIN dbo.Tour_mm_Guide
ON Tour_mm_Guide.TourID = Event.TourID
AND Inserted.GuideID = Tour_mm_Guide.GuideID
WHERE
Inserted.IsLead = 1
AND
(QualDate > Event.DateBegin
OR
RevokeDate IS NOT NULL
OR
QualDate IS NULL )
)
BEGIN
RAISERROR('Lead Guide is not Qualified.',16,1)
ROLLBACK TRANSACTION
END
go


-- test

INSERT Event_mm_Guide (EventID, GuideID, IsLead)
VALUES (10, 1, 1)

INSERT Event_mm_Guide (EventID, GuideID, IsLead)
VALUES (10, 2, 1)


-------------------------------------------------------
-- Auditing Data Changes

USE OBXKites

CREATE TABLE dbo.Audit (
AuditID UNIQUEIDENTIFIER RowGUIDCol NOT NULL
CONSTRAINT DF_Audit_AuditID DEFAULT (NEWID())
CONSTRAINT PK_Audit PRIMARY KEY NONCLUSTERED (AuditID),
AuditDate DATETIME NOT NULL,
SysUser VARCHAR(50) NOT NULL,
Application VARCHAR(50) NOT NULL,
TableName VARCHAR(50)NOT NULL,
Operation CHAR(1) NOT NULL,
PrimaryKey VARCHAR(50) NOT NULL,
RowDescription VARCHAR(50) NULL,
SecondaryRow VARCHAR(50) NULL,
[Column] VARCHAR(50) NOT NULL,
OldValue VARCHAR(50) NULL,
NewValue VARCHAR(50) NULL
)

--------------------------------------------------------------
-- Fixed Audit Trail Trigger

Use OBXKites

Go

CREATE TRIGGER Product_Audit
ON dbo.Product
AFTER Insert, Update
NOT FOR REPLICATION
AS

DECLARE @Operation CHAR(1)

IF EXISTS(SELECT * FROM Deleted)
SET @Operation = 'U'
ELSE
SET @Operation = 'I'

IF UPDATE(ProductCategoryID)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'ProductCategoryID',
OPC.ProductCategoryName, NPC.ProductCategoryName
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.ProductCategoryID
<> Deleted.ProductCategoryID
-- fetch ProductCategory Names
LEFT OUTER JOIN dbo.ProductCategory OPC
ON Deleted.ProductCategoryID = OPC.ProductCategoryID
JOIN dbo.ProductCategory NPC
ON Inserted.ProductCategoryID = NPC.ProductCategoryID
IF UPDATE(Code)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'Code',
Deleted.Code, Inserted.Code
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.Code <> Deleted.Code

IF UPDATE(ProductName)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'Name',
Deleted.ProductName, Inserted.ProductName
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.ProductName <> Deleted.ProductName

IF UPDATE(ProductDescription)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'ProductDescription',
Deleted.ProductDescription, Inserted.ProductDescription
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.ProductDescription <> Deleted.ProductDescription

IF UPDATE(ActiveDate)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'ActiveDate',
Deleted.ActiveDate, Inserted.ActiveDate
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.ActiveDate != Deleted.ActiveDate

IF UPDATE(DiscontinueDate)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'DiscontinueDate',
Deleted.DiscontinueDate, Inserted.DiscontinueDate
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.DiscontinueDate != Deleted.DiscontinueDate

go

-- Test the Fixed Audit Trail

EXEC pProduct_AddNew 'Kite', 200, 'The MonstaKite', 'Man what a big Kite!'

SELECT TableName, RowDescription, [Column], NewValue
FROM Audit

SELECT *
FROM Audit

UPDATE dbo.Product
SET ProductDescription = 'Biggie Sized'
WHERE Code = 200

SELECT AuditDate, OldValue, NewValue
FROM dbo.Audit
WHERE TableName = 'Product'
AND RowDescription = '200'
AND [Column] = 'ProductDescription'

--Rolling back an Audit Trail change

CREATE PROCEDURE pAudit_RollBack (
@AuditID UNIQUEIDENTIFIER)
AS
SET NoCount ON

DECLARE
@SQLString NVARCHAR(4000),
@TableName NVARCHAR(50),
@PrimaryKey NVARCHAR(50),
@Column NVARCHAR(50),
@NewValue NVARCHAR(50)

SELECT
@TableName = TableName,
@PrimaryKey = PrimaryKey,
@Column = [Column],
@NewValue = OldValue
FROM dbo.Audit
WHERE AuditID = @AuditID

SET @SQLString =
'UPDATE ' + @TableName
+ ' SET ' + @Column + ' = ''' + @NewValue +''''
+ ' WHERE ' + @TableName + 'ID = ''' + @PrimaryKey + ''''

EXEC sp_executeSQL @SQLString
go

--Test RollBack

DECLARE @AuditRollBack UNIQUEIDENTIFIER

SELECT @AuditRollBack = AuditID
FROM dbo.Audit
WHERE TableName = 'Product'
AND RowDescription = '200'
AND OldValue = 'Man what a big Kite!'

EXEC pAudit_RollBack @AuditRollBack

SELECT ProductDescription
FROM dbo.Product
WHERE Code = 200

Read Users' Comments (0)

Modifing Data

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 8 - Modifying Data
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Inserting Data

USE CHA2;

-- INSERT/VALUES
INSERT INTO dbo.Guide (LastName, FirstName, Qualifications)
VALUES ('Smith', 'Dan', 'Diver, Whitewater Rafting');

INSERT INTO dbo.Guide (FirstName, LastName, Qualifications)
VALUES ('Jeff', 'Davis', 'Marine Biologist, Diver');

INSERT INTO dbo.Guide (FirstName, LastName)
VALUES ('Tammie', 'Commer');

SELECT * from dbo.Guide;

-- INSERT/VALUES without a insert column listing
INSERT dbo.Guide
VALUES ('Jones', 'Lauren', 'First Aid, Rescue/Extraction','6/25/59','4/15/01');

SELECT GuideID, LastName, FirstName, Qualifications
FROM dbo.Guide;


-- INSERT/VALUES w/ expression
INSERT dbo.Guide (FirstName, LastName, Qualifications)
VALUES ('Greg', 'Wilson' , 'Rock Climbing' + ', ' + 'First Aid');

SELECT * from dbo.Guide;


-- INSERT/SELECT

USE OBXKites;
-- Using a fresh copy of OBXKites without population

--(Corrected from the text - added ContactCode)
INSERT dbo.Contact (FirstName, ContactCode, LastName, CompanyName)
SELECT FirstName, LastName, GuideID, 'Cape Hatteras Adv.'
FROM CHA2.dbo.Guide;

SELECT ContactID, FirstName AS FIRST, LastName AS LAST , CompanyName
FROM dbo.Contact;


-- INSERT/Exec

Use CHA2
Go
-- create the sample stored procedure
CREATE PROC ListGuides
AS
SET NOCOUNT ON

-- result set 1
SELECT FirstName, LastName
FROM dbo.Guide;

-- result set 2
SELECT FirstName, LastName
FROM OBXKites.dbo.Contact
RETURN; -- end of sproc
go

-- test the sample stored procedure
Exec ListGuides;

go
-- create a table for the insert
CREATE TABLE dbo.GuideSample
(FirstName VARCHAR(20),
LastName VARCHAR(20) );

-- the insert / exec statement
INSERT GuideSample ( FirstName, LastName)
EXEC ListGuides;

-- check to see that the insert worked
SELECT * FROM GuideSample;

-- SELECT INTO
USE CHA2;

-- sample code for setting the bulk-logged behavior
Alter DATABASE CHA2 SET RECOVERY FULL
EXEC SP_DBOPTION 'CHA2', 'select into/bulkcopy', 'TRUE';
go

-- the select/into statement
SELECT * INTO dbo.GuideList
FROM dbo.Guide
ORDER BY Lastname, FirstName;

Go
-- viewing the data structure of the new table
sp_help GuideList;

--testing the identity column of the new table
INSERT dbo.Guidelist (LastName, FirstName, Qualifications)
VALUES('Nielsen', 'Paul','trainer');

SELECT GuideID, LastName, FirstName
FROM dbo.GuideList;

-- INSERTing Identity Column Primary Keys
USE CHA2;

INSERT dbo.Guide (GuideID, FirstName, LastName)
VALUES (10, 'Bill', 'Wilson');

SET IDENTITY_INSERT Guide On;

INSERT dbo.Guide (GuideID, FirstName, LastName)
VALUES (10, 'Bill', 'Wilson');

INSERT dbo.Guide (GuideID, FirstName, LastName)
VALUES (7, 'Sue', 'Atlas');

SET IDENTITY_INSERT Guide Off;

INSERT dbo.Guide ( FirstName, LastName)
VALUES ( 'Arnold', 'Bistier');

SELECT GuideID, FirstName, LastName from dbo.Guide;

-----------------------------------------------------------
-- Updating Data

-- Updating a single column of a single row
USE CHA2
UPDATE dbo.Guide
SET Qualifications = 'Spelunking, Cave Diving, Rock Climbing, First Aid, Navigation'
Where GuideID = 6;

SELECT GuideID, LastName, Qualifications
FROM dbo.Guide
WHERE GuideID = 6;

-- Global Search and Replace

USE Family;

UPDATE Person
SET LastName = REPLACE(Lastname, 'll', 'qua');

SELECT lastname
FROM Person;


-- A complex update with expression

USE Tempdb;

CREATE TABLE dbo.Dept (
DeptID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
DeptName VARCHAR(50) NOT NULL,
RaiseFactor NUMERIC(4,2)
)
ON [Primary];
go

Create TABLE dbo.Employee (
EmployeeID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
DeptID INT FOREIGN KEY REFERENCES Dept,
LastName VARCHAR(50) NOT NULL,
FirstName VARCHAR(50) NOT NULL,
Salary INT,
PerformanceRating NUMERIC(4,2),
DateHire DATETIME,
DatePosition DATETIME
)
ON [Primary];
go
-- build the sample data
INSERT dbo.Dept VALUES ('Engineering', 1.2);
INSERT dbo.Dept VALUES ('Sales',.8);
INSERT dbo.Dept VALUES ('IT',2.5);
INSERT dbo.Dept VALUES ('Manufacturing',1.0);
go
INSERT dbo.Employee VALUES( 1, 'Smith', 'Sam', 54000, 2.0, '1/1/97', '4/1/2001' );
INSERT dbo.Employee VALUES( 1, 'Nelson', 'Slim', 78000, 1.5, '9/1/88', '1/1/2000' );
INSERT dbo.Employee VALUES( 2, 'Ball', 'Sally', 45000, 3.5, '2/1/99', '1/1/2001' );
INSERT dbo.Employee VALUES( 2, 'Kelly', 'Jeff', 85000, 2.4, '10/1/83','9/1/1998' );
INSERT dbo.Employee VALUES( 3, 'Guelzow', 'Dave', 120000, 4.0, '7/1/95', '6/1/2001' );
INSERT dbo.Employee VALUES( 3, 'Cliff', 'Melissa', 95000, 1.8, '2/1/99', '9/1/1997' );
INSERT dbo.Employee VALUES( 4, 'Reagan', 'Frankie', 75000, 2.9, '4/1/00', '4/1/2000' );
INSERT dbo.Employee VALUES( 4, 'Adams', 'Hank', 34000, 3.2, '9/1/98', '9/1/1998' );
go

-- assume raise date is 5/1/2002
SELECT * from dbo.Dept;
SELECT * from dbo.Employee;

-- test required data
SELECT LastName, Salary,
DateDiff(yy, DateHire, '5/1/2002') as YearsCompany,
DateDiff(mm, DatePosition, '5/1/2002') as MonthPosition,
CASE
WHEN Employee.PerformanceRating >= 2 THEN Employee.PerformanceRating
ELSE 0
END as Performance,
Dept.RaiseFactor
FROM dbo.Employee
JOIN dbo.Dept
ON Employee.DeptID = Dept.DeptID;

-- Test the raise amount
SELECT LastName,
(2 + (((DateDiff(yy, DateHire, '5/1/2002') * .1)
+ (DateDiff(mm, DatePosition, '5/1/2002') * .02)
+ (CASE
WHEN Employee.PerformanceRating >= 2 THEN Employee.PerformanceRating
ELSE 0
END * .5 ))
* Dept.RaiseFactor))/100 as EmpRaise
FROM dbo.Employee
JOIN dbo.Dept
ON Employee.DeptID = Dept.DeptID;

-- Perform the Update
Update Employee Set Salary = Salary * (1 +
(2 + (((DateDiff(yy, DateHire, '5/1/2002') * .1)
+ (DateDiff(mm, DatePosition, '5/1/2002') * .02)
+ (CASE
WHEN Employee.PerformanceRating >= 2 THEN Employee.PerformanceRating
ELSE 0
END * .5 ))
* Dept.RaiseFactor))/100 )
FROM dbo.Employee
JOIN dbo.Dept
ON Employee.DeptID = Dept.DeptID;


SELECT FirstName, LastName, Salary
FROM dbo.Employee;
Go

Drop Table dbo.Employee;
Go
Drop table dbo.Dept ;

-----------------------------------------------------------
-- Deleting Data

USE OBXKites;

DELETE FROM OBXKites.dbo.Product;
-- w/o WHERE clause deletes all rows

DELETE FROM dbo.Product
WHERE ProductID = 'DB8D8D60-76F4-46C3-90E6-A8648F63C0F0';

-- delete all the product in the books category
DELETE Product
FROM dbo.Product
JOIN ProductCategory
ON Product.ProductCategoryID
= ProductCategory.ProductCategoryID
WHERE ProductcategoryName = 'Video';

-- Building a foreign key with referential integrity that cascades deletes
USE CHA2;

CREATE TABLE dbo.Event_mm_Guide (
EventGuideID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
EventID INT NOT NULL
FOREIGN KEY REFERENCES dbo.Event ON DELETE CASCADE,
GuideID INT NOT NULL
FOREIGN KEY REFERENCES dbo.Guide ON DELETE CASCADE,
LastName VARCHAR(50) NOT NULL,
)
ON [Primary];
go


--ANSI Standard alternative to Delete From
DELETE FROM Table1 a
WHERE EXISTS (SELECT *
FROM Table2 b
WHERE
EMPL_STATUS = 'A'
AND
a.EMPLID = b.EMPLID
);

-- Truncate Table
TRUNCATE TABLE dbo.InventoryTransaction;


-----------------------------------------------------------
-- Using the Output clause

USE CHA2

INSERT dbo.Guide (LastName, FirstName, Qualifications)
OUTPUT Inserted.*
VALUES('Nielsen', 'Paul', 'diver');

UPDATE dbo.Guide
SET Qualifications = 'Scuba'
OUTPUT Deleted.Qualifications as OldQuals, Inserted.Qualifications as NewQuals
Where GuideID = 3;

-- with table variable
DECLARE @DeletedGuides TABLE (
GuideID INT,
LastName VARCHAR(50),
FirstName VARCHAR(50)
);

DELETE dbo.Guide
OUTPUT Deleted.GuideID, Deleted.LastName, Deleted.FirstName
INTO @DeletedGuides
WHERE GuideID = 2;

SELECT * FROM @DeletedGuides;



Read Users' Comments (0)

Distributing Queries

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 7 - Distributed Queries
--
-- Paul Nielsen
-----------------------------------------------------------

-------------------------------------------------
-- Linking to External Data Sources

EXEC sp_addlinkedserver
@server = 'HPPRESENT\SECOND',
@srvproduct = 'SQL Server';

EXEC sp_addlinkedserver
@server = 'nyack',
@datasrc = 'HPPRESENT\SECOND',
@srvproduct = '',
@provider='SQLOLEDB';

-- Viewing Linked Servers
SELECT [Name], Product, Provider, Data_source
FROM sys.servers
WHERE Is_Linked = 1;

-- Drop a linked server
EXEC sp_DropServer @server = 'Nyack';

-- Distributed Security and Logins
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'HPPRESENT\SECOND',
@useself = 'false',
@locallogin = 'HPPRESENT\total',
@rmtuser = 'sa',
@rmtpassword = 'P@s$w0rD';

-- Linking with Excel
EXEC sp_droplinkedsrvlogin 'CHA1_Schedule', NULL
EXEC sp_DropServer @server = 'CHA1_Schedule';

EXEC sp_addlinkedserver
@server = 'CHA1_Schedule',
@srvproduct = 'Excel',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'C:\SQLData\CHA1_Schedule.xls',
@provstr = 'Excel 5.0'
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'CHA1_Schedule',
@useself = 'false';

-- Linking with Microsoft Access
EXEC sp_DropServer @server = 'CHA1_Customers';
EXEC sp_addlinkedserver
'CHA1_Customers',
'Access 2003',
'Microsoft.Jet.OLEDB.4.0',
'C:\SQLData\CHA1_Customers.mdb';


----------------------------------------
-- Executing Distributed Queries

-- Accessing a Local SQL Server Database
USE CHA2;

SELECT LastName, FirstName
FROM OBXKites.dbo.Contact;

SELECT LastName, FirstName
FROM OBXKites..Contact;


-- Four Part Name
-- to SQL Server
SELECT LastName, FirstName
FROM [HPPresent\Second].Family.dbo.person;

-- to Excel
SELECT DISTINCT [Base Camp]
FROM CHA1_Schedule...[Base_Camp]
WHERE [Base Camp] IS NOT NULL;

-- to Access
SELECT *
FROM CHA1_Customers...Customers

UPDATE CHA1_Customers...Customers
SET ContactLastName = 'Wilson'
WHERE CustomerID = 1;

-- Four Part Name Black Box
Select o.orderid, c.contactcode, o.ordernumber, quantity
from [HPPresent\Second].obxkites.dbo.orderdetail od
join [HPPresent\Second].obxkites.dbo.[order] o
on o.orderid = od.orderid
join [HPPresent\Second].obxkites.dbo.contact c
on o.contactid = c.contactid
where
-- comment out combinations of where clause conditions
c.contactcode = '102'
and o.ordernumber = 3
and o.Orderid = '289623A7-09B8-4E9B-A2A3-075F9E6C34AC';

-- without linked server - SQL Server
SELECT FirstName, Gender
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=HPPresent\Second;Trusted_Connection=yes;'
).Family.dbo.Person;

-- without linked server - Access
SELECT ContactFirstName, ContactLastName
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source = C:\SQLData\CHA1_Customers.mdb')...Customers;
-----------------------------------------------
-- Using Pass-Through Queries

--OpenQuery()
SELECT *
FROM OPENQUERY(CHA1_Schedule,
'SELECT * FROM Tour WHERE Tour = "Gauley River Rafting"');

UPDATE -- to Excel
OPENQUERY(CHA1_Schedule, 'SELECT * FROM Tour WHERE Tour = "Gauley River Rafting"')
SET [Base Camp] = 'Ashville'
WHERE Tour = 'Gauley River Rafting';

-- OpenRowSet
SELECT *
FROM OPENROWSET ('SQLNCLI', 'Server=hppresent\second;Trusted_Connection=yes;',
'SELECT LastName, FirstName
FROM Family.dbo.person;')


-------------------------------------------------------
-- Controlling Distributed Transactions

USE Family;
SET xact_abort on;
BEGIN DISTRIBUTED TRANSACTION;

UPDATE Person
SET LastName = 'Johnson'
WHERE PersonID = 10;

UPDATE [hppresent\second].Family.dbo.Person
SET LastName = 'Johnson'
WHERE PersonID = 10;

COMMIT;









Read Users' Comments (0)

Views

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 6 - Selecting Data through Views
--
-- Paul Nielsen
-----------------------------------------------------------


-- Creating the View

USE CHA2

IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'vEventList')
DROP VIEW dbo.vEventList

go
CREATE VIEW dbo.vEventList
AS
SELECT dbo.CustomerType.Name AS Customer,
dbo.Customer.LastName, dbo.Customer.FirstName,
dbo.Customer.Nickname,
dbo.Event_mm_Customer.ConfirmDate, dbo.Event.Code,
dbo.Event.DateBegin, dbo.Tour.Name AS Tour,
dbo.BaseCamp.Name, dbo.Event.Comment
FROM dbo.Tour
INNER JOIN dbo.Event
ON dbo.Tour.TourID = dbo.Event.TourID
INNER JOIN dbo.Event_mm_Customer
ON dbo.Event.EventID = dbo.Event_mm_Customer.EventID
INNER JOIN dbo.Customer
ON dbo.Event_mm_Customer.CustomerID
= dbo.Customer.CustomerID
LEFT OUTER JOIN dbo.CustomerType
ON dbo.Customer.CustomerTypeID
= dbo.CustomerType.CustomerTypeID
INNER JOIN dbo.BaseCamp
ON dbo.Tour.BaseCampID = dbo.BaseCamp.BaseCampID
go

SELECT * FROM dbo.vEventList


-- Where
SELECT *
FROM dbo.vEventList
WHERE (Code = '01-016')


-- Order By

SELECT *
FROM vEventList
ORDER BY LastName, FirstName
go

-----------------------------------------------------------
-- Preventing Disappearing Rows

go
CREATE VIEW vCapeHatterasTour
AS
SELECT [Name], BaseCampID
FROM Tour
WHERE BaseCampID = 2

go
SELECT * FROM vCapeHatterasTour

INSERT vCapeHatterasTour ([Name], BaseCampID)
VALUES ('Blue Ridge Parkway Hike', 1)

SELECT * FROM vCapeHatterasTour

-- with check option

DELETE vCapeHatterasTour
WHERE [Name] = 'Blue Ridge Parkway Hike'
go
ALTER VIEW vCapeHatterasTour
AS
SELECT [Name], BaseCampID
FROM Tour
WHERE BaseCampID = 2
WITH CHECK OPTION
go

INSERT vCapeHatterasTour ([Name], BaseCampID)
VALUES ('Blue Ridge Parkway Hike', 1)
-----------------------------------------------------------
-- Protecting from Schema Changes

-- With Schema Binding

Use Tempdb
go
IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'vTest')
DROP View dbo.vTest
go
IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'Test')
DROP TABLE dbo.Test

go
CREATE TABLE dbo.Test (
[Name] NVARCHAR(50)
)
go

CREATE VIEW dbo.vTest
WITH SCHEMABINDING
AS
SELECT [Name] FROM dbo.Test

go
ALTER TABLE dbo.Test
ALTER COLUMN [Name] NVARCHAR(100)

-- Correcting schema/view out-of-sync
USE CHA2
sp_refreshview 'vCapeHatterasTour'

-----------------------------------------------------------
-- Nesting Views
USE CHA2
go

IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'vEventList30days')
DROP VIEW dbo.vEventList30days
go

CREATE VIEW dbo.vBaseCampCount
AS
SELECT dbo.vEventList.Name as BaseCamp, Count(*) as [Attendees]
FROM dbo.vEventList
GROUP BY dbo.vEventList.Name





Read Users' Comments (0)