BYPsoft on .NET & SQL

How SQL Server deletes records - DELETE internals

noreply@blogger.com (DLM@BYPsoft) — Fri, 27 Apr 2012 19:20:00 +0000

I was surprised with the fact that a lot of developers don't know that SQL Server does not perform immediate delete of their DELETE command, but it is doing an a background thread called GHOST CLEANUP periodically. When row delete is requested by transaction, that row is being just marked as "ghosted" and left to the background process to actually removes data. Thus, performances are enhanced and in case of ROLLBACK less work is done. In a system table sys.dm_exec_requests you can periodically notice appearance of the GHOST CLEANUP as command.
So, let's see what SLQ Server actually does when we are executing

DELETE FROM MyTable WHERE ...

At the beginning let's create test table and insert couple of records.

CREATE TABLE [dbo].[DeleteInternals](
[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [char](4) NOT NULL
)
GO

insert into DeleteInternals([Name]) values('Row1')
insert into DeleteInternals([Name]) values('Row2')
insert into DeleteInternals([Name]) values('Row3')
insert into DeleteInternals([Name]) values('Row4')

To discover internals of the table structure, we will use undocumented commands DBCC IND and DBCC PAGE which are heavily used internally in SQL Server but they are not documented and not supported which means you are using them at your own risk.

To find out which page IDs to look at with DBCC PAGE we are executing

DBCC IND(TestDatabase, DeleteInternals, 1)

Some important fileds for us in the output are

PageFID - the file ID of the page
PagePID - the page number in the file
OBjectID - the ID of the object which page is part of
IndexID - the ID of the index which page is part of
PageType - where 1 = Data Page, 2 = Index Page
IndexLevel - as SQL Server uses doubly-linked lists in a B-Tree for indexes, the data are actually leaf level (level 0) of the clustered index.

In order to get output back to the console from DBCC commands we need to turn on T3604

DBCC TRACEON(3604)
GO
DBCC PAGE(TestDatabase, 1,399,1)

Here is output from this command:

Important stuff for our example here are following records marked in a red square):

m_slotCnt = 4 - which shows number of records on the page
m_GhostRecCnt = 0 - count of "ghosted" records
RecordType = PRIMARY_RECORD for a Slot1 where "Row2" data is located which we will delete later on
Row - Offset table - offsets for all records.

After executing DELETE command we will take a look at a page structure again. But, to prevent GHOST CLEANUP process to execute and cleans our page we will enclose DELETE command in a transaction which we will not commit for some time:

begin transaction
delete from DeleteInternals where Id = 2

Let's take a look at page content:

DBCC PAGE(TestDatabase, 1,399,1)

We still have 4 records (m_slotCnt) but we can notice now that we have one "ghosted" record, m_ghostRecCnt = 1. That record is exactly our "Row2" which is marked now as GHOST_DATA_RECORD (RecordType). Row - Offset table remains unchanged.

After committing transaction by executing

commit

and inspecting again page structure

DBCC PAGE(TestDatabase, 1,399,1)

We see now, after committing transaction that number of records is equal 3 (m_slotCnt = 3) and number of "ghosted" records is equal 0 (m_ghostRecCnt = 0)
Also, Row - Offset table shows all three records, but you can notice that their offset remains as previous, before delete commnad is executed, what means that SQL Server didn't compacted page. About that, we speak some when next time.

Disk I/O on SQL Server

noreply@blogger.com (DLM@BYPsoft) — Fri, 20 Apr 2012 09:46:00 +0000

To get a pending request longer than 10 milliseconds on your SQL Server use

SELECT * FROM [sys].[dm_io_pending_io_requests] WHERE [io_type] = 'disk' AND [io_pending_ms_ticks] > 10

How to hide form on startup in .NET

noreply@blogger.com (DLM@BYPsoft) — Sun, 02 May 2010 19:28:00 +0000

If you ever wanted to hide a form on startup in a .NET you figured out that the task is not so obvious. If you just set Visible property to False you will see that it will be ignored.

The solution is to run the form as Minimized, by setting the form property WindowState to minimized and in Form_Load function to set this.Visible = false;

Please note that line

this.WindowState = System.Windows.Forms.FormWindowState.Minimized;

must be in InitializeComponent() function generated by designer. Either you add this line manually there or set the WindowState property in the Form designer. At the very end functions which hides your form on startup looks like

private void InitializeComponent()

{

this.SuspendLayout();

// Form1

this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);

this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;

this.ClientSize = new System.Drawing.Size(292, 266);

this.ControlBox = false;

this.Name = "Form1";

this.Text = "Form1";

this.WindowState = System.Windows.Forms.FormWindowState.Minimized;

this.Load += new System.EventHandler(this.Form1_Load);

this.ResumeLayout(false);

}

private void Form1_Load(object sender, EventArgs e)

{
this.Visible = false;

}

Database Full Text Search Tool

noreply@blogger.com (DLM@BYPsoft) — Sun, 28 Mar 2010 18:37:00 +0000

If you need to search for a particular word(s) or number(s) in a whole database you can use DBTYP.NET Database Full Text Search module which has been just added to the DBTYP.NET Studio.

It performs searches on all data types except binary, xml and date/time values. So you are free to search for whole words, part of words using SQL LIKE wild-cards or even for the numbers.

SQL Server Configuration Options

noreply@blogger.com (DLM@BYPsoft) — Fri, 26 Mar 2010 19:03:00 +0000

If you need to review configuration options of some SQL Server instance, you can get them out of the SYS.CONFIGURATIONS catalog view. So the select statement like

select * from sys.configurations

order by name

will give you back all configuration options of SQL Server instance.

Prevent Row Deletions/Updates on SQL Server

noreply@blogger.com (DLM@BYPsoft) — Thu, 25 Mar 2010 21:20:00 +0000

If you reach a requirements to prevent some table for deletions or/and updates you may wonder how to reach that. The simple answer is to use INSTEAD OF triggers. INSTEAD OF triggers override the standard action of triggering statement: INSERT, UPDATE or DELETE. On that way, INSTEAD OF trigger can ignore parts of the batch, not process part of the batch or taking an alternative action. The major difference to the AFTER triggers is that INSTEAD OF triggers can be defined on a views as well as on a tables.

So, a solution of the problem, preventing a row deletions on the table can be the following INSTEAD OF trigger:

CREATE TRIGGER trgPreventRowDeletion ON Test_table INSTEAD OF DELETE
AS
BEGIN
REISEERROR ('Deletions are not allowed from the Test_table', 16, 1)
END

On a similar way it can be defined INSTEAD OF INSERT and INSTEAD OF UPDATE triggers.

Identifying Index Fragmentation on SQL Server

noreply@blogger.com (DLM@BYPsoft) — Thu, 25 Mar 2010 21:16:00 +0000

The first thing you are doing when identifying poor query performances is to take a look at execution plan and indexes usage. Even that you see, everything is fine, your index can be fragmented so that SQL Server needs to make unnecessary data reads to execute query. At the end, your query is slow.
At the moment when index is created, little or no fragmentation is present. During the time, when updates, inserts and deletes occur indexes get fragmented what is a real bottleneck in a SQL Server performances.

There are two ways how to fix fragmented indexes: reorganizing or rebuilding them. Which operation is necessary depends on the level of fragmentation. Reorganization of index is suggested if fragmentation level is less than 30%. If it is more than 30% than rebuilding index is better choice.

Identifying fragmented indexes

Starting from version 2005, SQL Server contains a number of DMVs and DMFs qhich allow us to retrieve informations about SQL Server health and performances and identifying problems. One of them, allow us to take a look at the index fragmentation level - sys.dm_db_index_physical_stats DMF. It is important to said here that it places intent shared lock (IS) on the affected tables during execution.

sys.dm_db_index_physical_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | NULL | 0 | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
, { mode | NULL | DEFAULT }
)

The following query will get fragmentation of all indexes in the database

DECLARE @dbId int

SET @dbId = db_id('YOUR_DB_NAME')

SELECT s.[name] AS SchemaName, t.[name] AS TableName, i.[name] AS IndexName, p.[index_type_desc], p.[avg_fragmentation_in_percent]
FROM [sys].[dm_db_index_physical_stats](@dbId, NULL, NULL, NULL , 'DETAILED') p
INNER JOIN [sys].[tables] t ON p.[object_id] = t.[object_id]
INNER JOIN [sys].[schemas] s ON t.[schema_id] = s.[schema_id]
INNER JOIN [sys].[indexes] i ON p.[object_id] = i.[object_id] AND p.index_id = i.index_id
WHERE t.[is_ms_shipped] = 0

To get more proper candidates for rebuilding or reorganizing indexes it is necessary to consult other fields returned back from [sys].[dm_db_index_physical_stats] like avg_page_space_used_in_percent which indicates on average how full each page in index is. The higher number is better while but it is necessary here to balance fullness against the
number of inserts into index pages in order to keep the number of page splits to the absolute minimum. This exceeds the topic of this blog and requires adjustments of index fillfactor and monitoring of page splits.

ORA-01489: result of string concatenation is too long

noreply@blogger.com (DLM@BYPsoft) — Sun, 27 Sep 2009 19:30:00 +0000

One of our customers reported us this error during reading stored procedures from Oracle database. What have caused this error? USER_SOURCE view retrieve name, type and line by line of function/stored procedure definition. Each line in a new row. We used hierarchical queries and sys_connect_by_path function to concatenate all of those lines in one which keeps the full function/stored procedure definition.

The problem is, that SQL in Oracle is able to return just 4000 bytes in one column. So, if any string is longer than 4000 bytes in concatenation you will get this error. And there is no possibility to fix it. You must change logic, use chunks or something similar (CLOBs).

As a small notice, T-SQL variables can be used for concatenation very big strings which exceeds 4000 characters. Again, you will not be able to return it back with SQL.

As a sample, the following code produces the error:

select rpad(' ',4000)||'1' as 4001StringLength
from TestTable

but the following is fine:

declare
testVar   varchar2(32767);
begin
testVar := rpad(' ',4000)||'1'
end;

MySQL 5.4

noreply@blogger.com (DLM@BYPsoft) — Mon, 27 Apr 2009 19:58:00 +0000

New Features

MySQL has released a new version of database server – MySQL in version 5.4. Latest release was 5.1 with a really great features. This one brings not so much but also very important features:

InnoDB storage engine can address more than 4 CPU’s/cores. Thjs helps that MySQL scale much better under huge application workloads.
Till now, subqueries were well known as performance problematical. In version 5.4 subquery optimization has been improved a lot in a number of various use cases. As MySQL mentioned in the example below, execution time dropped from 12 minutes (9 000 000 reads) on MySQL 5.1 to just 1.8 seconds (153 000 reads) on MySQL 5.4

SELECT COUNT(l_orderkey) FROM lineitem
WHERE l_linenumber=1 AND
      l_orderkey IN
      (SELECT o_orderkey FROM orders
         WHERE o_totalprice > 1000 AND
               o_custkey IN
               (SELECT c_custkey FROM customer
                  WHERE c_address LIKE 'Le%'));

MySQL 5.4 offers a new much better join algorithm which speed up execution time of multi-way joins like in the following example

SELECT COUNT(*) FROM part, lineitem
       WHERE l_partkey=p_partkey AND p_retailprice>2050
             AND l_discount>0.04;

Improved error handling – through the implementation of standard SQL (SQL 2003) SIGNAL and RESIGNAL operations in stored procedures, functions and triggers developers are able to signal rule violations during execution.

Must mention here that Oracle and SQL Server not implement this functionality but have their own mechanisms: Oracle through RAISE and SQL Server through RAISEERROR statements.

INFORMATION_SCHEMA got a new objects PARAMETERS. We wait a lot on this.

In prior version of MySQL out parameter were not possible in prepared statements. That has been changed now.

This is all from programmers perspective. Have a fun.

Compare SQL Server, Oracle and MySQL database with DBTYP.NET Studio

Table 'mysql.proc' doesn't exist.

noreply@blogger.com (DLM@BYPsoft) — Thu, 19 Mar 2009 20:38:00 +0000

Sometimes our support team receive a helpdesk request concerning this error message shown in Schema Comparison of DBTYP.NET 2009 Studio.

The usual problem with such a customers is that almost all of them make recent upgrade of MySQL server. While some releases of MySQL introduce changes in the structure of the system tables in mysql database to add new privileges and support new features, it is necessary to update the system tables as well.

This can be achieved by running mysql_fix_privilege_tables script in MySQL prior to version 5.1.7 or mysql_upgrade in version 5.1.7 or above.

For more details check following links:

See all database differences and dependencies with DBTYP.NET Studio

DBTyP.NET 2008 Release 3 Available

noreply@blogger.com (DLM@BYPsoft) — Fri, 04 Jul 2008 20:12:00 +0000

BYPsoft announced the availability of DBTyP.NET 2008 Release 3, the newest version of the cross-database comparison tool that compares SQL Server, MySQL and Oracle databases (schema and data). DBTyP.NET 2008 Release 3 is available for download from http://www.bypsoft.com.

With its rich support for schema and data cross-database comparison, DBTyP.NET takes the mystery out of databases, making their comparison practical and easy for programmers and database administrators everywhere. Over and above its powerful comparison capabilities till now, BYPsoft added to the DBTyP.NET 2008 support for schema comparisons for Oracle databases. Full cross database comparison family includes now support for SQL Server, MySQL and Oracle databases in the latest release of DBTyP.NET 2008 for a fraction of the cost of single-database solution.

Since the DBTyP.NET is under constant development, user interface has been redesigned and rich visual interface and easy-to-use features of DBTyP.NET 2008 allow developers and DBAs to identify and deploy changes quickly on all supported databases and enhance productivity and maximize results. Features like colorfull differences, different objects icons, visual column description, in place colorfull script differences, multiple views and more all save time and ensure accuracy.

In addition to all the new features listed above, in the latest release of DBTyP.NET 2008 BYPsoft includes also the following:

SQL Server 2008 Ready
Support for SQL Server ROWGUIDCOL
Filtered out system objects on SQL Server databases.
Support SQLServer user defined types.
Support MySQL ENUM and SET data types.

We know that constant development is the key to progress, therefore we already started working on all new cool features. Stay with us.

List All Functions and Stored Procedures definitions in Oracle schema

noreply@blogger.com (DLM@BYPsoft) — Mon, 12 May 2008 12:30:00 +0000

A long time I have not published anything. BYPsoft preparing new version of DBTyP.NET with full support for a Oracle schema comparison and therefore didn't had any free time left for this blog.
A very interesting topic came to me out of this experience.
Have you ever tried to get a list of all stored procedures and functions in Oracle schema? Preaty easy,
SELECT * FROM USER_PROCEDURES;
You figured out that it is quite different comparing to SQL Server and MySQL where with the single query
SELECT * FROM INFORMATION_SCHEMA.ROUTINES;
you get a body definition as well.
To achieve this on Oracle is not so simply. First, to get user sources you should query USER_SOURCE view where each function or stored procedure line is stored in one row.
To get a full body, you have to use a powerful hierarchical query clause

SYS_CONNECT_BY_PATH. 
Here is a query which returns you list of all stored procedures and functions in Oracle schema together with their bodies defined in one column:

SELECT routine_name, y.TYPE, TRANSLATE(LTRIM(x.text1, '/'), '/', ' ') routine_definition  
FROM (SELECT name routine_name, LEVEL lvl,  sys_connect_by_path(text, '/') text1
                   FROM USER_SOURCE
                   CONNECT BY LINE - 1  = PRIOR LINE AND name = PRIOR name) x,
                   (SELECT name, TYPE, MAX(line) AS maxline
                    FROM USER_SOURCE
                   GROUP BY name, TYPE) y               
WHERE x.routine_name = y.name AND x.lvl = y.maxline
ORDER BY TYPE, routine_name

Compare database schema and data fast and easy with DBTYP.NET Studio

MySqlDateTime and .NET

noreply@blogger.com (DLM@BYPsoft) — Sat, 01 Mar 2008 23:29:00 +0000

In version 2008 our cross database comparison tool DBTyP.NET will support cross data comparison between SQL Server and MySQL DateTime values (date, datetime, smalldatetime). Actually, this is a common problem, between MySql.Data.Types.MySqlDateTime and System.DateTime types in .NET. It is very possible that if you tries to update MySqlDateTime column with DateTime variable you will get SystemArgumentException saying that could not store value ... into ... Expected type is MySqlDateTime. It is possible to format value as "yyyy-MM-dd HH:mm:ss" but in our case we don't want to iterate through whole DataSet and make this formatting.
Very elegant solution is to change a DataColumn type from MySqlDateTime to System.DateTime as

myMySqlDateTimeColum.DataType = typeof(System.DateTime);

After that you should not worry about formatting.

How to check if class has been used in Web or Windows application

noreply@blogger.com (DLM@BYPsoft) — Fri, 29 Feb 2008 23:26:00 +0000

After a while, I decided to participate in a newsgroups tonight and found an interesting question. "Is there a programmatic way for the class lib to determine what kind of app is running - web or windows". The worst, guy requested official response from Microsoft support and didn't get an answer. After 3 days his question wasn't answered, he re-posted it. So, is there are something wrong? Looks like not.
The simple answer from my side was, to implement following function somewhere in his shared assembly:
public bool IsWebApp(){
return (HttpContext.Current != null);
}
So, if there is Current HttpContext than function has been called from ASP.NET application and if there is not such an object, function is called from Windows Forms application.
Simple enough.

Handling Miliseconds in .NET and SQL Server

noreply@blogger.com (DLM@BYPsoft) — Tue, 26 Feb 2008 21:51:00 +0000

Have you taken a look at number of such a questions in online community? It looks like that a lot of .NET developers have a problems reading and updating milliseconds value from SQL Server databases. So what is a real problem here?
Let's take a look at common scenario. Developers read data from a table which has Datetime column ColDateTime. Let's say they are storing values in some DataTable table1. Suddenly, if you want just to print a Value of that field, with

table1.Rows[0].Cells["ColDateTime"].Value.ToString()

you will get value like '2008/02/26 2:26:53 PM'. But you value in database is actually '2008/02/26 14:26:53.3480'. So milliseconds are missing. The only way to get them is to convert value from database in DateTime variable and then print out that variable:

DateTime dtDbValue = System.Covert.ToDateTime(table1.Rows[0].Cells["ColDateTime"]);
dtDbValue.ToString();

Now, you have a real value: '2008/02/26 14:26:53.3480'

Connected to this is also reverse process, where people lose milliseconds during update. Take a look at following statements:

DateTame dtValue = DateTime.Now;

string sql = "update table1 set ColDateTime = '" + dtValue.ToString() + "'";

-- create DataCommand, assign CommandText and execute.

You will see that your column has been updated with the wrong value. Again milliseconds cut.

People try very often to format ToString method call to looks like:
string sql = "update table1 set ColDateTime = '" + dtValue.ToString("yyyy/MM/dd hh:mm:ss.ffff") + "'";

but this cause an exception on SQL Server side saying that varchar value can not be converted to Datetime type. Strange, and maybe lack of documentation, but the solution is to format your DateTime value with different format - using 3 f and not 4:

string sql = "update table1 set ColDateTime = '" + dtValue.ToString("yyyy/MM/dd hh:mm:ss.fff") + "'";

Compare databases fast, safe, free... DBTyP.NET - in version 2008 supports Oracle besides SQL Server and MySQL. DBTyP.NET 2008 is coming... in 3 weeks.

Great Help Authoring Tool

noreply@blogger.com (DLM@BYPsoft) — Sat, 02 Feb 2008 22:51:00 +0000

We have just discovered and get impressed with Rolehelp. We are still testing different help authoring tools but this one with their simplicity, great user interface, feature reach capabilities and creation of 7 different help formats simply fascinate us. Of course, we have never wanted to spend 1000$ for such a tool, therefore Roledata offer of 59$ and free demo version with limitation of 50 topics is very, very attractive one. So, if you need fast, simple, free or at least cheap help authoring tool, visit them and you will get one great tool.
I'm sure we will use it in our data and schema compare solution in version 2008.

Synchronize and compare the structure and data of your SQL Server, Oracle, and MySQL databases - DBTyP.NET by BYPsoft

MSDN Launches Code Galery

noreply@blogger.com (DLM@BYPsoft) — Tue, 29 Jan 2008 08:56:00 +0000

We found very interesting and wanted to share it with all of you that MSDN launches yesterday Code Gallery dedicated to developers community for sharing samples, tools, articles, participating in discussions...
Code Gallery has been used till now by internal Microsoft employess for sharing code samples and tools and now is availalble to the community. Anyone with LIVE ID can post there. Let's see how this will work.

Primary keys without "real" tables

noreply@blogger.com (DLM@BYPsoft) — Sun, 27 Jan 2008 21:12:00 +0000

Have you queried some when information_schema.table_constraints or sys.key_constraints to discover a primary keys in your database? And as a result from select * from information_schema.table_constraints you noticed that there are primary keys without TABLE_SCHEMA and TABLE_NAME in resultset?
You can be confused with this but the explanation is simple. Your primary keys defined in table-valued functions have been listed here. So if you have a function defined as

CREATE FUNCTION [dbo].[fnTest]
(
@foo int
)
returns @resultTable table (test1 int, test2 int primary key (test1))
as
begin
insert into @resultTable(test1, test2)
select 1, @foo

return
end

you will have as CONSTRAINT_NAME something like "PK__fnTest__2D27B809" and TABLE_SCHEMA and TABLE_NAME will be NULL.

Visual Studio 2008 - Automatic Properties in .NET 2.0

noreply@blogger.com (DLM@BYPsoft) — Fri, 23 Nov 2007 21:31:00 +0000

In the next couple of blogs we will present shortly some cool and interesting stuff we found about Visual Studio 2008 and .NET Framework 3.5. For all cool stuff see Visual Studio 2008 Official page.
One of the first items we noticed and played around are Automatic Properties. What does it mean? Untill now it was common to define a class as
public class Test
{
public Test() { }

private string _firstName;
public string FirstName
{
get { return _firstName; }
set { _firstName = value; }
}
}
where for each property you usually had a private member variable.
But now, Visual Studio 2008 and .NET framework 3.5 introduces Automatic Proeprties where your class can be defined as

public class Test
{
public Test() { }
public string FirstName { get; set; }
}
and you will notice that a private member variable is missing and a whole body of the property implementation itself (it has just a stub definition get; set; )
Imagine how much you reduce a time for class scripting. Readability is also much better.
But you will probably ask, where does .NET keep your data now? If you take a look at IL you will find that actually a compiler generates a field (variable) for you:
'<>k__AutomaticallyGeneratedPropertyField0'. Whole construction is done just by using CompilerGeneratedAttribute. But, what we had in mind, this attribute is part of .NET Framework 2.0, what lead us to... Hey, Automatic Properties are possible in .NET 2.0 than?! And this is really true. Open your Visual Studio 2008 (Express), create such a construction, as a Target Framework for a project choose .NET Framework 2.0 and you will see it runs. WOW!!! We like this very much and will use it in DBTyP.NET as soon as possible.

Formatting bounded DateTime fields in ASP.NET

noreply@blogger.com (DLM@BYPsoft) — Tue, 13 Nov 2007 06:59:00 +0000

If you ever tried to work with BoundField object bound to a DateTime field with the DataFormatString, it must happen, that first time you didn't get your DateTime value proeprly formatted.
<asp:BoundField DataField="MyNotFormattedDate" DataFormatString="{0:MM/dd/yyyy}" />

Looks like that you have done everything correct but still getting your value formatted using its ToString() method like "11/13/2007 10:05:12 PM". So, the problem is not in your definition but in ASP.NET which tries to prevent cross site scripting attacks, the field value is HTMLEncoded. And HTMLEncoding occurs before applying any formatting, making your formatting string without effects. To Get your field formatted as you define, you should tell object not to use HTMLEncoding like
<asp:BoundField DataField="MyFormattedDate" DataFormatString="{0:MM/dd/yyyy}" HtmlEncode="false"/>

Compare databases with the best tool - DBTYP.NET Studio.

Changing default column values - SQL Server

noreply@blogger.com (DLM@BYPsoft) — Tue, 30 Oct 2007 21:05:00 +0000

A very common mistake in T-SQL is trying to alter column definition trying to alter DEFAULT value (constraint) for that column. Probably, developers expect to have this because it is possible to assign default value for a column during table definition:
CREATE TABLE Table1 (
col1 INT NOT NULL DEFAULT (0),
col2 INT NOT NULL
)
Due to possibility to change column type from int to varchar(20) or null-ability with
ALTER TABLE Table1 ALTER COLUMN col2 VARCHAR(20) NULL
a lot of developers expect to be able to change a column default value with
ALTER TABLE Table1 ALTER COLUMN col1 DEFAULT (1)
Error message is displayed trying to execute this: "Incorrect syntax near the keyword 'DEFAULT'".
Mistake.
With a create table syntax mentioned earlier, SQL Server creates default constraint with auto generated name like "DF__Table1__col1__57DD0BE4" and stores default value in [text] column of syscomments table.
So, to change a default value for a column you should change a constraint definition and the only way to do that is to drop current constraint and create the new one:
ALTER TABLE table1 DROP CONSTRAINT DF__Table1__col1__57DD0BE4
ALTER TABLE table1 WITH NOCHECK ADD CONSTRAINT [Df_test_col1] DEFAULT (1) FOR col1

Compare databases with the fastest tool - DBTYP.NET

CheckBox header column for DatagridView

noreply@blogger.com (DLM@BYPsoft) — Wed, 24 Oct 2007 19:10:00 +0000

It is very common to have a list of items in DataGridView with a check box in the first column where your later action will depend on user selection. This can be very easy done having a first column defined as DataGridViewCheckBoxCell object. But, how can your customer select all items in the list (let's say you are working on an email client app and user wants to delete all of his 100 spams)?
Unfortunately, .NET framework does not have a header class similar to DataGridViewCheckBoxColumn. Such a problem lead us to the idea to generate a class which will have a check box item in header where developer can have a full control after user check/uncheck item in the header. Common action is to check/uncheck all items in the DataGridView depending if header is checked/unchecked.
Whole article and class definition is available at http://www.codeproject.com/useritems/CheckBoxHeaderCell.asp

Introduction

noreply@blogger.com (DLM@BYPsoft) — Wed, 24 Oct 2007 11:36:00 +0000

BYPsoft decided to be a part of programming community and brings a valuable expirence to others. You can find our posts in different forums all around the world. But now, it is time to collect all of this, and brings new and interesting topics, mainly from .NET area to publicity. Besides writing on .NET stuff we will also promote our database comparison tool DBTyP.NET here.