Monday, August 27, 2012

.NET : Extension Method

An Extension method is a new language feature of C# starting with the 3.0 specification, as well as Visual Basic.NET starting with 9.0. Extension methods enable you to "add" methods to existing types without creating a new derived type, recompiling, or otherwise modifying the original type. An extension method is a static method of a static class that you can call as though it were an instance method of a different class. 
For example, you could create an extension method named ToDouble that is a static method in a static class you create named StringConversions, but that is called as though it were a method of an object of type string.
Extension Method Declarations and Invocations:
Specifying a method’s first argument with the this keyword modifier will make that method an extension method. The extension method will appear as an instance method of any object with the same type as the extension method’s first argument’s data type. For example, if the extension method’s first argument is of type string, the extension method will appear as a string instance method and can be called on any string object. Also, extension methods can only be declared in static classes.
Example of an extension method:
namespace Netsplore.Utilities
{
    public static class StringConversions
    {
        public static doubleToDouble(this string s)
        {
            return Double.Parse(s);
        }
        public static boolToBool(this string s)
        {
            return Boolean.Parse(s);
        }
    }
}
Here both the class and every method it contains are static. ToDouble is an extension method, because method is static and its first argument specifies the this keyword.
Calling an extension method:
    usingNetsplore.Utilities;
    double pi = "3.1415926535".ToDouble();
    Console.WriteLine(pi);
This produces the following results:
    3.1415926535
Extension Method Precedence:
Normal object instance methods take precedence over extension methods when their signature matches the calling signature. Extension methods seem like a really useful concept, especially when you want to be able to extend a class you cannot, such as a sealed class or one for which you do not have source code. The previous extension method examples all effectively add methods to the string class. Without extension methods, you couldn’t do that because the string class is sealed.

Thursday, August 23, 2012

T-SQL Syntax

Recently I have came across very good question on SQLServerCentral.com
Different T-SQL constructs can assign a value to a regular identifier with a leading '@' without using SET nor SELECT :
  1. An Input argument to a Procedure or Function - http://msdn.microsoft.com/en-us/library/ms187926
  2. An Output argument to a Procedure - http://msdn.microsoft.com/en-us/library/ms187926
  3. EXECUTE a Function: EXEC @return = udfFunct() - http://msdn.microsoft.com/en-us/library/ms188332
  4. The Stored Procedure return status: EXEC @status = uspProc (This is very different from a Function return.) - http://msdn.microsoft.com/en-us/library/ms188332
  5. The OUTPUT clause: OUTPUT INTO @tablevar (Could count this 4 times but it is really one construct) http://msdn.microsoft.com/en-us/library/ms177564
  6. RECEIVE .... FROM INTO @tablevar - http://msdn.microsoft.com/en-us/library/ms186963.aspx
  7. FETCH NEXT FROM cursor INTO @varname - http://msdn.microsoft.com/en-us/library/ms180152
  8. DECLARE @varname INT = 0; - http://msdn.microsoft.com/en-us/library/ms188927

Wednesday, August 8, 2012

WCF Endpoints : Addresses

All communication with a Windows Communication Foundation (WCF) service occurs through the endpoints of the service. Endpoints provide clients access to the functionality offered by a WCF service.
Each endpoint consists of four properties:
  • An address that indicates where the endpoint can be found.
  • A binding that specifies how a client can communicate with the endpoint.
  • A contract that identifies the operations available.
  • A set of behaviours that specify local implementation details of the endpoint.
Addresses:
In WCF, every service is associated with a unique address. The address provides two important elements: the location of the service and the transport protocol, or transport scheme, used to communicate with the service. The location portion of the address indicates the name of the target machine, site, or network; a communication port, pipe, or queue; and an optional specific path, or URI (Universal Resource Identifier). A URI can be any unique string, such as the service name or a globally unique identifier (GUID).
WCF supports the following transport schemes:
  • HTTP/HTTPS
  • TCP
  • IPC
  • Peer network
  • MSMQ
  • Service bus
Addresses always have the following format:
    [base address]/[optional URI]
The base address is always in this format:
    [transport]://[machine or domain][:optional port]
Here are a few sample addresses:
    http://localhost:8001
    http://localhost:8001/MyService
    net.tcp://localhost:8002/MyService
    net.pipe://localhost/MyPipe
    net.msmq://localhost/private/MyQueue
    net.msmq://localhost/MyQueue


TCP Addresses
TCP addresses use net.tcp for transport and typically include a port number, as in:
    net.tcp://localhost:8002/MyService
When a port number is not specified, the TCP address defaults to port 808:
    net.tcp://localhost/MyService
It is possible for two TCP addresses (from the same host) to share a port:
    net.tcp://localhost:8002/MyService
    net.tcp://localhost:8002/MyOtherService
You can configure TCP-based addresses from different service hosts to share a port.

HTTP Addresses
HTTP addresses use http for transport and can also use https for secure transport. You typically use HTTP addresses with outward-facing Internet-based services, and you can specify a port as shown here:
    http://localhost:8001
If you do not specify the port number, it defaults to 80 (and port 443 for HTTPS). As with TCP addresses, two HTTP addresses from the same host can share a port, even on the same machine.

IPC Addresses
IPC (Inter-Process Communication) addresses use net.pipe for transport, to indicate the use of the Windows named pipe mechanism. In WCF, services that use IPC can only accept calls from the same machine. Consequently, you must specify either the explicit local machine name or localhost for the machine name, followed by a unique string for the pipe name:
    net.pipe://localhost/MyPipe
You can open a named pipe only once per machine, so it is not possible for two named pipe addresses to share a pipe name on the same machine.

MSMQ Addresses
MSMQ addresses use net.msmq for transport, to indicate the use of the Microsoft Message Queue (MSMQ). You must specify the queue name. When you’re dealing with private queues, you must also specify the queue type, but you can omit that for public queues:
    net.msmq://localhost/private/MyService
    net.msmq://localhost/MyService

Service Bus Addresses
Windows Azure AppFabric Service Bus addresses use sb, http, or https for transport, and must include the service bus address along with the service namespace, for example:
    sb://MyNamespace.servicebus.windows.net/

Monday, July 23, 2012

#temp table Vs @table variable

Some facts about Table Variables and Temp Tables are:
  • You can create local and global temporary tables. Local temporary tables (#table_name) are visible only in the current session, and global temporary tables (##table_name) are visible to all sessions. 
  • Table Variables and Temp Tables both use the tempdb database.
  • Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction.
  • Temp tables behave same as normal tables and are bound by transactions.
  • Assignment operation between table variables is not supported.
  • Clustered indexes can be created on both table variables and temporary tables.
  • Both are logged in the transaction log.
  • Temporary tables cannot be partitioned.
  • Table variables are only allowed in SQL Server 2000+, with compatibility level set to 80 or higher.
Difference:
#temp table @table variable
You can truncate a temp table. You cannot truncate a table variable.
You can alter temp table. Table variables cannot be altered after they have been declared.
One of the most valuable assets of a temp table is the ability to add either a clustered or non-clustered index. You cannot explicitly add an index to a table variable, however you can create a system index through a PRIMARY KEY CONSTRAINT, and you can add as many indexes via UNIQUE CONSTRAINTs as you like.
Temp tables can be used in the following situations:
INSERT #temp EXECsp_someProcedure
SELECT * INTO #temp FROM someTable
You cannot use a table variable in either of the following situations:
INSERT @table EXECsp_someProcedure
SELECT * INTO @table FROM someTable
You cannot create temp tables inside user-defined function. You can declare table variable inside user-defined function.
Temp tables allow for the auto-generated statistics to be created against them. The system will not generate automatic statistics on table variables. Likewise, you cannot manually create statistics
Statistics generated on temp table, help the optimizer to determine cardinality. A table variable will always have a cardinality of 1, because the table doesn'texist at compile time.
Temp tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE. You cannot drop a table variable when it is no longer necessary. They are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.
Temp table can be referenced by its name or by an alias, except in the FROM clause.
e.g.: SELECT id FROM #t1 t INNER JOIN #t2 ON t.id = #t2.id
Table variables must be referenced by an alias, except in the FROM clause.
e.g.: SELECT id FROM @foo f INNER JOIN #foo ON f.id = #foo.id
Temp tables are visible to the calling procedure in the case of nested procs. Table variables are not visible to the calling procedure in the case of nested procs.
Use of temp table in stored procedure can cause stored procedure recompilations. Table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used
Temp tables are involved in SQL transactions. Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.
Temp tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices. Table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan.
Only static tables and temporary tables support the statement "SET IDENTITY_INSERT ON".The table variable does not support the statement "SET IDENTITY_INSERT ON".

Wednesday, May 30, 2012

Windows Server Core

Server Core is an exciting new installation option available in Windows Server 2008 that enables branch offices, data centers, and other networking environments to greatly reduce the total cost of ownership (TCO) involved with deploying and managing Windows servers. The Server Core option is a new minimal installation option available that excludes large parts of the graphical user interface(GUI).
A Server Core installation includes only a limited number of server roles compared with a Full installation of Windows Server 2008. It also supports only a limited subset of the features available on a Full installation of Windows Server 2008. 
Benefits of Server Core
  • Greater stability: Because a Server Core installation has fewer running processes and services than a Full installation, the overall stability of Server Core is greater. 
  • Simplified management: Because there are fewer things to manage on a Server Core installation, it's easier to configure and support a Server Core installation than a Full one—once you get the hang of it.
  • Reduced maintenance:Because Server Core has fewer binaries than a Full installation, there's less to maintain. For example, fewer hot fixes and security updates need to be applied to a Server Core installation. 
  • Reduced memory and disk requirements: A Server Core installation on x86 architecture, with no roles or optional components installed and running at idle, has a memory footprint of about 180 megabytes (MB), compared to about 310 MB for a similarly equipped Full installation of the same edition. Disk space needs differ even more—a base Server Core installation needs only about 1.6 gigabytes (GB) of disk space compared to 7.6 GB for an equivalent Full installation.
  • Reduced attack surface: Because Server Core has fewer system services running on it than a Full installation does, there's less attack surface (that is, fewer possible vectors for malicious attacks on the server). This means that a Server Core installation is more secure than a similarly configured Full installation.
Read more : http://technet.microsoft.com/en-us/library/dd184075.aspx
 

Features of SQL Server 2012

This latest release of the SQL Server presents new features and improvements that increase the power and efficiency of architects, developers, and administrators who design, develop, and maintain data storage systems.
  • Availability Enhancements
AlwaysOn SQL Server Failover Cluster Instances
AlwaysOn Failover Cluster Instances leverages Windows Server Failover Clustering(WSFC) functionality to provide local high availability through redundancy at the server-instance level - a failover cluster instance(FCI).

AlwaysOn Availability Groups
With AlwaysOn, users will be able to fail over multiple databases in groups instead of individually. Also, secondary copies will be readable, and can be used for database backups.
  • Manageability Enhancements
Manageability of the SQL Server 2012 Database Engine is improved by enhancement to tools and monitoring features listed below:
    SQL Server Management Studio
    Startup Option
    Contained Databases
    Data-tier Applications
    Windows PowerShell
    BCP Utility
    sqlcmd.exe
    Database Engine Tuning Advisor
  • Programmability Enhancements
Sequences
Sequence is a user defined object that generates a sequence of a number.

Ad-Hoc Query Paging
The Order By option in the SQL SELECT statement has been enhanced in SQL Server 2012. Using a combination of OFFSET and FETCH along with ORDER BY gives you control of paging through a result set. Using this technique can really help performance by bring back only the results you want to show to your users when they are needed.

Full Text Search
The Full Text Search in SQL Server 2012 has been enhanced by allowing you to search and index data stored in extended properties or metadata.
  • Scalability and Performance Enhancements
Scalability and performance enhancements in the Database Engine includes:
    Columnstore Indexes
    Online Index Create, Rebuild, and Drop
    Partition Support Increased
    FILESTREAM Filegroups Can Contain Multiple Files
  • Security Enhancements
Security enhancements in the SQL Server Database Engine include provisioning during setup, new SEARCH PROPERTY LIST permissions, new user-defined server roles, and new ways of managing server and database roles.
  • Resource Governor Enhancements
The enhancements to the Resource Governor enable you to more effectively govern performance in multi-tenancy environments like private cloud. The enhancements include support for 64 resource pools, greater CPU usage control, and resource pool affinity for partitioning of physical resources and predictable resource allocation.


Monday, April 2, 2012

Undocumented sp_msforeachtable, sp_msforeachdb procedure

As database administrators or developers, sometimes we need to perform an action on all of the tables within a database or on all the databases within a instance. Microsoft SQL Server provides two undocumented stored procedures designed for iteration that allow you to process through all tables in a database, or all databases in a SQL Server instance. The first stored procedure, "sp_msforeachtable" allows you to easily process some code against every table in a single database. The other stored procedure, "sp_msforeachdb" will execute a T-SQL statement against every database associated with the current SQL Server instance.
 
For example, the following script checks the integrity of each table in the AdventureWorks database using the DBCC CHECKTABLE command. Notice that a [?] is used as a placeholder for the table name in the SQL statement.
USE AdventureWorks;
EXECUTE sp_msforeachtable 'DBCC CHECKTABLE ([?])';
 
As another example, you can sp_msforeachdb procedure to find all the stored procedures in all available databases on the instance which have the word 'RPT' in their definition by running the following command.
EXECUTE sp_msforeachdb 'SELECT ''?'' AS DB, SPECIFIC_NAME, OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME)) FROM [?].INFORMATION_SCHEMA.ROUTINES WHERE OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME)) LIKE ''%RPT%''';

Some level of testing and care should be taken when using undocumented code from Microsoft. Since these stored procedures are not documented, it means that Microsoft might change this code with any new release or patch without notifying customers.