Sunday, June 9, 2013

SELECT SalesOrderID
,   ShipDate
FROM Sales.SalesOrderHeader
WHERE ShipDate BETWEEN '2005-07-23T00:00:00'
                       AND '2005-07-24T23:59:59'


IS NULL
IS NOT NULL


SELECT ProductID,
   Name,
   ISNULL(Weight,0) AS Weight
FROM Production.Product
WHERE Weight IS NULL;

SELECT ProductID,
   Name,
   Weight
FROM Production.Product
WHERE Weight IS NOT NULL;


IN

WHERE Color IN ('Silver', 'Black', 'Red')
WHERE Color = 'Silver' OR Color = 'Black' OR Color = 'Red'

WHERE Description LIKE '%/%%' ESCAPE '/
Wildcards for the LIKE predicate
WildcardUsage
%The percent sign. Represents a string of zero or more characters
_The underscore. Represents a single character
[…]A list of characters enclosed within square brackets. Represents a single character from among any in the list. You may use the hyphen as a shorthand to translate a range into a list. For example, [ABCDEF]-flatcan be written more succinctly as[A-F]-flat.You can also mix and match single characters and ranges. The expressions[A-CD-F]-flat[A-DEF]-flat, and [ABC-F]-flat all mean the same thing and ultimately resolve to [ABCDEF]-flat.
[^…]A list of characters enclosed within square brackets and preceeded by a caret. Represents a single character from among any not in the list.
ORDER BY

ORDER BY p.Name DESC,
   h.EndDate DESC

ORDER BY p.Name ASC,
   h.EndDate ASC;

ORDER BY p.Name ASC,
   h.EndDate DESC;


One issue when ordering by unselected columns is that ORDER BY items must appear in the SELECT list if SELECT DISTINCT is specified. That’s because the grouping operation used internally to eliminate duplicate rows from the result set has the effect of disassociating rows in the result set from their original underlying rows in the table. That behavior makes perfect sense when you think about it. A deduplicated row in a result set would come from what originally were two or more table rows. And which of those rows would you go to for the excluded column? There is no answer to that question, and hence the caveat.


SELECT p.ProductID,
   p.Name,
   p.Color
FROM Production.Product AS p
WHERE p.Color IS NOT NULL
ORDER BY 
CASE  LOWER(p.Color)
    WHEN 'red' THEN ' '
    ELSE LOWER(p.Color)
END;

SELECT ProductID, Name
FROM Production.Product
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY



DECLARE @AddressLine2 nvarchar(60)
DECLARE @AddressLine3 nvarchar(60)
SELECT @AddressLine3=ISNULL(@AddressLine2,'10, lake view')
SELECT @AddressLine3


DECLARE @AddressLine1 nvarchar(60) = '101 E. Varnum'
DECLARE @AddressLine2 nvarchar(60) = 'Ambulance Desk'
SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 49862;
IF @@ROWCOUNT = 1
  SELECT @AddressLine1, @AddressLine2
ELSE
  SELECT 'Either no rows or too many rows found.';

Saturday, June 8, 2013

SELECT * FROM table_name

SELECT * FROM HumanResources.Employee

Never use: SELECT * FROM - in production code.

Reason:

1. COLUMNS can vary. Addition or removal of columns affect client application using them.
2. Network traffic - No need to select all the information, if you need only very few.

OperatorDescription
!=Tests two expressions not being equal to each other.
!>Tests whether the left condition is less than or equal to (i.e., not greater than) the condition on the right.
!<Tests whether the left condition is greater than or equal to (i.e., not less than) the condition on the right.
<Tests the left condition as less than the right condition.
<=Tests the left condition as less than or equal to the right condition.
<>Tests two expressions not being equal to each other.
=Tests equality between two expressions.
>Tests the left condition being greater than the expression to the right.
>=Tests the left condition being greater than or equal to the expression to the right.

SELECT * FROM HumanResources.Employee
WHERE JobTitle='SALES Representative'

WHERE - can be combined with AND and OR 

SELECT 
FirstName
,LastName AS [Surname]
FROM
Person.Person


COLUMN ALIAS

SELECT BusinessEntityID AS EmployeeID
       ,VacationHours + SickLeaveHours AS [TotalHolidaysAvailable]
FROM HumanResources.Employee;

TABLE ALIAS:

SELECT E.BirthDate FROM HumanResources.Employee AS E

T-SQL Notes

SELECT list_of_columns_required
FROM list_of_tables_or_views_that_act_as_source_of_data
WHERE list_of_conditions_or_filter
ORDER BY sort_order_columns;

http://msftdbprodsamples.codeplex.com/ --- sample database download for SQL Server products

To connect to specific database in an SQL Server Instance:
USE [AdventureWorks2012]
GO

One SQL Server Instance, can have many databases used by one or more different applications.

select * from [AdventureWorks2012].[HumanResources].[Employee]
select * from AdventureWorks2012.HumanResources.Employee

We can do:


USE [AdventureWorks2012]
GO
SELECT * from HumanResources.Employee


The advantage with high-lighted approach: The code can be run against a different database, as long as the schema exists.

Say we can have [MYSTARTUP] database which has HumanResources schema and the schema contains Employee table.

SELECT
 BusinessEntityID
 ,NationalIDNumber
 ,LoginID
FROM
[HumanResources].[Employee]


In the above HumanResources is Schema. A schema contains the object, and that schema is then owned by a user. Because users own a schema, and the schema contains the object, you can change the owner of the schema without having to modify object ownership.




Wednesday, June 13, 2012

Threading - C# - Glossary


Voltatile
The subject of memory barriers is quite complex. It even trips up the experts from time to time. When we talk about a memory barrier we are really combining two different ideas.
·         Acquire fence: A memory barrier in which other reads & writes are not allowed to move before the fence.
·         Release fence: A memory barrier in which other reads & writes are not allowed to move after the fence.
A memory barrier that creates only one of two is sometimes called a half-fence. A memory barrier that creates both is sometimes called a full-fence.
The volatile keyword creates half-fences. Reads of volatile fields have acquire semantics while writes have release semantics. That means no instruction can be moved before a read or after a write.
The lock keyword creates full-fences on both boundaries (entry and exit). That means no instruction can be moved either before or after each boundary.
However, all of this moot if we are only concerned with one thread. Ordering, as it is perceived by that thread, is always preserved. In fact, without that fundamental guarentee no program would ever work right. The real issue is with how otherthreads perceive reads and writes. That is where you need to be concerned.

MemoryBarrier:
Synchronizes memory access as follows: The processor executing the current thread cannot reorder instructions in such a way that memory accesses prior to the call to MemoryBarrier execute after memory accesses that follow the call to MemoryBarrier
MemoryBarrier();
Mutex:
When two or more threads need to access a shared resource at the same time, the system needs a synchronization mechanism to ensure that only one thread at a time uses the resource. Mutex is a synchronization primitive that grants exclusive access to the shared resource to only one thread. If a thread acquires a mutex, the second thread that wants to acquire that mutex is suspended until the first thread releases the mutex.
The Mutex class enforces thread identity, so a mutex can be released only by the thread that acquired it. By contrast, theSemaphore class does not enforce thread identity.
An abandoned mutex often indicates a serious error in the code. When a thread exits without releasing the mutex, the data structures protected by the mutex might not be in a consistent state. The next thread to request ownership of the mutex can handle this exception and proceed, if the integrity of the data structures can be verified.
// This example shows how a Mutex is used to synchronize access to a protected resource. Unlike Monitor, Mutex can be used with
    WaitHandle.WaitAll and WaitAny, and can be passed across AppDomain boundaries.

// Create a new Mutex. The creating thread does not own the Mutex.
    private static Mutex mut = new Mutex();
// Wait until it is safe to enter.
   mut.WaitOne();
// Release the Mutex.
   mut.ReleaseMutex();
Mutexes are of two types: local mutexes, which are unnamed, and named system mutexes. A local mutex exists only within your process. It can be used by any thread in your process that has a reference to the Mutex object that represents the mutex. Each unnamed Mutex object represents a separate local mutex.
If its name begins with the prefix "Global\", the mutex is visible in all terminal server sessions. If its name begins with the prefix "Local\",
the mutex is visible only in the terminal server session where it was created.

Here is my take on the subject and to attempt to provide a quasi-complete list in one answer. If I run across any others I will edit my answer from time to time.
Mechanisms that are generally agreed upon to cause implicit barriers:
·         All Monitor methods including the C# keyword lock
·         All Interlocked methods.
·         Thread.VolatileRead and Thread.VolatileWrite
·         Thread.MemoryBarrier
·         The volatile keyword.
·         Anything that starts a thread or causes a delegate to execute on another thread including QueueUserWorkItem,Task.Factory.StartNewThread.Start, compiler supplied BeginInvoke methods, etc.
·         Using a signaling mechanism such as ManualResetEventAutoResetEventCountdownEventSemaphore,Barrier, etc.
·         Using marshaling operations such as Control.InvokeDispatcher.Invoke,SynchronizationContext.Post, etc.
Mechanisms that are speculated (but not known for certain) to cause implicit barriers:
·         Thread.Sleep (proposed by myself and possibly others due to the fact that code which exhibits a memory barrier problem can be fixed with this method)
·         Thread.Yield
·         Thread.SpinWait
·         Lazy<T> depending on which LazyThreadSafetyMode is specified
Other notable mentions:
·         Default add and remove handlers for events in C# since they use lock or Interlocked.CompareExchange.
·         x86 stores have release fence semantics
·         Microsoft's implemenation of the CLI has release fence semantics on writes despite the fact that the ECMA specification does not mandate it.
·         MarshalByRefObject seems to suppress certain optimizations in subclasses which may make it appear as if an implicit memory barrier were present. Thanks to Hans Passant for discovering this and bringing it to my attention.1
1This explains why BackgroundWorker works correctly without having volatile on the underlying field for theCancellationPending property.
public sealed class Singleton {
   private Singleton() {}
   private static Singleton value;
   private static object syncRoot = new Object();

   public static Singleton Value {
          get {
                 if (Singleton.value == null) {
                        lock (syncRoot) {
                               if (Singleton.value == null) {
 Singleton newVal = new Singleton();
// Insure all writes used to construct new value have been flushed.
 System.Threading.Thread.MemoryBarrier();
                                      Singleton.value = newVal;         // publish the new value
                               }
                        }
                 }
                 return Singleton.value;
          }
   }     
}
Interlocked:
Provides atomic operations for variables that are shared by multiple threads.
The methods of this class help protect against errors that can occur when the scheduler switches contexts while a thread is updating a variable that can be accessed by other threads, or when two threads are executing concurrently on separate processors. The members of this class do not throw exceptions.
The Increment and Decrement methods increment or decrement a variable and store the resulting value in a single operation. On most computers, incrementing a variable is not an atomic operation, requiring the following steps:
1.       Load a value from an instance variable into a register.
2.       Increment or decrement the value.
3.       Store the value in the instance variable.
If you do not use Increment and Decrement, a thread can be preempted after executing the first two steps. Another thread can then execute all three steps. When the first thread resumes execution, it overwrites the value in the instance variable, and the effect of the increment or decrement performed by the second thread is lost.
The Exchange method atomically exchanges the values of the specified variables. The CompareExchange method combines two operations: comparing two values and storing a third value in one of the variables, based on the outcome of the comparison. The compare and exchange operations are performed as an atomic operation.

Read ---- Returns a 64-bit value, loaded as an atomic operation.

The Read method is unnecessary on 64-bit systems, because 64-bit read operations are already atomic.
On 32-bit systems, 64-bit read operations are not atomic unless performed using Read.

Semaphore
Use the Semaphore class to control access to a pool of resources. Threads enter the semaphore by calling the WaitOne method, which is inherited from the WaitHandle class, and release the semaphore by calling the Release method.

ReaderWriterLockSlim
Represents a lock that is used to manage access to a resource, allowing multiple threads for reading or exclusive access for writing.
ReaderWriterLock is used to synchronize access to a resource. At any given time, it allows either concurrent read access for multiple threads, or write access for a single thread. In a situation where a resource is changed infrequently, a ReaderWriterLockprovides better throughput than a simple one-at-a-time lock, such as Monitor.
ReaderWriterLock works best where most accesses are reads, while writes are infrequent and of short duration. Multiple readers alternate with single writers, so that neither readers nor writers are blocked for long periods.
BARRIER
Enables multiple tasks to cooperatively work on an algorithm in parallel through multiple phases.
CountDownEvent
Represents a synchronization primitive that is signaled when its count reaches zero.
EventWaitHandle
Reset  Sets the state of the event to nonsignaled, causing threads to block. 
Set  Sets the state of the event to signaled, allowing one or more waiting threads to proceed. 
WaitOne: Blocks the current thread until the current WaitHandle receives a signal

ExpensiveData _data = null;
                     bool _dataInitialized = false;
                     object _dataLock = new object();
                  //  ...

                    ExpensiveData dataToUse = LazyInitializer.EnsureInitialized(ref _data, ref _dataInitialized, ref _dataLock);


ManualResetEvent allows threads to communicate with each other by signaling. Typically, this communication concerns a task which one thread must complete before other threads can proceed.
When a thread begins an activity that must complete before other threads proceed, it calls Reset to put ManualResetEvent in the non-signaled state. This thread can be thought of as controlling the ManualResetEvent. Threads that call WaitOne on theManualResetEvent will block, awaiting the signal. When the controlling thread completes the activity, it calls Set to signal that the waiting threads can proceed. All waiting threads are released.
Once it has been signaled, ManualResetEvent remains signaled until it is manually reset. That is, calls to WaitOne return immediately.

Saturday, May 19, 2012

.NET - Strings, Encodings

Notes:

1. string.Compare(stringA, stringB, boolIgnoreCase, CultureInfo)



 //the infamous Turkish I problem
            string a = "file";
            string b = "file";
            bool equalInvariant = string.Compare(a, b, true, CultureInfo.InvariantCulture) == 0;
            bool equalTurkish = string.Compare(a, b, true, CultureInfo.CreateSpecificCulture("tr-TR")) == 0;



2. stringA.ToUpper() ---Uses current culture
    stringA.ToUpperInvariant()
    stringA.ToUpper(CultureInfo.CreateSpecificCulture("tr-TR"))


3. stringA.Split(delimitersCharArray);


4.

void Main()
{
string name = "Manikandan";

var bytes = Encoding.ASCII.GetBytes(name);

Console.WriteLine ("ASCII: Bytes: {0} Length: {1}", BitConverter.ToString(bytes), bytes.Length);


bytes = Encoding.Unicode.GetBytes(name);

Console.WriteLine ("Unicode Bytes: {0} Length: {1}", BitConverter.ToString(bytes), bytes.Length);

bytes = Encoding.UTF7.GetBytes(name);

Console.WriteLine ("UTF7 Bytes: {0} Length: {1}", BitConverter.ToString(bytes), bytes.Length);

bytes = Encoding.UTF8.GetBytes(name);

Console.WriteLine ("UTF8 Bytes: {0} Length: {1}", BitConverter.ToString(bytes), bytes.Length);

bytes = Encoding.UTF32.GetBytes(name);

Console.WriteLine ("UTF8 Bytes: {0} Length: {1}", BitConverter.ToString(bytes), bytes.Length);

}

output:


ASCII: Bytes: 4D-61-6E-69-6B-61-6E-64-61-6E
Length: 10

Unicode Bytes: 4D-00-61-00-6E-00-69-00-6B-00-61-00-6E-00-64-00-61-00-6E-00
Length: 20

UTF7 Bytes: 4D-61-6E-69-6B-61-6E-64-61-6E
Length: 10

UTF8 Bytes: 4D-61-6E-69-6B-61-6E-64-61-6E
Length: 10

UTF8 Bytes: 4D-00-00-00-61-00-00-00-6E-00-00-00-69-00-00-00-6B-00-00-00-61-00-00-00-6E-00-00-00-64-00-00-00-61-00-00-00-6E-00-00-00
Length: 40

Tuesday, May 15, 2012

C# - Enum

References:




Notes:




  • System.Enum - Derived from ValueType
  • Flag
  • enumerations must have values explicitly assigned that are unique powers of two: 1 2 4 8 16 32 to work correctly.
  • Important methods:
  • Enum.TryParse()
  • Enum.Parse()
  • Enum.IsDefined()
  • HasFlag()
  • Enum.GetValues(typeof(MyEnumInstance))
  • Enum.GetName(typeof(MyEnumInstance), myEnumVariable)
  • You can't override ToString()  for an Enum
  • You can attach meta data to Enum types with Attribute mechanism and Extension Methods.  
  • (MyAttribute[]) MyEnumVariable.GetType().GetField(
  • MyEnumVariable.ToString()).GetCustomAttributes(typeof(MyAttributes), false);





Thursday, April 26, 2012

WCF - Questions - 1


  1. What are the contracts WCF provides?
    • ServiceContract
    • OperationContract
    • DataContract
    • MessageContract
    • FaultContract 
  2. How the contracts are implemented?
    • All the above contracts are implemented using Attributes
  3. What are the Message Exchange Patterns in WCF
    • Request-Response
    • Oneway
    • Duplex
  4. What is the different between Request-Response and Oneway Message exchange?
    • OneWay - fire and forget. Message is sent and no reply is received
      • HTTP status code: 202 - Accepted, protocol level ACKnowledgement, which indicates that the request has reached the service. That's it. No information after that.
    • Request-Response - Message is sent and reply is received
      • HTTP status code: 200 - OK
  5. What is Duplex Message Exchange Pattern? When do you really use it? any real time example?
    • Client sends arbitrary number of messages and it is received in any order at server.
  6. What is the first step in writing a service?
    • Define the Contract
  7. Can a method which is exposed via [OperationContract(IsOneWay=true)], return values?
    • No. While hosting the service exception will be thrown, at runtime.
    • Unhandled Exception: System.InvalidOperationException: Operations marked with IsOneWay=true must not declare output parameters, by-reference parameters or return values.
  8. What Serialization engine WCF uses?
    • DataContractSerializer - to serialize and de-serialize data.