Dotnet Notes

Launch disk cleanup tool from command line
c:> cleanmgr
=============
Below diagram shows the lifecycle during the initial load of a page, and how ViewState is involved during a PostBack.

Initial Load: InitàLoadàPreRenderàSaveViewStateàRenderàDispose

In Init, controls are created and added to the control tree, in PreRender “CreateChildControls” is executed and controls are prepared for render, In SaveViewState controls save current states, in Render controls render themselves and finally in Dispose controls and Page are disposed.

Controls on a page take the opportunity to save their data to the ViewState state bag just before they render. Another diagram shown below shows how pages and controls use the ViewState data carried along and returned during a PostBack to synthesize events for the page developer’s use.

PostBack: InitàLoadViewStateàLoadàPostBack dataàPostBack eventsàPreRenderàSaveViewStateàRenderàDispose
============

Ramanujam number 1729 (1 cube+ 12 cube, 9 cube +10 cube, 7x13x19 all odds) .
====================

In ASP.Net  TextBox Control

When your TextMode is set to “MultiLine”, the MaxLenght property has no affect. You’ll have
to use a JavaScript function or RegularExpressionValidator instead.
<asp:RegularExpressionValidator ID=”rgConclusionValidator2″
                                ControlToValidate=”txtSpecialInstruction” ErrorMessage=”Special instruction can’t exceed 200 characters”
                                ValidationExpression=”^[\s\S]{0,200}$” runat=”server” Display=”None”  SetFocusOnError=”true” />

————————–

SELECT TOP 97 PERCENT DT.*
FROM
(    SELECT TOP 97 PERCENT *
    FROM Products
    ORDER BY UnitPrice ASC
) AS DT
ORDER BY UnitPrice DESC

Out of 80 records if you want top 20 records then u can use query like
select top 25 percent * from table
—————-
 
order by and union problem

Posted – 07/01/2008 :  09:42:35    
——————————————————————————–
 

I HAVE CREATED A QUERY LIKE BELOW AND FOUND ERROR
Incorrect syntax near the keyword ‘union’.

SELECT TOP 10 PERCENT ID FROM wsitimesheetdetails
ORDER BY ID ASC
UNION
SELECT TOP 30 PERCENT ID FROM wsitimesheetdetails  ORDER BY ID ASC

error is syntax error at union

 
Then Solution is :

select * from(

SELECT TOP 10 PERCENT ID FROM wsitimesheetdetails
ORDER BY ID ASC) a

UNION
select * from(
SELECT TOP 30 PERCENT ID FROM wsitimesheetdetails  ORDER BY ID ASC)b

=============================================================

Problem
I’m designing a table and I’ve decided to create an auto-generated primary key value as opposed to creating my own scheme or using natural keys. I see that SQL Server offers globally unique identifiers (GUIDs) as well as identities to create these values. What are the pros and cons of these approaches?

Solution
Yes, there are a number of ways you can auto-generate key values for your tables. The most common ways are via the use of the IDENTITY column property or by specifying a uniqueidentifier (GUID) data type along with defaulting with either the NEWID() or NEWSEQUENTIALID() function. Futhermore, GUIDs are heavily used in SQL Server Replication to uniquely identify rows in Merge Replication or Transactional Replication with updating subscriptions.

The most common, well known way to auto-generate a key value is via the use of the IDENTITY column property on a column that’s typically declared as an integer. Once defined, the engine will automatically generate a sequential number based on the way the property has been declared on the column. The IDENTITY property takes an initial seed value as its first parameter and an increment vaAnother way to auto-generate key values is to specify your column as a type of uniqueidentifier and DEFAULT using NEWID() or NEWSEQUENTIALID(). Unlike IDENTITY, a DEFAULT constraint must be used to assign a GUID value to the column.

How do NEWID() and NEWSEQUENTIALID() differ? NEWID() randomly generates a guaranteed unique value based on the identification number of the server’s network card plus a unique number from the CPU clock. In contrast, NEWSEQUENTIALID() generates these values in sequential order as opposed to randomly.

Let’s create new tables that use a uniqueidentifier along with both NEWID() and NEWSEQUENTIALID()
lue as its second
As you can see, the first table which uses NEWID() generates random values while the second table that uses NEWSEQUENTIALID() generates sequential values. As opposed to the integers generated by the IDENTITY approach, the GUID values generated are not as friendly to look at or work with. There is one other item to note. SQL Server keeps the last generated identity value in memory which can be retrieved right after an INSERT using SCOPE_IDENTITY(), @@IDENTITY, or CHECK_IDENT (depending on the scope you require). There is nothing similar to capture the last generated GUID value. If you use a GUID, you’ll have to create your own mechanism to capture the last inserted value (i.e. retrieve the GUID prior to insertion or use the SQL Server 2005 OUTPUT clause).

Now that we understand how to auto generate key values and what they look like, let’s examine the storage impacts of each approach. As part of the previously created table definitions, I added a column of CHAR(2000) to mimic the storage of additional column data. Let’s examine the physical storage of the data:

Looking at this output, you can see that the NEWID() test table is very fragmented as evidenced by its fragmentation percentage of 98%. Furthermore, you can see that the rows were dispersed among 490 pages. This is due to the page splitting that occurred due to the random nature of the key generation. In contrast, the IDENTITY and NEWSEQUENTIALID() test tables show minimal fragmentation since their auto generated keys occur in sequential order. As a result, they don’t suffer from the page splitting condition that plagues the NEWID() approach. Though you can defragment the NEWID() table, the random nature of the key generation will still cause page splitting and fragmentation with all future table INSERTs. However, page splitting can be minimized by specifying an appropriate FILL FACTOR.

Looking at the NEWSEQUENTIALID() test table, we see it generated fewer pages than the NEWID() approach but it still generated more pages than the IDENTITY approach. Why is this? It’s because the uniqueidentifier data type consumes 16 bytes of disk space as opposed to the 4 bytes used by the integer data type that was used for the IDENTITY. Considering that SQL Server pages are generally capped at 8K or roughly 8060 bytes (as of SQL Server 2005, there is a row-overflow mechanism that can kick in but that’s for another discussion), this leads to more pages generated for the NEWSEQUENTIALID() approach as opposed to the IDENTITY approach.

Now also consider this, since a uniqueidentifier data type consumes 16 bytes of data, the size of any defined non-clustered indexes on a table using a GUID as a clustered index are also affected because the leaf level of these non-clustered indexes contains the clustered index key as a pointer. As a result, the size of any non-clustered indexes would end up being larger than if an IDENTITY were defined as integer or bigint.

It’s evident that using IDENTITY to auto-generate key values offers a few advantages over the GUID approaches:

IDENTITY generated values are configurable, easy to read, and easier to work with
Fewer database pages are required to satisfy query requests
In comparison to NEWID(), page splitting (and its associated overhead) is eliminated
Database size is minimized
System functions exist to capture the last generated IDENTITY value (i.e. SCOPE_IDENTITY(), etc)
Some system functions – such as MIN() and MAX(), for instance – cannot be used on uniqueidentifier columns

Next Steps

Read more about NEWSEQUENTIALID() in the SQL Server 2005 Books Online
Read Using uniqueidentifier Data in the SQL Server 2005 Books Online
If you’re not in a situation where you require a globally unique value, consider if an IDENTITY makes sense for auto-generating your key values.
Regardless if you decide on a GUID or IDENTITY, consider adding a meaningful UNIQUE key based on the real data in your table.
REfer : http://www.mssqltips.com/tip.asp?tip=1600

——————————————–=========================================
http://blog.sqlauthority.com/2007/06/22/sql-server-explanation-and-comparison-of-nullif-and-isnull/
Explanation of NULLIF
Syntax:
NULLIF ( expression , expression )

Returns a null value if the two specified expressions are equal. NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.

Following is good example of NULLIF and CASE from BOL:
USE AdventureWorks;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag)AS ‘Null if Equal’
FROM Production.Product
WHERE ProductID < 10;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,’Null if Equal’ =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM Production.Product
WHERE ProductID < 10;
GO

Explanation of ISNULL
Syntax:
ISNULL ( check_expression , replacement_value )

Replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

Following is good example of ISNULL from BOL:
USE AdventureWorks;
GO
SELECT AVG(ISNULL(Weight, 50))
FROM Production.Product;
GO

Observation:
Interesting observation is NULLIF returns null if it comparison is successful, where as ISNULL returns not null if its comparison is successful. In one way they are opposite to each other.
———————————-

Refer : http://www.eggheadcafe.com/software/aspnet/32410183/checking-empty-value.aspx

In a stored procedure there is an uniqueidentifier input parameter and it is
used for an insert statement.

Some times that param comes from front-end application with all zero value
(because of Guid.Empty from C#).
And when it comes like that ,  (when the value is
‘00000000-0000-0000-0000-000000000000’ ) i want to insert null value.

How can i check if this input parameter are empty ?

Solution:
———
CREATE PROCEDURE InsertFoo
@guid UNIQUEIDENTIFIER
AS
INSERT INTO Foo (guid_col)
SELECT NULLIF(@guid, ‘00000000-0000-0000-0000-000000000000’);
============================================================================
Getting date only
SELECT dateonly = CONVERT(CHAR(8),dt,112) FROM #foo

Getting time only
SELECT timeonly = CONVERT(CHAR(8),dt,8) FROM #foo

—————
USe of Case statement

1.
http://www.craigsmullins.com/ssu_0899.htm

SELECT title, price,
        Budget = CASE price
         WHEN price > 20.00 THEN ‘Expensive’
          WHEN price BETWEEN 10.00 AND 19.99 THEN ‘Moderate’
          WHEN price < 10.00 THEN ‘Inexpensive’
          ELSE ‘Unknown’
        END,
FROM titles

2.
http://www.4guysfromrolla.com/webtech/102704-1.shtml
SELECT
  SUM(CASE
         WHEN DateDiff(d, InvoiceDate, getdate()) BETWEEN 0 AND 29 THEN InvoiceAmount
      END) as Current,
  SUM(CASE
         WHEN DateDiff(d, InvoiceDate, getdate()) BETWEEN 30 AND 59 THEN InvoiceAmount
      END) as ThirtyToSixty,
  SUM(CASE
         WHEN DateDiff(d, InvoiceDate, getdate()) BETWEEN 60 AND 89 THEN InvoiceAmount
      END) as SixtyToNinty,
  SUM(CASE
         WHEN DateDiff(d, InvoiceDate, getdate()) >= 90 THEN InvoiceAmount
      END) as NintyPlus
FROM Invoices     
=====================================

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: