понедельник, 31 января 2011 г.

Stop or Offline ASP.NET web application


The reason why you want to stop ASP.NET application could be if you do big website upgrade, which could include updating of pages, database, dlls etc. This process can take some time. If visitor tries to see pages, could experience errors. Sounds better to place a message that site is down for maintenance and that will be live again at certain time.
One more reason could be to unlock resources for updating. Some resources are locked while application is running, like SQL Server Express .mdf file in App_Data folder or MS Access .mdb file. You can't in the same time upload new database file and have active connections.
There are three common ways to make ASP.NET application offline:
  1. Using IIS to stop website
  2. Use App_Offline.htm file
  3. Use HttpRuntime element in Web.config
Using IIS
Simple solution is to stop website from IIS management console. More visitor friendly is to redirect all requests to info page where you can explain why website is down and when will be active again. On IIS 6,
Description: Http redirection in IIS 6
On IIS7, Http redirection is not available by default. First, go to Control Panel -> Turn Windows Features On or Off -> Internet Information Services -> World Wide Web Services -> Common HTTP Features -> and select Http Redirection like in next image:
Description: Enable HTTP redirect on Windows 7
Now, when you start IIS and select website, you will see new "HTTP Redirect" icon in Features View. Use is almost the same like on IIS 6, you need to specify exact destination for all requests. In addition to that, IIS 7 gives an option to set status code that will be returned to browser.
Accessing to Control Panel and IIS is impossible if you are on shared hosting where developer usually has very limited rights. Fortunately, ASP.NET offers similar option using App_Offline.htm file.
Going offline with App_Offline.htm file
Very simple way to make your web application offline is by using App_Offline.htm file. Create new HTML page and name it "App_Offline.htm". Copy the page to root folder of website. Now, if you try to visit website in browser, server will return only App_Offline.htm. Web server will shut down the application and unload it
On this page, you can place some information to visitors about why website is down, and when they could expect to be online again. You can put practically anything in this file, but be careful about its size. If App_Offline.htm is smaller than 512 bytes Internet Explorer (occurst on IE 6) will show its own HTTP error message. To avoid this, be sure that App_Offline.htm has more than 512 bytes, even if you place some invisible HTML comments.
When website upgrading is finished, delete or rename App_Offline.htm from root folder. Web application will go online again after next request.
Keep in mind that solution with App_Offline.htm page works only for ASP.NET pages. Many web hosting providers today offer several technologies in same hosting package, so you can use ASP.NET, PHP, classic ASP etc., all mixed on single website. Notice that any PHP, ASP or static HTML pages will still work normally after App_Offline.htm is uploaded.
Using httpRuntime element in web.config
To make web application offline, you can use Web Site Administration Tool. If you can't use WSAT, edit web.config manually. Add inside tag, like this:
<configuration>    
<system.web>
    <httpRuntime enable="false"/>
...
Application will not be loaded to memory. Any request to ASP.NET page will return error 404 "The resource cannot be found". This error is not friendly as App_Offline.htm file where you can inform visitors what is happening.
<httpRuntime enable="false"/> affects only ASP.NET pages, so content, like images, static HTML pages, classic ASP pages etc., will still be available.

воскресенье, 30 января 2011 г.

Using Computed Columns in SQL Server with Persisted Values

In my databases I had some values that were often calculated while generating several reports. Also there were some columns that were dependent on one or more other columns. As one column was updated triggers were being used to synchronize new values in dependent columns. I was required to provide a more efficient and standard approach to handle these types of scenarios. So how could I get rid of the overhead for calculations at report generation time and how could I avoid the use of triggers for synchronizing updated columns?
SolutionFor such scenarios where calculated values are required or values are generated through manipulation on other columns, we have a powerful feature provided in SQL Server. This feature is "Computed Columns".
A computed column is computed from an expression that can use another column or columns in the same table. Functions, variables, constants, non computed column names or any combination of all these may be used along with operators to create a computed column. In this tip we will go through an example of implementing a computed column.
I have tested the scripts on SQL Server 2005 and SQL Server 2008. In the following script we will create a table called CCtest in the AdventureWorks database with three columns [empNumb], [DOBirth] , [DORetirement].
We are required to have the "Date of Retirement" for each employee as (DOBirth + 60 years - 1 day). Instead of calculating it each time in the report or updating the column [DORetirement] each time through a trigger when [DOBirth] is updated, we have a better approach here to create [DORetirement] as a computed column.  Since this rule could change at anytime we are implementing it as a computed column instead of a hard coded value.
Script # 1: Create a table with computed column
USE [AdventureWorks]
GO 
-- Create Table with computed column
CREATE TABLE [dbo].[CCtest]
(
[empNumb] [int] NULL,
[DOBirth] [datetime] NULL,
[DORetirement] AS (dateadd(year,(60),[DOBirth])-(1)) PERSISTED
)
GO
The same may be done through SSMS. Create new table by right clicking on the Tables folder in the AdventureWorks database.
You will be provided a design view for the new table in SSMS. Provide specifications for the calculated column as shown below.
Now we have our table CCtest with a computed column. Similarly we can add a computed column to any existing table using the "ALTER TABLE" command or opening the table in design view using SSMS and making the changes.
Let's insert some data and run a query to test the functionality of the computed column.
Script # 2: Insert data in table
USE AdventureWorks
GO 
INSERT INTO CCTest (empNumb, DOBirth)
SELECT 30 ,'1985-12-13' UNION ALL
SELECT 25 ,'1980-11-18' UNION ALL
SELECT 21 ,'1978-01-19' UNION ALL
SELECT 7 ,'1985-12-13' UNION ALL
SELECT 5 ,'1975-07-23' 
GO
SELECT * FROM dbo.CCTest
GO
Here we can see our computed column:
To verify that the computed column will be updated for any updates, we will update [DOBirth] for [empNumb] 25.
Script # 3: Update DOBirth of empNumb 25
USE AdventureWorks
GO 
UPDATE CCtest
SET DOBirth = '1960-03-25'
WHERE empnumb = 25
GO
SELECT * FROM dbo.CCTest
WHERE Empnumb = 25
GO
Here we can see our computed column has been updated.
PersistedYou may have noticed that we also used the property "Persisted" for our computed column. This property for computed columns has been introduced in SQL Server 2005 and onwards. It is important for any computed column, because many additional features depend on it. To be able to make a computed column as Persisted it has to be deterministic.
Here are a few rules:
  • If Persisted property is off then calculated column will be just a virtual column. No data for this column will be stored on disk and values will be calculated every time when referenced in a script. If this property is set active then data of computed column will be stored on disk.
  • Any update in referenced column will be synchronized automatically in computed column if it is Persisted.
  • Along with some other conditions Persisted is required to create an index on the computed column.
Nullability
Nullibility for a computed column value will be determined by the database engine itself. The result of a non-nullable referenced column may be NULL in certain conditions to avoid possible overflows or underflows. You can provide an alternate value for NULL using the ISNULL(check_expression, constant), if required.
Some Limitations
  • For SQL Server 2000 you can not create a persisted computed column.
  • You can not reference columns from other tables for a computed column expression directly.
  • You can not apply insert or update statements on computed columns.
  • If you are combining operators of two different data types in your expression then operator of lower precedence will be converted to that of higher precedence. If implicit conversion is not possible then error will be generated.
  • A subquery can not be used as an expression for creating a computed column.
  • Computed columns can be used in SELECT lists, WHERE or ORDER BY clauses and as regular expressions , but to use a computed column as CHECK, FOREIGN KEY or NOT NULL constraints you have to set it to Persisted.
  • To use a computed column as Primary or Unique Key constraint it should be defined by a deterministic expression and data type of computed column expression should be indexable.
Next Steps
  • While planning for any computed column, please keep in mind that although a Persisted computed column will reduce overhead for calculations at run time it will consume more space on disk.
  • To get Nullability of any computed column in a table, use the COLUMNPROPERTY function with the AllowsNull property.
  • Creating indexes on computed columns requires certain conditions to be fulfilled. For details of these conditions please visit BOL

How to Use Computed Column SQL Server Databases


What is Computed Column ?


If you have columns in your sql database tables such that their values depend on other columns, you may use computed columns.
Using computed columns or calculated columns will enable you save the calculation logic of such computed values in database.
And will save you from extra coding everytime you have to do when you require the computed item.
A computed column can be defined as an expression which can use other columns in the same table as well as using other table values via user-defined functions.
The expression of the computation can be directly a name of a noncomputed column (*), constants, functions including sql or user-defined functions or can contain all in the computed column definition.
It is important that the computed column expression can not be a subquery which means you can not use SELECT statements in the expression. Even if you are selecting a constant value like "SELECT 1" .
(*) As a note :
A computed column in a table is not allowed to be used in another computed-column definition in the same table.
You are reference a computed column in another table by using a user defined function in the expression of the calculated column.
Computed columns are actually not physically created on the related database table unless they are defined as "Persisted" computed columns.
You can think of computed columns as virtual columns. They are not physically stored in the related sql database table.
They are calculated every time they are referenced in a sql statement.
If you want to create an index on the computed column, then If you require the computed column to be stored in the database instead of calculating everytime, you should define the computed column as persisted using the "PERSISTED" keyword in CREATE TABLE ... ADD column or ALTER TABLE ... ALTER COLUMN statements.
Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic.
Computed columns just like an ordinary table column can be used in a select list, can take place in WHERE clauses and in ORDER BY clauses.
But computed columns cannot be the directly updated or values can not be inserted into computed columns using INSERT or UPDATE statements.

Subqueries are not allowed in this context. Only scalar expressions are allowed.


A computed column is computed from an expression. But the expression cannot be a subquery.
If you need to execute SELECT statement to gather required data for the computed column value expression, then a user-defined function can be used.
You can build a user-defined function which can run SELECT statements.
But if you decide to create or alter your calculated column to be persisted, you may experience some other problems relating with determinism especially for XML data types and XQuery.
You may solve the problem for creating a persisted computed column by defining the user defined function (udf) which is used in the expression by using the "WITH SCHEMABINDING" with user sql function.

Cannot Create Persisted Computed Columns using User-Defined Functions with Select From Other Tables


You can not create calculated columns / computed columns as persisted if there is a user-defined function in the computed column expression and this function runs SELECT queries from database tables.
This is because if you are using a user defined sql function in the calculated column expression, to make the computed column persisted, udf must be deterministic.
For determinism for such a sql function, you must define the user defined t-sql function with "WITH SCHEMABINDING" .
In this case, schema binding will require the table names in two-part format.
And when you alter the sql function with tables names in two part format including the schema name and "WITH SCHEMABINDING" hint, this time the function will not be able for use in an expression of a persisted column.
Because then the column will be defined with user data access which is not suitable for a persisted computed column.

T-SQL Computed Column Sample Codes for SQL Server Tables


Here you can find samples where computed column properties are being illustrated with sample t-sql code.
CREATE TABLE Authors
(
 AuthorId int IDENTITY(1,1) NOT NULL,
 FirstName nvarchar(100),
 LastName nvarchar(100),
 FullName AS (FirstName + SPACE(1) + LastName) -- computed column
)
The above table sample has the FullName computed column defined as the concatenation of two other column values in the same table.
This is a simple expression sample. We define the computed column by "AS" clause and see that we do not define a column type since the type is defined implicitly by the calculation expression.
INSERT INTO Authors (FirstName, LastName) VALUES (N'Roger', N'Wolter')
INSERT INTO Authors (FirstName, LastName) VALUES (N'Dejan', N'Sarka')
SELECT * FROM Authors
SQL Server computed column sample
Let's add an other computed column where a CASE expression is used in the definition of the calculated column value.
ALTER TABLE Authors ADD FullName2 AS (CASE WHEN AuthorId < 3 THEN LastName + SPACE(1) + FirstName ELSE FirstName + SPACE(1) + LastName END)
Now insert some data into the sql table, and see the results.
INSERT INTO Authors (FirstName, LastName) VALUES (N'Itzik', N'Ben-Gan')
SELECT * FROM Authors
SQL Server computed column sample
Let's now create a subsidiary SQL Server database table which we will use in our following samples.
Create Table DBParameters (
 ParameterId int Identity(1,1) NOT NULL,
 ParameterDescription nvarchar(1000),
 ParameterValue nvarchar(1000)
)
GO
insert into DBParameters select N'First Name is before Last Name', N'1'
And now we will try to use a sub-select query in the computed column expression.
ALTER TABLE Authors ADD FullName3 AS (CASE WHEN EXISTS(SELECT * FROM DBParameters) THEN LastName + SPACE(1) + FirstName ELSE FirstName + SPACE(1) + LastName END)
And the error that the SQL Engine will throw is:
Msg 1046, Level 15, State 1, Line 1
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Since subselects are not allowed, I will create a user-defined function to implement the same control.
CREATE FUNCTION GetBooleanParameterValue(
 @ParameterId as int
) RETURNS bit
AS
BEGIN
 DECLARE @result bit
 SELECT @result = CAST(ParameterValue AS bit) FROM DBParameters WHERE ParameterId = @ParameterId
 RETURN @result
END
GO
SELECT dbo.GetBooleanParameterValue(1)
Now, I'll try using the user-defined function instead of the sub-select query.
ALTER TABLE Authors ADD FullName3 AS (CASE WHEN dbo.GetBooleanParameterValue(1) = 1 THEN (FirstName + SPACE(1) + LastName) ELSE (LastName + SPACE(1) + FirstName) END)
UPDATE DBParameters SET ParameterValue = 1 WHERE ParameterId = 1
SELECT FirstName, LastName, FullName3 FROM Authors

UPDATE DBParameters SET ParameterValue = 0 WHERE ParameterId = 1
SELECT FirstName, LastName, FullName3 from Authors
SQL Server computed column sample
The first select displays the firstname before the lastname in the fullname.
And after the parameter table is updated, the second select displays the lastnames before the last.
Let's create an other table which will help us in the next computed columns.
CREATE TABLE [Author Expertise]
(
 Id int Identity(1,1) NOT NULL,
 AuthorId int,
 Expertise nvarchar(1000) NOT NULL
)
GO

INSERT INTO [Author Expertise] SELECT 1, N'T-SQL'
INSERT INTO [Author Expertise] SELECT 1, N'CLR'
INSERT INTO [Author Expertise] SELECT 2, N'CLR'
INSERT INTO [Author Expertise] SELECT 2, N'XML'
INSERT INTO [Author Expertise] SELECT 3, N'T-SQL'
INSERT INTO [Author Expertise] SELECT 3, N'CLR'
INSERT INTO [Author Expertise] SELECT 3, N'Service Broker'
GO

CREATE FUNCTION GetAuthorExpertiseCount(
 @AuthorId as int
) RETURNS int
AS
BEGIN
 DECLARE @result int
 SELECT @result = COUNT(*) FROM [Author Expertise] WHERE AuthorId = @AuthorId
 RETURN @result
END
GO

SELECT dbo.GetAuthorExpertiseCount(3)

ALTER TABLE Authors ADD Expertise_Topics_Count AS ( dbo.GetAuthorExpertiseCount(AuthorId) )
SELECT * FROM Authors
SQL Server computed column sample
ALTER TABLE Authors ADD BooksInfo XML
GO
UPDATE Authors
SET BooksInfo = N'

 
  Microsoft SQL Server 2005
 

 
  Microsoft SQL Server 2008
 
'
WHERE AuthorId = 3
GO
CREATE FUNCTION GetLastBookTitle (
 @BooksInfo XML
) RETURNS nvarchar(1000)
AS
BEGIN
 DECLARE @Title nvarchar(1000)

 select
  @Title = i.value('@title', 'nvarchar(1000)')
 from @BooksInfo.nodes('/books/book') n(i)
 order by CONVERT(datetime, i.value('./publishdate[1]/.', 'nvarchar(1000)'), 103) desc

 RETURN @Title
END
GO
ALTER TABLE Authors ADD LastBookTitle AS dbo.GetLastBookTitle(BooksInfo)
GO
SELECT AuthorId, Fullname3, BooksInfo, LastBookTitle FROM Authors
SQL Server computed column sample
ALTER TABLE Authors ADD BirthDate datetime
GO
UPDATE Authors SET BirthDate = '19700101' WHERE AuthorId = 1
GO
ALTER TABLE Authors ADD Age AS DATEDIFF(yy, BirthDate, GETDATE())
Let's add an other age table which is depending on another calculated column.
ALTER TABLE Authors ADD Age2 AS DATEADD(yy, Age, BirthDate)
Unfortunately, a computed column can not be based upon another calculated field.
The SQL Server error message will be:
Msg 1759, Level 16, State 0, Line 1
Computed column 'Age' in table 'Authors' is not allowed to be used in another computed-column definition.
Also a user- defined function which is using the computed table column in its t-sql code cannot be used in another computed column expression.
CREATE FUNCTION TestUDF(
 @Age int,
 @BirthDate datetime
) RETURNS datetime
AS
BEGIN
 RETURN DATEADD(yy, @Age, @BirthDate)
END
GO
ALTER TABLE Authors ADD Age2 AS dbo.TestUDF(Age, BirthDate)
The SQL Engine will throw the following error message in this ALTER TABLE command.
Msg 1759, Level 16, State 0, Line 1
Computed column 'Age' in table 'Authors' is not allowed to be used in another computed-column definition.
What about if we want to define the computed column as PERSISTED column in order to create indexes on these columns.
ALTER TABLE Authors ADD LastBookTitlePersisted AS dbo.GetLastBookTitle(BooksInfo) Persisted
Msg 4936, Level 16, State 1, Line 1
Computed column 'LastBookTitlePersisted' in table 'Authors' cannot be persisted because the column is non-deterministic.
The reason while we can not define the LastBookTitlePersisted column as PERSISTED is the computed column is non-deterministic because of the user-defined function dbo.GetLastBookTitle() is an XML based function.
But the solution for this problem is simple for this udf.
If we alter the procedure by adding the "WITH SCHEMABINDING" hint, we can manage creating the related column persisted.
CREATE FUNCTION GetLastBookTitle2 (
 @BooksInfo XML
) RETURNS nvarchar(1000)
WITH SCHEMABINDING -- The "WITH SCHEMABINDING" is needed if I want to the column a persisted computed column
AS
BEGIN

 DECLARE @Title nvarchar(1000)

 select
  @Title = i.value('@title', 'nvarchar(1000)')
 from @BooksInfo.nodes('/books/book') n(i)
 order by CONVERT(datetime, i.value('./publishdate[1]/.', 'nvarchar(1000)'), 103) desc

 RETURN @Title
END
Now we can successfully create the column as persisted.
ALTER TABLE Authors ADD LastBookTitlePersisted AS dbo.GetLastBookTitle2(BooksInfo) Persisted

среда, 26 января 2011 г.

CROSS APPLY


В SQL Server 2005 добавили оператор CROSS APPLY, с помощью которого можно соединять таблицу с возвращающей табличное значение функцией (table valued function - TVF), причём TVF будет иметь параметр, который будет изменяться для каждой строки. Например, представленный ниже запрос возвратит тот же результат, что и показанное ранее внутреннее соединение, но с использованием TVF и CROSS APPLY:

create function dbo.fn_Sales(@Cust_Id int)
returns @Sales table (Item varchar(10))
as
begin
insert @Sales select Item from Sales where Cust_Id = @Cust_Id
return
end

select *
from Customers cross apply dbo.fn_Sales(Cust_Id)

Cust_Id Cust_Name Item
----------- ---------- ----------
2 John Doe Camera
3 Jane Doe Computer
3 Jane Doe Monitor


Также можно использовать внешнее обращение - OUTER APPLY, позволяющее нам найти всех клиентов независимо от того, купили ли они что-нибудь или нет. Это будет похоже на внешнее соединение.

select *
from Customers outer apply dbo.fn_Sales(Cust_Id)

Cust_Id Cust_Name Item
----------- ---------- ----------
1 Craig NULL
2 John Doe Camera
3 Jane Doe Computer
3 Jane Doe Monitor


понедельник, 24 января 2011 г.

How To Change Drive Letters in Windows 7


Here's How:
  1. Click on the Start button and then chooseControl Panel.
  2. Click on the System and Security link.
    Note: If you're viewing the Large icons or Small icons view of Control Panel, you won't see this link so just click on the Administrative Tools icon and skip to Step 4.
  3. In the System and Security window, click on theAdministrative Tools heading located near the bottom of the window.
  4. In the Administrative Tools window, double-click on the Computer Management icon.
  5. When Computer Management opens, click on Disk Management on the left side of the window, located under Storage.
    After a brief loading period, Disk Management should now appear on the right side of the Computer Management window.
    Note: If you don't see Disk Management listed, you may need to click on the |> icon to the left of the Storage icon.
  6. You can now partition a hard driveformat a hard drivechange a drive's letter, or do whatever else you need to do in Windows 7's Disk Management tool.
Tips:
  1. See How To Open Disk Management From the Command Prompt for instructions on starting Disk Management via a command.
  2. Not a Windows 7 user? See How Do I Open Disk Management in Windows? for specific instructions for your version of Windows.

воскресенье, 23 января 2011 г.

Hierarchy ID: Model Your Data Hierarchies With SQL Server 2008


Code download available at: SQLHierarchyID2008_09a.exe (155 KB)
Browse the Code Online

This article is based on a prerelease version of SQL Server 2008 RC0. All information herein is subject to change.
This article discusses:
  • Modeling hierarchical data
  • Creating a bill of materials system
  • Moving to HierarchyID
  • Testing the system
This article uses the following technologies: 
SQL Server 2008

SQL SERVER – Difference Between Unique Index vs Unique Constraint


Unique Index and Unique Constraint are the same. They achieve same goal. SQL Performance is same for both.
Add Unique Constraint
ALTER TABLE dbo.<tablenameADD CONSTRAINT<namingconventionconstraintUNIQUE NONCLUSTERED(
<
columnname>
ON [PRIMARY]
Add Unique Index
CREATE UNIQUE NONCLUSTERED INDEX<namingconventionconstraintON dbo.<tablename>
(
<
columnname>
ON [PRIMARY]
There is no difference between Unique Index and Unique Constraint. Even though syntax are different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index or Primary Key Index are physical structure that maintain uniqueness over some combination of columns across all rows of a table. It is a convenient way to enforce a Unique Constraint for SQL Server.