A Visual Explanation of SQL Joins

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Sql Joins and Venn Diagrams..
A very interesting read

How to loop in Sql Server

Scenario: We have a Table : TableA

Structure of TableA:

Id                Name

-- ---------------------------

1           Pirate

2            Monkey

3            Ninja

4            Spaghetti

-------------------------------

 

Requirement: Iterate through the Names (“Name” column) of TableA

Solution:

We all know that the most convenient, easy and widely used solution is by using a Cursor.

DECLARE cursor1 CURSOR

         FOR SELECT Name FROM TableA

OPEN cursor1

       FETCH NEXT FROM cursor1

This works, but we should be aware of the disadvantages of using a Cursor…

Cursor implementation in application, helps data manipulation easy and even they are very effective but due to some major disadvantage of Cursor normally they are not preferred.

Disadvantages of cursors

• Uses more resources because Each time you fetch a row from the cursor, it results in a network roundtrip
• There are restrictions on the SELECT statements that can be used.
• Because of the round trips, performance and speed is slow.
• As we know cursor doing round trip it will make network line busy and also make time consuming methods. First of all select queries generate output and after that cursor goes one by one so round trip happen.
• Another disadvantage of cursor are there are too costly because they require lot of resources and temporary storage so network is quite busy.

Apart from these I would like to point out some great advantages of cursor if the entire result set must be transferred to the client for processing and display.

• Client-side memory : For large results, holding the entire result set on the client can lead to demanding memory requirements on client side system.
• Response time : Cursors can provide the first few rows before the whole result set is assembled. If you do not use cursors, the entire result set must be delivered before any rows are displayed by your application.
• Concurrency control :It's a general problem with current applications, If you make updates to your data and do not using cursors in your application, you must send separate SQL statements to the database server to apply the changes. This raises the possibility of concurrency problems if the result set has changed since it was queried by the client. In turn, this raises the possibility of lost updates.But Cursors act as pointers to the underlying data, and so impose proper concurrency constraints on any changes you make.

Some Alternatives to using a Cursor:

• Use WHILE LOOPS
• Use temp tables
• Use derived tables
• Use correlated sub-queries
• Use the CASE statement
• Perform multiple queries

It has been generally observed that looping without using a cursor is faster than looping using a cursor.

Some solutions:

1.  Add the recordset to a new Temp Table and also introduce a new column to the temp table.

*****************************************************
set rowcount 0
select NULL mykey, * into #mytemp from TableA

set rowcount 1
update #mytemp set mykey = 1

while @@rowcount > 0
begin
set rowcount 0
select * from #mytemp where mykey = 1
delete #mytemp where mykey = 1
set rowcount 1
update #mytemp set mykey = 1
end
set rowcount 0

*****************************************************************

2.  The following solution assumes that there is a unique indexed int column named id.

declare @id char( 11 )

select @id = min( id ) from TableA

while @id is not null
begin
    select * from TableA where id = @id
    select @id = min( id ) from TableA where id > @id
end

****************************************************************

3.  Here to the temp table we are adding a new column (RowID ) which is a identity column

DECLARE @RowsToProcess  int
DECLARE @CurrentRow     int
DECLARE @SelectCol1     int

DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), Name varchar(50)) 
INSERT into @table1 (Name ) SELECT Name FROM tableA
SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT
        @SelectCol1=Name
        FROM @table1
        WHERE RowID=@CurrentRow

    --do your thing here--

END

 

******************************************************************

4.

DECLARE @table1 TABLE (
    idx int identity(1,1),
    col1 int )

DECLARE @counter int

SET @counter = 1

WHILE(@counter < SELECT MAX(idx) FROM @table1)
BEGIN
    DECLARE @colVar INT

    SELECT @colVar = col1 FROM @table1 WHERE idx = @counter

    -- Do your work here

    SET @counter = @counter + 1
END

 

Believe it or not, this is actually more efficient and performant than using a cursor.

*********************************************************************

5.

DECLARE
  @LoopId  int
,@MyData  varchar(100)

DECLARE @CheckThese TABLE
(
   LoopId  int  not null  identity(1,1)
  ,MyData  varchar(100)  not null
)

INSERT @CheckThese (YourData)
select MyData from MyTable
order by DoesItMatter

SET @LoopId = @@rowcount

WHILE @LoopId > 0
BEGIN
    SELECT @MyData = MyData
     from @CheckThese
     where LoopId = @LoopId

    --  Do whatever

    SET @LoopId = @LoopId - 1
END

*********************************************************************

6.

*********************************************************************

While fetching we should always remember that SQL Server Queries are SET Based operations and work best in circumstances dealing with SET Based operations.

You can loop through the table variable or you can cursor through it. This is what we usually call a RBAR - pronounced Reebar and means Row-By-Agonizing-Row.

So, we should always strive to find a SET-BASED answer and move away from RBARs as much as possible.

Set based queries are (usually) faster because:

1. They have more information for the query optimizer to optimize
2. They can batch reads from disk
3. There's less logging involved for rollbacks, transaction logs, etc.
4. Less locks are taken, which decreases overhead
5. Set based logic is the focus of RDBMSs, so they've been heavily optimized for it (often, at the expense of procedural performance)

Popular Javascript Frameworks

---

Knockout.js

Knockout is a JavaScript library that helps you to create rich, responsive display and editor user interfaces with a clean underlying data model. Any time you have sections of UI that update dynamically (e.g., changing depending on the user’s actions or when an external data source changes), KO can help you implement it more simply and maintain-ably.

http://knockoutjs.com/

---

Angular.js

AngularJS is an open-source JavaScript framework, maintained by Google, that assists with running what are known as single-page applications. Its goal is to augment browser-based applications with model–view–controller (MVC) capability, in an effort to make both development and testing easier. The library reads in HTML that contains additional custom tag attributes; it then obeys the directives in those custom attributes, and binds input or output parts of the page to a model represented by standard JavaScript variables. The values of those JavaScript variables can be manually set, or retrieved from static or dynamic JSON resources.
http://angularjs.org/

---

Backbone.js

Backbone.js gives structure to web applications by providing models with key-value binding and custom events, collections with a rich API of enumerable functions, views with declarative event handling, and connects it all to your existing API over a RESTful JSON interface.
http://backbonejs.org

---

Node.js

Node.js is a platform built on Chrome’s JavaScript runtime for easily building fast, scalable network applications. Node.js uses an event-driven, non-blocking I/O model that makes it lightweight and efficient, perfect for data-intensive real-time applications that run across distributed devices.
http://nodejs.org/

---

Modernizr

Modernizr is a small JavaScript library that detects the availability of native implementations for next-generation web technologies, i.e. features that stem from the HTML5 and CSS3 specifications. Many of these features are already implemented in at least one major browser (most of them in two or more), and what Modernizr does is, very simply, tell you whether the current browser has this feature natively implemented or not.
http://modernizr.com

---

Require.js

RequireJS is a JavaScript file and module loader. It is optimized for in-browser use, but it can be used in other JavaScript environments, like Rhino and Node. Using a modular script loader like RequireJS will improve the speed and quality of your code.
http://requirejs.org

---

Less

LESS extends CSS with dynamic behaviour such as variables, mixins, operations and functions.LESS runs on both the server-side (with Node.js and Rhino) or client-side (modern browsers only).
http://lesscss.org/

---

Sass

Sass is an extension of CSS3, adding nested rules, variables, mixins, selector inheritance, and more. It’s translated to well-formatted, standard CSS using the command line tool or a web-framework plugin.
http://sass-lang.com

 

Source: http://www.codeproject.com/Articles/596800/JavaScript-Frameworks-and-Resources