Auto Increment Id in Sql Server

SQL server identity column values use in table

Steps-Firstly Open-> Sql Server Create Table -> First Field Like ID Usually Autoincrement=>
Table create then=>insert data in table=> then Select data show increment id.

The following SQL statement defines the "ID" column to be an auto-increment primary key field in the "Category" table (Given Category is Table Name and CategoryId And  CategoryName are Field  in Table

         Create table Category

             (

                   CategoryId INT IDENTITY(1,1) Primary Key,

                   CategoryName NVARCHAR(100)     

             )

  We use IDENTITY(1,1) in Table .

Where the 1,1 is the starting number is 1 and increment  by 1 that's the resion We use Identity(1,1)

In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

Exp. To specify that the "ID" column should start at value 10 and increment by 5, change it to IDENTITY(10,5).

Result-

   In This Following Image CategoryId Is Auto Increment And Id id Start with 1 and It Will 

    Increment By 1

    

  Use This Steps In Result-

  1. Create Table With Create Query.

  2. Insert Data With Insert Query.

  3.  Show Table Data Through Select Query in Sql Server.

Read More

Get Random Rows in SqlServer

How To Get  Random  ROWS in  Table  Using Sql Server


Step 1- 
             Open Sql Server and Create Table like in Ex.

Ex.

Step 2-
              We want to  fetch Unique Randomly Rows and values in table  Then use query like

Syntax-   SELECT TOP 2 * FROM tbl_Randomvalues ORDER BY NEWID() 

Result

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 2 its use to only Top 2 result in table.

SELECT TOP 2 * FROM tbl_Randomvalues ORDER BY NEWID() 

Read More

Reset Identity Column in Sql Server

We Want To Reset Identity Column Then Use Code.If We Delete column By Id Like We delete 5 no id and
After Than we Insert New then We get 6 no Id .We Show Data 1 to 4 id then 6 because 5 no id We Deleted Then Use Code and Get 5 id In Example-
Example-
SELECT * FROM vibha1<Table Name> --Here Vibha1 is TableName
We get Values-

Delete data  from table
Ex.
DELETE FROM vibha1 WHERE id=6
One Value Delete then Insert Value
INSERT INTO vibha1(name) VALUES('vibha1')

Get Values-

Then We Use Code
Syntax: DBCC CHECKIDENT( <TableName>, RESEED, <ValuesThen Start>)
And Delete id 7 record
DELETE FROM vibha1 WHERE id=7
Then  Execute This
 DBCC CHECKIDENT( vibha1 , RESEED, 5)
Then Insert
INSERT INTO vibha1(name) VALUES('vibha1')
Now Get Result

Read More

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

Create Table

Create Table In Sql Server

Syntax-

CREATE TABLE TableName
(
ColumnName1  INT PRIMARY KEY IDENTITY,
ColumnName2,
ColumnName3
)

Ex.
 CREATE TABLE Tbl_CityMaster
(
Id INT PRIMARY KEY IDENTITY,
CityName nvarchar(20),
C_Id int
)

Read More

Show Table Data

This IS Code For Show  Table Data..
If We Want to Get Data Inserted in Any  Table And Copy then use  this Code in SQL Server

Syntax-
DECLARE @Fields VARCHAR(max); SET @Fields = '[ColumnName1], [ColumnName2], [ColumnName3]' -- your fields, keep []
DECLARE @Table  VARCHAR(max); SET @Table  = 'Table Name'   -- your Table Name
DECLARE @SQL    VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Fields + ')'') + CHAR(13) + CHAR(10) +
 ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Fields, ',', ' + '', '' + '), '[', ''''''''' + CAST('),']',' AS VARCHAR(max))
+ ''''''''') +' FROM ' + @Table + '
PRINT @S'
EXEC (@SQL)

 Ex.-
DECLARE @Fields VARCHAR(max); SET @Fields = '[id], [Waiver], [isactive]' -- your fields, keep []
DECLARE @Table  VARCHAR(max); SET @Table  = 'plot_Waiver'               -- your table
DECLARE @SQL    VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Fields + ')'') + CHAR(13) + CHAR(10) +
 ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Fields, ',', ' + '', '' + '), '[', ''''''''' + CAST('),']',' AS VARCHAR(max))
+ ''''''''') +' FROM ' + @Table + '
PRINT @S'
EXEC (@SQL)
OutPut-

INSERT INTO plot_Waiver([id], [Waiver], [isactive])
SELECT '1', '40.00', '1' UNION
SELECT '2', '25.00', '1' UNION
SELECT '4', '20.00', '1' UNION
SELECT '5', '45.00', '1'

Read More

Alter Query

To add a column in a table, use Alter Query the following syntax:

ALTER TABLE table_name
ADD column_name datatype.

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column)

ALTER TABLE table_name
DROP COLUMN column_name.

To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name

MODIFY COLUMN column_name datatype.
(InMySql)

Read More

CHECK WHETHER DUPLICATE EATERY

IF((SELECT COUNT(*) FROM TableName WHERE ColumnName=@ColumnName AND ColumnName1=@ColumnName1) > 0)
BEGIN   
 SELECT '! Exit'
 return
 END
 ELSE
 SELECT '! Not Exit'  

Write this code then we can't face duplicate entry

Read More

The INSERT INTO statement is used to insert new records in a table.
Syntax:-
  INSERT INTO table_name (column1,column2,column3,...)
  VALUES (value1,value2,value3,...);

 The DELETE statement is used to DELETE  records in a table.
Syntax:-
  DELETE FROM table_name
  WHERE some_column=some_value; 

The UPDATE  statement is used to UPDATE  records in a table.
Syntax:-
  UPDATE table_name
  SET column1=value1,column2=value2,...
  WHERE some_column=some_value;

Read More

SQL SELECT Query
The SELECT statement is used to select data from a database.
Query
SELECT * FROM TABLE NAME.
Use this query we get All data of Table. And we Want to particular data then use Select Column Name
Example.

SELECT column_name,column_name
FROM table_name;
 

Read More