July 2008 Entries
If you want a quick way to set some default indexes up in your database the best candidates for your indexes would be the foreign keys. They are usually highly selective and many of our queries tend to have some sort of filter on the foreign keys, this makes them good candidates for an index.
You may be quick to think:
'but sql server has indexes on the foreign keys'
That's not true, since a foreign key is an integrity constraint while an index is for performance. Have a look at an interesting discussion over at sqlblog about this: http://sqlblog.com/blogs/greg_low/archive/2008/07/29/indexing-foreign-keys-should-sql-server-do-that-automatically.aspx
The following SQL Script will create a non clustered index on each of the foreign key columns in our database.
--Create non clustered indexes for all foreign key references in the database
DECLARE @sql nvarchar(max)
SELECT @sql = IsNull(@sql + ';' + char(13) , '') + 'CREATE NONCLUSTERED INDEX [IX_' + tablename + '_' + columnname +'] ON [dbo].[' + tablename + '] ( [' + columnname + '] ASC)'
FROM
(
--Script all foreign key columns that are not already an index SELECT
o.name AS tablename, cols.name AS columnName FROM sys.foreign_key_columns fc
inner join sys.objects o on fc.parent_object_id = o.object_id
inner join sys.columns cols on cols.object_id = o.object_id and fc.parent_column_id = cols.column_id
EXCEPT
SELECT o.name, cols.name FROM sys.index_columns icols
inner join sys.objects o on icols.object_Id = o.object_id
inner join sys.columns cols on cols.object_id = o.object_id and icols.column_id = cols.column_id
) T
ORDER BY
tablename,
columnname
--Display the sql that will be executed
Print @sql
--Run the sql and create all the indexes
exec sp_executesql @sql
To monitor the performance of your existing indexes check out the script sp_indexInfo by Tibor Karaszi.
It is a stored procedure that provides some useful metrics for all the indexes in a given table or in a given database.
Plenty of powerful new language features have fairly recently become mainstream with the launch of VS 2008 in Nov 2007 that supports C# 3.0, amongst which:
- Implicitly Typed Local Variables
- Extension Methods
- Lambda Expressions
- Object and Collection Initializers
- Anonymous Types
- Query Expressions
- Automatically Implemented Properties
Although most of us are still trying to fully absorb the above features, there has been some speculation going on about what sort of features we would like to see in the next version of C# and Jeremy Miller has posted a question with what he would like to see.
Here are some of the language features I would really like to see in C#:
1. Implementing Interfaces by delegation to fields
I would like to see support in C# 4.0 that would allow us to delegate the implementation of an interface to a field in the implementing class. For example this could look something like the following:
//faux code
public class Foo : IList<string>
{
private List<string> _Collection implements IList<string>;
public Foo()
{
_Collection = new List<string>();
}
}
The field could be delegated to implement one or more interfaces from the encapsulating class by separating them with a comma. This would remove a lot of redundant code in scenarios like the above one, ie. Instead of implementing a lot of pass through functions on the encapsulating class (Foo) the functions are directly mapped to the delegated interface implementation. This sort of functionality would also enhance support for mixins
This is known as the delegation pattern and from wikipedia:
The delegation pattern is a technique where an object outwardly expresses certain behaviour but in reality delegates responsibility for implementing that behaviour to an associated object in an Inversion of Responsibility. The delegation pattern is the fundamental abstraction that underpins composition (also referred to as aggregation), mixins and aspects.
Taking it one step even further, one could override the delegated implementation in a syntax like so:
public class Foo : IList<string>
{
private List<string> _Collection { get; set; } implements IList<string>;
public Foo()
{
_Collection = new List<string>();
}
//This would override the delegated implementation
// for nice mixin functionality and easy decorator pattern implementation
public int IList.Add(string value)
{
if (!_Collection.Contains(value))
_Collection.Add(value);
}
}
2. Anonymous type return values
I would like to see the anonymous types becoming first class 'citizens' in C#. Anonymous types can only be used in a local scope and cannot be returned from functions. It would be nice if we could return our strongly typed Linq Query Projection from a function eg:
//faux code
public var GetProductInfos()
{
var productInfos =
from p in products
select new { p.ProductName, p.Category, Price = p.UnitPrice };
return productInfos;
}
3. Some Duck-typing or Structural Subtyping support
If a class has a property or a method signature that is the same as the method signature on a declared interface, then that class implicitly implements that interface, if it is not already inheriting from it. The class would implicitly implement an interface if and only if it would implement all the method signatures of a given interface. Basically
if it walks like a duck and it quacks like a duck, then I would call it a duck! (James Riley)
So what is the difference with Structural Subtyping? I would argue that structural subtyping is more suitable to the static style of C#, since it is a 'static duck typing', or according to wikipedia:
Duck typing differs from structural typing in that only the part of the structure accessed at run time is checked for compatibility.
Lets see how this could be beneficial through a use case:
The .Net framework has a few controls that implement a ReadOnly property amongst which are the TextBox, DataGrid, NumericUpDown.
Lets define the IReadOnlyRestrictable interface in our source like so:
public interface IReadOnlyRestricable
{
bool ReadOnly { get; set; }
}
Let's assume that we wanted to Loop through all the controls on a form and anything that satisfies the above interface signature (i.e has a property 'ReadOnly') set its readonly property to true. With ducktyping the qualifying controls would be cast to a valid IReadOnlyRestrictable instance like below, without needing to resort to reflection
foreach (Control c in f.Controls)
{
//would like to have implicit cast to IReadOnlyRestrictable if interface contract is in class we are checking against
IReadOnlyRestricable editable = c as IReadOnlyRestricable;
if (editable != null)
editable.ReadOnly = true;
}
The main advantage I see with ducktyping is that you can declare interfaces for libraries that you do not have access to, this can be useful in scenarios where you would like to minimise dependancies, check out Phil Haacks more extensive post on duck typing and why he believes it would benefit C# developers.
Apparantly, the c# foreach operator already uses duck typing according to Krzysztof Cwalina
4. Safe Null Dereferencing Operator
I would really like to see a safe way to dereference a null value in an expression that of form Object.Property.Property.Value.
For example if we had Customer?.FirstName and Customer was null, then the expression would evaluate to null instead of raising a null reference exception.
Some more examples of how this could work:
//FAUX CODE
//this would throw a null reference exception as usual if either Customer or Order was null
int orderNumber = Customer.Order.OrderNumber;
//this would not compile since it would require a nullable return type
int orderNumber = Customer.Order?.OrderNumber;
//this would return null if a Customer was null or if Order was null
int? orderNumber = Customer?.Order?.OrderNumber;
if (orderNumber.HasValue)
//... do something with it
//instead of having to do
if ((Customer != null) && (Customer.Order != null))
int a = Customer.Order.OrderNumber
Wrap up
Here is a recent interview of the C# 4.0 team in the room where all the magic happens
What is your take? Do you agree? What features you would like to see?
Have you been shying out of putting extra functions on your SQL Server 2005 using SQL CLR simply because you would like to avoid DLL Hell?!
Well, the good news is that you need not be so worried, since SQL Server does not link to the dll itself, but instead it embeds the assembly in the database. What's even better is that you can deploy your assembly by simply scripting it.
Lets do this with an example, in the previous post there is a sample that shows how to create support for regular expressions using SQL CLR. To deploy this on your production server without having to copy any url's you simply need to open Sql Server Management Studio and go to:
DB > Programmability > Assemblies > AssemblyName > RightClick > ScriptAssembly As > Create To > Clipboard | File | New Window
like so:
In fact this will create the following sql script for you:
CREATE ASSEMBLY [TextFunctions]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300C5987F480000000000000000E0000E210B0108000010000000200000000000000E290000002000000040000000004000002000000010000004000000000000000400000000000000008000000010000000000000030040050000100000100000000010000010000000000000100000000000000000000000C02800004B000000004000002803000000000000000000000000000000000000006000000C000000442800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000014090000002000000010000000100000000000000000000000000000200000602E7273726300000028030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C00000000600000001000000030000000000000000000000000000040000042000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F0280000000000004800000002000500DC200000680700000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CA0F00281100000A2D090F01281100000A2C067E1200000A2A0F00281300000A0F01281300000A17281400000A281500000A2A000330040042000000000000000F00281100000A2D120F01281100000A2D090F02281100000A2C067E1600000A2A0F00281300000A0F01281300000A0F02281300000A17281700000A731800000A2A1E02281900000A2A000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000074020000237E0000E00200005403000023537472696E6773000000003406000008000000235553003C0600001000000023475549440000004C0600001C01000023426C6F620000000000000002000001471500000900000000FA0133001600000100000016000000020000000300000005000000190000000F000000010000000300000000000A0001000000000006003F0038000A00670052000A00720052000600AC009A000600C9009A0006000101E20006000F01E200060023019A0006003C019A00060057019A00060072019A0006008B019A000600A4019A000600C3019A000600E0019A0006000A02F70143001E02000006004D022D0206006D022D020A00B40299020E001103F2020E001703F2020000000001000000000001000100010010001C00000005000100010050200000000096007C000A0001008420000000009600870013000300D22000000000861894001E00060000000100C90200000200CF0200000100C90200000200CF02000003003803210094002200290094002200310094002200390094002700410094002200490094002200510094002200590094002200610094002200690094002200710094002200790094002200810094002C00910094003200990094001E00A10094001E001900D7025C001100E20260001900E8026400A9002403680011002C037000190044037600A90049037A00190094002200090094001E002000830037002E004300D8002E000B0083002E001B0090002E002300BA002E003300BA002E003B00C0002E005B00BA002E004B00BA002E005300BA002E006B00EB002E007B00FD002E006300D8002E007300F4004000830037000480000001000000000000000000000000008B02000002000000000000000000000001002F00000000000200000000000000000000000100460000000000020000000000000000000000010038000000000000000000003C4D6F64756C653E005465787446756E6374696F6E732E646C6C00526567756C617245787072657373696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0053716C537472696E670052656745784D617463680052656745785265706C616365002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F705365727669636573004775696441747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005465787446756E6374696F6E73004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500696E707574007061747465726E006765745F49734E756C6C0046616C7365006765745F56616C75650053797374656D2E546578742E526567756C617245787072657373696F6E730052656765780052656765784F7074696F6E730049734D61746368006F705F496D706C69636974007265706C6163656D656E74004E756C6C005265706C616365000000000003200000000000D51DC224975A24449F15285FBFA1D5530008B77A5C561934E0890800021109110D110D0A0003110D110D110D110D03200001042001010E04200101020520010111450420010108240100020054020F497344657465726D696E6973746963015402094973507265636973650103200002030611090320000E070003020E0E11590500011109020306110D0800040E0E0E0E11590C010007312E302E302E3000002901002464313338356464382D376539322D343065362D383234632D646632376639393862656630000005010000000017010012436F7079726967687420C2A920203230303700001201000D5465787446756E6374696F6E7300000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000C5987F4800000000020000005F0000006028000060180000525344536A480B33B1CFDD46B12FF5343A6664BF01000000433A5C446576656C6F706D656E745C54657374696E675C434C525C5465787446756E6374696F6E735C6F626A5C52656C656173655C5465787446756E6374696F6E732E7064620000E82800000000000000000000FE280000002000000000000000000000000000000000000000000000F02800000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000D00200000000000000000000D00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00430020000010053007400720069006E006700460069006C00650049006E0066006F0000000C020000010030003000300030003000340062003000000044000E000100460069006C0065004400650073006300720069007000740069006F006E00000000005400650078007400460075006E006300740069006F006E0073000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000044001200010049006E007400650072006E0061006C004E0061006D00650000005400650078007400460075006E006300740069006F006E0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003000370000004C00120001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005400650078007400460075006E006300740069006F006E0073002E0064006C006C0000003C000E000100500072006F0064007500630074004E0061006D006500000000005400650078007400460075006E006300740069006F006E0073000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C0000001039000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
Without having the dll nor the requirement to link to any dll you now have part of your change script for deploying this assembly to your production server.
Of course, the server still needs to be CLR Enabled, if it is not CLR Enabled you will need to enable it before creating the assembly:
sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
Before we can test our assembly we register our new functions
CREATE Function RegExMatch(@Input NVARCHAR(512),@Pattern NVARCHAR(127))
RETURNS BIT
EXTERNAL NAME TextFunctions.RegularExpressions.RegExMatch
GO
CREATE Function RegExReplace(@Input NVARCHAR(512),@Pattern NVARCHAR(127), @Replacement NVARCHAR(512))
RETURNS NVARCHAR(512)
EXTERNAL NAME TextFunctions.RegularExpressions.RegExReplace
GO
Now we lets test our newly available functions:
Select dbo.RegExMatch('333-4444','\d{3}-\d{4}')
--returns 1
Select dbo.RegExReplace('333-4444','(\d+)-(\d+)', '$2-$1')
--reverses two numbers seperated by a dash returns 4444-333
And that's all needed. If you would like to check out the source code for creating the original dll and compiling it, have a look here
In this post I'll show just how easy it is to make your SQL Server 2005 database support Regular Expressions through what is known as SQL CLR
Just Fire up Visual Studio and create a new library project (I called it TextFunctions)
Add a new Class and Call it Regular Expressions and simply paste in the following Code:
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Data.SqlTypes;
public class RegularExpressions
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true, IsPrecise=true)]
public static SqlBoolean RegExMatch(SqlString input, SqlString pattern)
{
if (input.IsNull || pattern.IsNull) //nulls dont qualify for a match
return SqlBoolean.False;
//Use the static IsMatch method. This is more performant than creating a
// new instance of Regex as the static method also caches the last expressions we used.
return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase);
}
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString RegExReplace(SqlString input, SqlString pattern, SqlString replacement)
{
if (input.IsNull || pattern.IsNull || replacement.IsNull)
return SqlString.Null;
return new SqlString(Regex.Replace(input.Value, pattern.Value, replacement.Value, RegexOptions.IgnoreCase));
}
}
In this example we have two functions: one to Match a Regular expression pattern and one to perform a Replace based on a regular expression pattern.
To enable our SQL Server database to make use of the above functions we need to follow the steps below:
- Compile the library for Release
- CLR Enable Sql Server with:
sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
Reference the assembly and register the functions:
CREATE ASSEMBLY TextFunctions FROM 'c:\Development\Testing\CLR\TextFunctions\bin\Release\TextFunctions.dll'
GO
CREATE Function RegExMatch(@Input NVARCHAR(512),@Pattern NVARCHAR(127))
RETURNS BIT
EXTERNAL NAME TextFunctions.RegularExpressions.RegExMatch
GO
CREATE Function RegExReplace(@Input NVARCHAR(512),@Pattern NVARCHAR(127), @Replacement NVARCHAR(512))
RETURNS NVARCHAR(512)
EXTERNAL NAME TextFunctions.RegularExpressions.RegExReplace
GO
Now we're ready to put our new functions to the test:
select dbo.RegExMatch('me@mymail.com','^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,6}$')
--Verifies an email address
SELECT dbo.RegExMatch('12354', '\d')
--Verifies number Returns 1
SELECT dbo.RegExReplace('Poem Title (Author)', '.*?\((.*?)\).*', '$1')
-- Returns the match within the parenthesis, returns: Author (Useful for splitting one column in two)
SELECT EmailAddress, dbo.RegExMatch(EmailAddress,'^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,6}$')
FROM Users
ORDER BY 2
--Checks your users table for Invalid Email addresses,
--at the top of the results all users with invalid email addresses
Regular expressions can come in really handy especially in data cleansing/transformation operations in your database.
Here are some further pointers to boost creativity with regular expressions:
Email Validation with Regular Expressions
Regular Expressions Cheat Sheet
RegExLib - Library of Regular Expressions for .Net
Well, i guess that's a pretty elaborate title, but it's what this post is about. How you text-wrapping when exporting to Excel via SQL Server Reporting services 2005 on the web be disabled.
It is unfortunate that there is no configuration option in when authoring a report to control whether the text can wrap or not.
Why disable text wrapping?
In the above screenshot we can see that when a given cell is constrained in size (i.e fixed height).
With text wrap on: the cell will not show the complete text unless it is wide enough.
With text wrap off: the complete text will display by overflowing onto the adjacent empty cells. So long as the next cells are empty.
But why would I want to do that when I could simply merge cells B + C + D together? It all comes down to usability and user expectations. If the end user expects to be able to sort the data in the columns then merged cells is just a feature that will be an obstacle in his path. In the screenshot below I try to sort by column B, but 1 of the rows contains merged cells so Excel naturally prompts the user.
If you do find yourself with a reporting services report that you have spent hours creating in your report designer and you are handed the above niche requirement, there are the following options for you friend:
If a third party product is not an option for you, you may find the following helpful.
Disable Text Wrap using Microsoft.Office.Interop.Excel
Office.Interop requires that Excel is installed on the machine that will render the report. Here is the function required:
private static void DisableTextWrap(string fileName)
{
Application excel = new Application();
try
{
Workbook workbook = excel.Workbooks.Open(
fileName,
Type.Missing, false, Type.Missing, Type.Missing,
Type.Missing, true, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
try
{
Worksheet wsheet = workbook.Sheets[1] as Worksheet;
try
{
Range excelRange = wsheet.UsedRange;
try
{
excelRange.WrapText = false;
}
finally
{
Marshal.ReleaseComObject(excelRange);
}
}
finally
{
Marshal.ReleaseComObject(wsheet);
}
workbook.Save();
}
finally
{
workbook.Close(false, fileName, null);
Marshal.ReleaseComObject(workbook);
}
}
finally
{
excel.Quit();
Marshal.ReleaseComObject(excel);
}
}
The above function will open an excel file and disable text-wrap in the used area of only the first spreadsheet
Enable a Web Server to run Excel through COM automation
In order to enable a web server to run Excel through COM automation you can follow this very helpful and detailed advice:
http://blog.crowe.co.nz/archive/2006/03/02/589.aspx
One point to note after following the above instructions
After running DCOMCNFG
In the Security Tab
Under Activation
Click customise. I would recommend that the excel is launched (activated) by a user that has already used excel once on the server you are trying to perform this. So, first create a user, then go to Excel right click and use 'Run As..' option and select the newly created user. The reason for doing this is that when a user runs excel for the first time, excel creates a profile for them and does some custom installation and also pops up a dialog box which is something we would like to avoid during the COM automation. Doing this will ensure that the installation of the user profile is not trying to take place under the COM automation.
Hope this helps someone!