StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I will also add, that "normalization" of expression sometimes do not only adds brackets, but it also changes some expressions in a very different way!</p> <p>For example in MS SQL 2008 Express I created a default with the following expression:</p> <pre><code>year(getdate()) + 1 </code></pre> <p>but SQL Server changes it to be</p> <pre><code>(datepart(year,getdate())+(1)) </code></pre> <p>So, I don't believe that any kind of rules or regular expressions will solve your problem for 100% of cases, so I do recommend you to combine several methods</p> <p>1) First of all I think that in your case there is a limited number of typical constraints, that usually exists in most databases. As a rule, there are getdate(), and constant numeric expressions (0), (1). You can have a table of those typical constraints that will help you to match expected and real expressions. </p> <p>2) Then you can try very simple rule to include all fields names in [] brackets and all constants and math operations in (), so you will have <strong>year + 1</strong> transformed into <strong>([year] + (1))</strong>. I suppose this could be done with a Regular Expressions.</p> <p>3) for all cases where you was not able to compare expected and actual results using 1th or 2nd method, you will do what you suggested - create a temp table and compare results.</p> <hr> <p>EDIT 04.Aug:</p> <p>I found that when you create a database-level defaults, they will not be normalized. Strange, eh? But probably, you can use this fact and create database-level defaults that you bind to columns instead of creating default constraints for columns (though, I suppose this will be a very big change in design and will require a huge update of existing databases)</p> <p>As for columns default constraints, and the approach to create/drop defaults dynamically in order to get their normalized form, here is a simple C# code using Microsoft.SqlServer.Management.Smo library. I will suggest to create one test_table with columns IntTest int, VarcharTest varchar(1), DateTimeTest datetime and so on - i.e. only one column for each type. In this case you will create/drop defaults but will not have to create drop table and columns and this will increase the performance.</p> <p>C# code will follow (include using Microsoft.SqlServer.Management.Smo;)</p> <pre><code> Server server = new Server("localhost\\SQLEXPRESS"); Database db = server.Databases["test"]; Table t = db.Tables["test_defaults"]; //here should be some logic to select column name depending on default data type //for example for DateTime defaults we will use "DateTimeTest" column Column c = t.Columns["DateTimeTest"]; //clean up previous results if they exist DefaultConstraint constr = c.DefaultConstraint; if (constr != null) constr.Drop(); //create new constraint constr = c.AddDefaultConstraint(); constr.Text = "getdate()"; constr.Create(); //after refresh we will have a new text constr.Refresh(); string result = constr.Text; //drop it if we don't need it constr.Drop(); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload