Thursday, August 24, 2017

Get the last generated Identity column value in SQL Server

In SQL Server Identity column values are auto created. 

There are multiple ways in sql server, to retrieve the last identity value that is generated. The most common way is to use SCOPE_IDENTITY() built in function.  

Apart, from using SCOPE_IDENTITY(), you also have @@IDENTITY and IDENT_CURRENT('TableName') function. But there are certain very important variation that needs to be taken care. which is explained as below,


Example,

Select SCOPE_IDENTITY()
Select @@IDENTITY
Select IDENT_CURRENT('GalaxyEmployees')

Let's now understand the difference between three of the different scenarios,

SCOPE_IDENTITY() returns the last identity value that is created in the same session (Connection) and in the same scope (in the same session and same scope - Stored procedure, function, trigger). 

Let's say, I have 2 tables GalaxyEmployee1 and GalaxyEmployee2, and I have a trigger on GalaxyEmployee1 table, which will insert a record into GalaxyEmployee2 table. 

So, when we insert a record into GalaxyEmployee1 table, SCOPE_IDENTITY() returns the identity value that is generated in GalaxyEmployee1 table, where as @@IDENTITY returns, the value that is generated in GalaxyEmployee2 table. So, @@IDENTITY returns the last identity value that is created in the same session without any consideration to the scope. 

IDENT_CURRENT('GalaxyEmployees') returns the last identity value created for a specific table across any session and any scope.

In brief:
SCOPE_IDENTITY() - Returns the last identity value that is created in the same session and in the same scope.

@@IDENTITY - Returns the last identity value that is created in the same session and across any scope.

IDENT_CURRENT('Table') - Returns the last identity value that is created for a specific table across any session and any scope.

Wednesday, April 16, 2014

Difference between inner join and equi join and natural join

Inner Join

This is the most used join in the SQL. this join returns only those records/rows that match/exists in both the database tables.

Inner Join Example

  
  1. SELECT * FROM tblEmp JOIN tblDept
  2. ON tblEmp.DeptID = tblDept.DeptID;
Inner Join Result
tblEmp.Name
tblEmp.DeptID
tblDept.Name
tblDept.DeptID
Hitesh
1
HR
1
Ankit
2
IT
2
Miki
2
IT
2
Anup
3
ADMIN
3
In the join condition, you can also use other operators like <,>,<>.

Equi Join

Equi join is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi join.

Equi Join Example

  
  1. SELECT * FROM tblEmp JOIN tblDept
  2. ON tblEmp.DeptID = tblDept.DeptID;
  3. --Using Clause is not supported by SQL Server
  4. --Oracle and MySQL Query
  5. SELECT * FROM tblEmp INNER JOIN tblDept USING(DeptID)
Equi Join Result
tblEmp.Name
tblEmp.DeptID
tblDept.Name
tblDept.DeptID
Hitesh
1
HR
1
Ankit
2
IT
2
Miki
2
IT
2
Anup
3
ADMIN
3

Note

  1. Inner join can have equality (=) and other operators (like <,>,<>) in the join condition.
  2. Equi join only have equality (=) operator in the join condition.
  3. Equi join can be an Inner join, Left Outer join, Right Outer join
  4. The USING clause is not supported by SQL Server and Sybase. This clause is supported by Oracle and MySQL.

Natural Join

Natural join is a type of equi join which occurs implicitly by comparing all the same names columns in both tables. The join result have only one column for each pair of equally named columns.

Natural Join Example

  
  1. --Run in Oracle and MySQL
  2. SELECT * FROM tblEmp NATURAL JOIN tblDept
Natural Join Result
DeptID
tblEmp.Name
tblDept.Name
1
Hitesh
HR
2
Ankit
IT
2
Miki
IT
3
Anup
ADMIN
In the above join result we have only one column "DeptID" for each pair of equally named columns.

Note

  1. In Natural join, you can't see what columns from both the tables will be used in the join. In Natural join, you might not get the desired result what you are expecting.
  2. Natural join clause is not supported by SQL Server, it is supported by Oracle and MySQL.

--
Thank you.

Kind Regards,
Anup Shah.
Mobile : +91 8460372171 
Skype  : anupshah.india | gtalk: anupshah2it

​​

Saturday, November 23, 2013

Introduction to Scaleout in SignalR

In general, there are two ways to scale a web application: scale up and scale out.
  • Scale up means using a larger server (or a larger VM) with more RAM, CPUs, etc.
  • Scale out means adding more servers to handle the load.
The problem with scaling up is that you quickly hit a limit on the size of the machine. Beyond that, you need to scale out. However, when you scale out, clients can get routed to different servers. A client that is connected to one server will not receive messages sent from another server.



One solution is to forward messages between servers, using a component called a backplane. With a backplane enabled, each application instance sends messages to the backplane, and the backplane forwards them to the other application instances. (In electronics, a backplane is a group of parallel connectors. By analogy, a SignalR backplane connects multiple servers.)
SignalR currently provides three backplanes:
  • Windows Azure Service Bus. Service Bus is a messaging infrastructure that allows components to send messages in a loosely coupled way.
  • Redis. Redis is an in-memory key-value store. Redis supports a publish/subscribe (“pub/sub”) pattern for sending messages.
  • SQL Server. The SQL Server backplane writes messages to SQL tables. The backplane uses Service Broker for efficient messaging. However, it also works if Service Broker is not enabled.

Implementation

In SignalR, every message is sent through a message bus. A message bus implements the IMessageBus interface, which provides a publish/subscribe abstraction. The backplanes work by replacing the default IMessageBus with a bus designed for that backplane. For example, the message bus for Redis is RedisMessageBus, and it uses the Redis pub/sub mechanism to send and receive messages.
Each server instance connects to the backplane through the bus. When a message is sent, it goes to the backplane, and the backplane sends it to every server. When a server gets a message from the backplane, it puts the message in its local cache. The server then delivers messages to clients from its local cache.
For each client connection, the client’s progress in reading the message stream is tracked using a cursor. (A cursor represents a position in the message stream.) If a client disconnects and then reconnects, it asks the bus for any messages that arrived after the client’s cursor value. The same thing happens when a connection uses long polling. After a long poll request completes, the client opens a new connection and asks for messages that arrived after the cursor.
The cursor mechanism works even if a client is routed to a different server on reconnect. The backplane is aware of all the servers, and it doesn’t matter which server a client connects to.

Limitations

Using a backplane, the maximum message throughput is lower than it is when clients talk directly to a single server node. That's because the backplane forwards every message to every node, so the backplane can become a bottleneck. Whether this limitation is a problem depends on the application. For example, here are some typical SignalR scenarios:
  • Server broadcast (e.g., stock ticker): Backplanes work well for this scenario, because the server controls the rate at which messages are sent.
  • Client-to-client (e.g., chat): In this scenario, the backplane might be a bottleneck if the number of messages scales with the number of clients; that is, if the rate of messages grows proportionally as more clients join.
  • High-frequency realtime (e.g., real-time games): A backplane is not recommended for this scenario.

Enabling Tracing For SignalR Scaleout

To enable tracing for the backplanes, add the following sections to the web.config file, under the rootconfiguration element:

<configuration>
  <system.diagnostics>
    <sources>
      <source name="SignalR.SqlMessageBus">
        <listeners>
          <add name="SignalR-Bus" />
        </listeners>
      </source>
      <source name="SignalR.ServiceBusMessageBus">
        <listeners>
          <add name="SignalR-Bus" />
        </listeners>
      </source>
      <source name="SignalR.ScaleoutMessageBus">
        <listeners>
          <add name="SignalR-Bus" />
        </listeners>
      </source>
    </sources>
    <switches>
      <add name="SignalRSwitch" value="Verbose" />
      <!-- Off, Critical, Error, Warning, Information, Verbose -->
    </switches>
    <sharedListeners>
      <add name="SignalR-Bus" 
          type="System.Diagnostics.TextWriterTraceListener" 
          initializeData="bus.log.txt" />
    </sharedListeners>
    <trace autoflush="true" />
  </system.diagnostics>
  . . .
</configuration>

Friday, September 27, 2013

ASP.NET Themes,Skins,Global Themes,Creating Page Themes,Adding a Skin file to a Page Theme,Applying a Theme to a Web Site


Introduction
Using themes is cool and easy way to create a consistent look and feel across a page or an entire web site. Using themes you can easily customize your server controls with predefined looks that come with the .NET Framework or you can create your own themes for your own look.


Themes
Themes are a way to counter the problems faced when creating a layout for server controls and giving them the same look and feel throughout the entire application, with as little effort as possible. Default or Global themes are contained in a special folder inside the framework and can be declared in the source as well as class files. Custom made themes are saved inside the predefined "App_Themes" folder inside ASP.NET applications, making them easier to manage and use according to your needs. The essential part of themes are skin files with the .skin extension. Besides skin files, a theme can be composed of styles sheets .css files as well as images for added support for the layout of the website.


Skins
A skin file has the file name extension .skin and contains property settings for individual controls such as Button, Label, TextBox, or Calendar controls. Control skin settings are like the control markup itself, but contain only the properties you want to set as part of the theme.
                Code Example
<asp:button runat="server" BackColor="lightblue" ForeColor="black" />

You create .skin files in the Theme folder. A .skin file can contain one or more control skins for one or more control types. You can define skins in a separate file for each control or define all the skins for a theme in a single file.
There are two types of control skins, default skins and named skins:
  • A default skin automatically applies to all controls of the same type when a theme is applied to a page. A control skin is a default skin if it does not have a SkinID attribute. For example, if you create a default skin for a Calendar control, the control skin applies to all Calendar controls on pages that use the theme. (Default skins are matched exactly by control type, so that a Button control skin applies to all Button controls, but not to LinkButton controls or to controls that derive from the Button object.)
A named skin is a control skin with a SkinID property set. Named skins do not automatically apply to controls by type. Instead, you explicitly apply a named skin to a control by setting the control's SkinID property. Creating named skins allows you to set different skins for different instances of the same control in an application.


Global Themes
Built-in default themes are stored under the installation path of the .NET Framework: 
%SystemRoot%\Microsoft.NET\Framework\VX.X.XXXX\ ASP.NETClientFiles\Themes\
The actual name of the subdirectory labeled vX.X.XXXX changes according to the build of ASP.NET. Themes defined in this path are visible to all applications running on the machine. You can also use your create a global theme by saving it in a subfolder of the \Themes\ folder in the above directory.

Creating Page Themes
1.       In the Solution Explorer right-click on web site name and point to Add ASP.NET and click Themes
2.       Visual Studio will create a App_Themes folder automatically
3.       Create a subfolder of the App_Themes folder and name it accordingly
4.       Add Skins, Cascading Style Sheets, and Images as needed


Adding a Skin file to a Page Theme
  1. In the Solution Explorer right-click the name of the theme and click Add New Item
  2. In the Add New Item dialog box click Skin File
  3. Type the name of the .skin in the name box
  4. In the .skin file add control definition using declarative syntax, only include properties you want to set for the theme. The definition must include the runat=”server” attribute and must not include the ID=”” attribute.


Code Example
<asp:Button runat="server"
  BackColor="black"
  ForeColor="green"
  Font-Name="Arial"
  Font-Size="10pt" />

You can create as many or as few .skin files in the theme folder but typically you would only create one per control. You can define only one default Skin per control. If you want more use the SkinID attribute in the skin’s control declaration to create named Skins for the same control.


Code Example
                <asp:Label runat="server" ForeColor="#585880" Font-Size="0.7em" Font-Names="Verdana" SkinID="LabelHeader" />

                <asp:Label runat="server" ForeColor="#585980" Font-Size="0.6em" Font-Names="Arial" SkinID="LabelFooter" />


Adding Cascading Style Sheets to a theme is the same as adding a skin accept in the Add New Item dialog box you select Style Sheet.


Applying a Theme to a Web Site
  1. In the application’s web.config file set the <pages> element to the name of the theme either page or global

Code Example
<configuration>
    <system.web>
        <pages theme="ThemeName" />
    </system.web>
</configuration>

Or

  1. Set as style sheet theme and be subordinate to local control properties set the styleSheetTheme attribute instead

Code Example
<configuration>
    <system.web>
        <pages styleSheetTheme="ThemeName" />
    </system.web>
</configuration>


Applying a Theme to an individual page
Set the Theme or StyleSheetTheme attribute of the @ Page directive to the name of the theme

                Code Example
                                <%@ Page Theme="ThemeName" %>
                <%@ Page StyleSheetTheme="ThemeName" %>


Applying a named skin to a control
Set the control’s SkinID property
               
Code Example
                                <asp:Calendar runat="server" ID="DateSelector" SkinID="LargeCalendar" />

Conclusion

In conclusion I hope this information was helpful to you. Themes are nice way to create a consistent look and feel across Web sites quickly and easily.


Thanks & Regards,
www.galaxywebmind.com