Fetch Unique random Record in sql server

Fetch Unique random Record in sql server

In This Artical You can easily fetch unique and random record And you get each time unique record if you execute this query every time you fetch unique and random record using Newid() function.
Follow some steps-

• Step 1 - Firstly Create Data base and create Table See in Example.
• Step 2 - Create table after that insert some record and select records.
• Step 3 - Now get some random and unique record than use new id function() ,this function fetch                       random record each time

Example

Step 1-
CREATE TABLE Tbl_Customer
(
      ID INT PRIMARY KEY IDENTITY(1,1),
      Name NVARCHAR(200),
       Salary DECIMAL(18,2)
)
Step 2-
Insert Record -
INSERT INTO Tbl_Customer(Name,Salary)VALUES('RAJ',25000)
Insert multiple records and select with select query
Select * from Tbl_Customer
Step 3-
SELECT TOP 1 * FROM Tbl_Customer ORDER BY NEWID()
Fetch unique and random record then use this query-
if you execute this query then got one record and next time execute then got different record so you can easily find unique and random records using NewId() function.

The key here is the NEWID function, which generates a globally unique identifier (GUID) in memory for each row.By definition, the GUID is unique and fairly random; so, when you sort by that GUID with the ORDER BY clause, you get a random ordering of the rows in the table. So finaly we get Unique and Random rows by using this query. We use Top 1 its use to only Top 1 result in table.

SQL Select Top

In this artical filtring records according SELECT TOP clause. The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance. You can select top 10 percent also and filter record according percents.

Example

Create table tbl_Employee in SQL Server.
CREATE TABLE Tbl_Employee
(
Id int primary key identity(1,1),
Name nvarchar(50),
Salary decimal(18,2)
)

Insert Records in table with Insert Query like.Write insert query and execute (f5).

INSERT INTO Tbl_Employee VALUES('JEET',30000)

Inserted record and after that Select table and show data.

Example

SELECT * FROM Tbl_Employee

Output

Now Select table and table return all rows you can Apply select Top then Write query select top like

Example Select Top

Syntex

SELECT TOP number|percent ColumnName(s) FROM TableName
SELECT Top 5 * FROM Tbl_Employee

Output

Note- In this example Select Top 5 then show only 5 records and you can Write top6 ,7,8...etc and get result according top numbers. Select Top number you want to give percent then write percent also like. In this example select top 50 percent then record give just half of all records.It is filter According percentage.

Example Select Top

Syntex

SELECT Top 50 PERCENT * FROM Tbl_Employee

Rank Given In Table

Rank Given in Table in SQL Server
If We Want to Give Indviual Rank And Same Rank to Same data Then Use

Use Of  ROW_NUMBER() Function 
1.This function will assign a unique id to each row returned from the query.
2.RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

DENSE_RANK()
Trivially, DENSE_RANK() is a rank with no gaps, i.e. it is “dense”. We can write:

Code
Create table-
CREATE TABLE Tbl_CityMaster1
(
Id INT PRIMARY KEY IDENTITY,
CityName nvarchar(20),
C_Id int
)
Data Insertd Then OutPut
OutPut-
Id  CityName   C_Id
1    Allahabad    1
2    Allahabad   1
3    Allahabad    1
4    Patna        1
5    Patna        2
6    Patna        3
7    Varanasi    3
8    Varanasi    3
9    Varanasi    3
10    Varanasi    3

Give Rank 

Query-
SELECT
    Id,CityName,[C_id],
    Row_index = ROW_NUMBER() OVER(PARTITION BY [C_Id] ORDER BY [C_Id]),
    SameRank = DENSE_RANK() OVER (ORDER BY [C_Id])
FROM Tbl_CityMaster

OutPut

Id CityName  C_Id RowNo SameRank
1    Allahabad     1    1     1
2    Allahabad     1    2     1
3    Allahabad     1    3     1
4    Patna             1    2     2
5    Patna             2    2     2
6    Patna             3    2     2
7    Varanasi        3    1     3
8    Varanasi        3    2     3
9    Varanasi        3    3     3
10    Varanasi      3    4     3

Read More

Operator in JavaScript

What is an operator?

JavaScript has both binary and unary operators, and one special ternary operator, the conditional operator. A binary operator requires two operands, one before the operator and one after the operator:

operand1 operator operand2
For example, 3+4 or x*y.

A unary operator requires a single operand, either before or after the operator:

operator operand
or
operand operator
For example, x++ or ++x.

Let us take a simple expression 5 + 5 is equal to 10. Here 5 and 5 are called operands and ‘+’ is called the operator.
JavaScript supports the following types of operators.

• Arithmetic Operators
• Comparision Operators
• Logical (or Relational) Operators
• Assignment Operators
• String Operators
• Conditional (or ternary) Operators

Using this operator we easly add,subtract,multiply,divide etc like see in example. Operator Description Example Result.

Arithmetic Operators

Operator	Description	Example	Expression	Result
+	Addition	x=2 y=2	x+y	4
-	Subtraction	x=5, y=3	x-y	2
*	Multiplication	x=5,y=5	x*y	25
/	Division	x=15,y=5	x/y	3
%	Modulus (division remainder)	x=5,y=2	x%y	1
++	Increment	x=5	x++	6
--	Decrement	x=5	x--	4

Comparison Operators

Operator	Description	Example
==	is equal to	5==8 returns false
===	is equal to (checks for both value and type)	x=5,y="5" x==y returns true x===y returns false
!=	is not equal	5!=8 returns true
>	is greater than	5>8 returns false
>=	is Greter than equal	5 >=8 returns false
<	is Less than	5 <8 returns true
<=	is Less than equal	5 <=8 returns true

Logical Operators

Operator	Description	Example
&&	and	x=6 y=3 (x < 10 && y > 1) returns true
||	or	x=6 y=3 x==5 || y==5) returns false
!	Not	x=5 y=2 !(x==y) returns true

Assignment Operators

In this opertor You can assign value in variable in javascript

Syntex -    var x= value;

Example - var x = 5;

=  this is Assign Operator you can assign any value using this operator. In this example assign 5 value in x variable and use this

String Operators

A string is most often text, for example "Hello World!". To stick two or more string variables together, use the + operator.

txt1="How a very";
txt2="nice flower
txt3=txt1+txt2;

The variable txt3 now contains "How a verynice flower!". To add a space between two string variables, insert a space into the expression, OR in one of the strings.

txt1="How a very";
txt2="nice flower!";
txt3=txt1+" "+txt2;
or
txt1="How a very ";
txt2="nice flower!";
txt3=txt1+txt2;

The variable txt3 now contains "How a very nice flower!".

Conditional Operators

JavaScript also contains a conditional operator that assigns a value to a variable based on some condition.

Syntax
variablename=(condition)?value1:value2
Example
greeting=(visitor=="PRES")?"Dear President ":"Dear ";

If the variable visitor is equal to PRES, then put the string "Dear President " in the variable
named greeting. If the variable visitor is not equal to PRES, then put the string "Dear " into the variable named greeting.

Read More

Conditional Statements in JavaScript

Conditional Statements

In This Tutorial You want to perform different actions for different situvation.
In JavaScript we have the following conditional statements:

• if statement .
• if-else statement .
• if...else if....else statement.
• switch statement.

• if statement - If statement if you want to execute some code only if a specified condition is true

Syntax

  if (condition)
  {
          Write if condition which is true
  }

Example

<!DOCTYPE html>
<html>
<head>
  <script type="text/javascript">
    //If Age is grater than 18 then eligible for voting
     var age= 20;
       if(age>18) {
            document.write("Age Is Valid For Vote")
          }
 </script>
</head>
</html>

• if...else statement -  This statement if you want to execute some code if the condition is true and another code if the condition is false

Syntax

if (condition)
  {
    Write True condition.
  }
else
 {
   Write False condition.
 }

Example

<!DOCTYPE html>
<html>
<head>
  <script type="text/javascript">
    //If Age is grater than 18 then eligable for voting
    var age= 15;
    if(age>18) {
         document.write("Age Is Valid For Vote")
        }
    else
        document.write("Age Is Not Valid For Vote")
  </script>
</head>
</html>

• if...else if....else statement - Use this statement if you want to select one of many blocks of code to be executed

Syntax

if (condition1)
{
   code to be executed if condition1 is true
}
else if (condition2)
{
   code to be executed if condition2 is true
}
else if (condition3)
{
   code to be executed if condition3 is true
}
else
{
   code to be executed if condition1 and condition2 and condition3 are not true
}

Example

<!DOCTYPE html>
<html>
<head>
  <script type="text/javascript">
      var d = new Date()
      var time = d.getHours()
       if (time<10) {
            document.write("Good morning");
          }
       else if (time>10 && time)<16 br=""> {
           document.write("Good day");
         }
      else if (time>16 && time)<19 br=""> {
           document.write("Good Evening");
        }
      else if (time>19 && time)<24 br=""> {
          document.write("Good Night");
        }
     else
       {
         document.write("Hello World!");
        }
  </script>
</head>
</html>

• switch statement - Switch statement if you want to select one of many blocks of code to be executed

Syntax

switch(n)
{
  case 1:
    execute code case 1
    break;
  case 2:
    execute code case 2
    break;
  default:
    code to be executed if n is
    different from case 1 and 2
}
This is how it works: First we have a single expression n (most often a variable), that is evaluated once. The value of the expression is then compared with the values for each case in the structure. If there is a match, the block of code associated with that case is executed. Use break to prevent the code from running into the next case automatically.

Example

<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
//You will receive a different greeting based
//on what day it is. Note that Sunday=0,
//Monday=1, Tuesday=2, etc.
   var d=new Date();
   theDay=d.getDay();
   switch (theDay)
    {
        case 5:
           document.write("Day is Friday");
           break;
        case 6:
            document.write("Day is Saturday");
            break;
        case 0:
             document.write("Day is Sunday");
             break;
        default:
              document.write("I'm looking forward to this weekend!");
     }
</script>
</head>
</html>

Read More

Create Duplicate Table of existing table in SQL Server

In This Tutorial we create table and want to same structure and same data then We create new table with data type after that  insert in table this process is very time consuming and take more time so solved this problem in sql server we just create replica of table only using simple query like in example step wise show-

1st Step -   Firstly open sql server and select database

2nd Step  -   Create table like in examples

  Example-

     CREATE TABLE  Employee
      (
              EmpId int,
              EmpName nvarchar(50),
    
      )

3rd Step  -   Created table after that Insert Data.

4Th Step  -   Create Replica of table in Sql query like

Syntex-
               SELECT * INTO New_TableName From Existing_Table

Example

SELECT * INTO Replica_Employee From Employee


5th Step -  Run This Query and See Replica_Employee Table Data

 In this example Replica_Employee is new table name whatever u want and Employee
    is existing table name so using this we easly create duplicate table.

HOW TO CHECK DUPLICATE ENTERY

In this artical you can prvent duplicat entery.Means if you enter any record and you don't enter same record again then you check firstly record is exit and not follows some steps like-

• Step 1  :  Firstly open sql and create database in Sql.
• Step 2  :  Second steps is create table in Sql.
• Step 3  :  Third steps is Insert records in Sql.
• Step 4  :  Forth step Check Duplicat record.
• Step 5  :  Get result.

Step1 :  Craete DataBase with db_Emp

CREATE DATABASE db_Emp
created database and execute run(F5) then use this write code like-
USE db_Emp --

Step 2:Created table Employee and insert record in table step 3 both steps See in previous example. Step 4:Inserted record and check duplicate entery in table like-

SYNTEX

IF((SELECT COUNT(*) FROM TableName WHERE ColumnName= @ColumnName) > 0)
  BEGIN
     SELECT '! exist'
  END
 ELSE
     SELECT '! Not exist'
NOTE- Here @ColumnName is parameter pass when we create procedure then Write this code then we can't face duplicate entry

 

Example

CREATE Sp_CheckDuplicate
 (
 @Name nvarchar(50)
)
 Set @Name='Ram'
 IF((SELECT COUNT(*) FROM Employee WHERE EmpName= @Name) > 0)
  BEGIN
    SELECT '!Record already exist'
  END
ELSE
    SELECT '! Not exist'

Output- !Record already exist
Note- See In previous example in Employee table Ram EmpName is already exit. Output show Record already exist

Read More

Email Validation using Regex Expression in Javascript

• In This Tutorial I Explain How Validate Email Using Regex Expression.
• Firstly take textbox in HTML See in Examle.
• After that Call JavaScript function on text box OnKeyPress.
• Click on text box and enter Email if Email is not valid then show validation error Message, see above Example

Example

<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
function EmailValidation(emailField)
{
   var reg = /^\w+([-+.']\w+)*@@\w+([-.]\w+)*\.\w+([-.]\w+)*$/
    if (reg.test(emailField.value) == false)
      {
        document.getElementById("error").style.display = "inline";
       }
    else
      document.getElementById("error").style.display = "none";
 }
</script>
</head>
<body>
<h3>Email validation using Regex Expression</h3>
<table>
  <tr>
    <td>Email </td>
    <td>: </td >
    <td>
      <input onkeypress="EmailValidation(this)" type="text"/>
      <span id="error" style="color:red;display:none">* Invalid Email Id</span>
    </td>
  </tr>
</table>
</body>
</html>

OutPut - 

Mobile no validation using regex javascript

In This Tutorial I Explained How to validate Phone number using regular expression in javascriptsss.
Firstly take textbox in HTML See in Examle.
After that Call JavaScript function on text box OnKeyPress.
If We Enter Mobile No and obile no is not valid then show validation error message using javascript see above example

Example

<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
function MobileNumValidation(e) {
var reg = /^(7|8|9)\d{9}$/
if (reg.test(e.value) == false) {
document.getElementById("error").style.display = "inline";
}
else
document.getElementById("error").style.display = "none";
} </script>
</head>
<body>
<h2>Mobile Num validation using Regex Expression</h2>
<table>
<tr>
<td>Mobile No
<td>
<td>: </td >
<td>
<input onkeypress="MobileNumValidation(this)" type="text"/>
<span id="error" style="color:red">* Enter valid Mobile Num</span>
</td>
</tr>
</table>
</body>
</html>

Age Validation Using Regex expression in Javascript 

• In This Tutorial I Explain How Validate only Age in Specific range.
• Firstly take textbox in HTML See in Examle.
• After that Call JavaScript function on text box OnKeyPress.
• Click on text box and enter Age if age not in proper range then show validation error using javascript Regex expression, see above example

Example

<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
function AgeValidation(AgeField)
{
   var reg = /^([0]?[1-9]{1,2})$/
   if (reg.test(AgeField.value) == false) {
   document.getElementById("error").style.display = "inline";
}
else
  document.getElementById("error").style.display = "none";
}
</script>
</head>
<body>
<h3>Age validation using javascript</h3>
<table>
   <tr>
     <td>Age
     </td>
     <td>: </td >
     <td>
        <input onkeypress="AgeValidation(this)" type="text"/>
        <span id="error" style="color:red;display:none">* Age Should be in between rang 10 to                        99</span>
     </td>
  </tr>
</table>
</body>
</html>

OutPut-

Numeric Validation-Accept Only Numeric value 

• In This Tutorial I Explain How Validate only numeric value.
• Firstly take textbox in HTML See in Examle.
• After that Call JavaScript function on text box OnKeyPress.
• If We Click on text box then show validation error using javascript see above example.-

Example

<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
var specialKeys = new Array();
specialKeys.push(8); //Backspace
function IsNumeric(e)
 {
     var keyCode = e.which ? e.which : e.keyCode
     var ret = ((keyCode >= 48 && keyCode <= 57) || specialKeys.indexOf(keyCode) != -1);
     document.getElementById("error").style.display = ret ? "none" : "inline";
     return ret;
}
</script>
</head>
<body>
<h2>Numeric validation using javascript</h2>
<table>
  <tr>
      <td>Employee Salary
      </td>
      <td>: </td >
      <td>
      <input  önkeypress="return IsNumeric(event)" type="text"  />
      <span id="error" style="color:red;display:none">* Input digits (0 - 9) </span>
      </td>
 </tr>
</table>
</body>
</html>

OutPut-

 

 

Read More

About Javascript

What is JavaScript?

• JavaScript was designed to add interactivity to HTML pages
• JavaScript is a scripting language
• A scripting language is a lightweight programming language
• A JavaScript consists of lines of executable computer code
• A JavaScript is usually embedded directly into HTML pages
• JavaScript is an interpreted language (means that scripts execute without preliminary compilation)
• Everyone can use JavaScript without purchasing a license

Are Java and JavaScript the Same?

NO!
Java and JavaScript are two completely different languages in both concept and design!
Java (developed by Sun Microsystems) is a powerful and much more complex programming language - in the same category as C and C++.

What can a JavaScript Do?

• JavaScript gives HTML designers a programming tool - HTML authors are normally not programmers, but JavaScript is a scripting language with a very simple syntax! Almost anyone can put small "snippets" of code into their HTML pages JavaScript can put dynamic text into an HTML page - A JavaScript statement like this: document.write("<h1>" + name + "</h1>") can write a variable text into an HTML page
• JavaScript can react to events - A JavaScript can be set to execute when something happens, like when a page has finished loading or when a user clicks on an HTML element
• JavaScript can read and write HTML elements - A JavaScript can read and change the content of an HTML element
• JavaScript can be used to validate data - A JavaScript can be used to validate form data before it is submitted to a server. This saves the server from extra processing
• JavaScript can be used to detect the visitor's browser - A JavaScript can be used to detect the visitor's browser, and - depending on the browser - load another page specifically designed for that browser
• JavaScript can be used to create cookies - A JavaScript can be used to store and retrieve information on the visitor's computer

How to Use Java Script in HTML

Example

<!DOCTYPE html>
<html>
<body>
<script type="text/javascript">
document.write("Hello!");
</script>
</body>
</html>

OutPut

Hellow!

Example Explained

To insert a JavaScript into an HTML page, we use the <script>
tag. Inside the <script> tag we
use the "type=" attribute to define the scripting language.
So, the <script type="text/javascript"> and </script> tells where the JavaScript starts and ends:

<html >
<body >
<script type="text/javascript" >
...
</script >
</body >
</html >

The word document.write is a standard JavaScript command for writing output to a page. By entering the document.write command between the
<script> and </script> tags, the
browser will recognize it as a JavaScript command and execute the code line. In this case the
browser will write Hello World! to the page:

Read More

Advantage and Disadvantage of LINQ

Advantages

1. Faster development
2. Unit of work pattern (context)
3. Auto-generated domain objects that are usable small projects
4. Lazy loading.
5. Learning to write linq queries/lambdas is a must learn for .NET developers.
6. Quick turn around for development
7. Queries can be dynamically
8. Tables are automatically created into class
9. Columns are automatically created into properties
10. Relationship are automatically appeaded to classes
11. Lambda expressions are awesome
12. Data is easy to setup and use
13. LINQ offers a common syntax for querying any type of data sources
14. LINQ expedites development time by catching errors at compile time and includes IntelliSense & Debugging support
15. LINQ expressions are Strongly Typed.

Disadvantages

1. No clear outline for Tiers
2. No good way of view permissions
3. Small data sets will take longer to build the query than execute
4. There is an overhead for creating queries
5. When queries are moved from sql to application side, joins are very slow
6. DBML concurrency issues
7. Hard to understand advance queries using Expressions

Difference between LINQ and Stored Procedure

• In this Artical differences existing between LINQ and Stored procedures. These differences are mentioned below.
• Stored procedures are much faster than a LINQ query as they follow an expected execution plan.
• It is easy to avoid run-time errors while executing a LINQ query than in comparison to a stored procedure as the former has Visual Studio’s Intelligense support as well as full-type checking during compile-time.
• LINQ allows debugging by making use of .NET debugger which is not in case of stored procedures. Easly debugg in linq
• LINQ offers support for multiple databases in contrast to stored procedures in SQL, where it is essential to re-write the code for diverse types of databases.
• Deployment of LINQ based solution is easy and simple in comparison to deployment of a set of stored procedures in SQL. Using linq query it is complex when use jin in query and performance is slow as compare store procedure in Sql

Difference between LINQ to SQL v/s Entity Framework

LINQ to SQL	Entity Framework
It is only works with SQL Server Database you can write query with sql.	It can works with various multiple databases like Oracle, DB2, MYSQL, SQL Server etc.
LINQ to SQL use then generates .dbml to maintain the relation.	It generates an .edmx files initially. The relation is maintained using 3 different files .csdl, .msl and .ssdl
LINQ to SQL it has not support for complex type.	Entity Framework has support for complex type.
LINQ to SQL allows only one to one mapping between the entity classes and the relational tables /views.	Entity Framework allows one-to-one, one-to-many & many-to-many mappings between the Entity classes and the relational tables /views
Linq to sql allows you to query data using DataContext.	Entity Framework allows you to query data using EntitySQL, ObjectContext, DbContext.
LINQ to SQL provides a tightly coupled approach.	Entity Framework provides a loosely coupled approach. Since its code first approach allow you to use Dependency Injection pattern which make it loosely coupled .
LINQ to SQL can be used for rapid application development only with SQL Server.	Entity Framework can be used for rapid application development with RDBMS like SQL Server, Oracle, DB2 and MySQL etc.
LINQ to SQL is a complex type and it reduce performance as compare to Entity Framework.	Entity Framework is fast and less complex as compare to LINQ to SQL.

Read More