Creating Database WithOut GUI in asp.net

Title:Create new data base using dot net framework 

1.With in object explorer right click on the database note and choose new database to open new database dialog box
2.In that new database dialog box at the database name option provide a name to the database and keep the owner as the default
3.By default one data filer and one log file are created automatically and will be shown in as table when you ant to create additional files and log files.
4.Click on add button at the bottom of the table.The table provides the first column name that can be used to specify logical name of the file .
5.File type column to specify whether the data file belongs to primary or secondary file group and the initial size column to specify initial memory to be allocated to the file
6.Use the auto growth column to specify how much memory to be incremented to the file.When it names additional memory and the maximum limit for the file.
7.To change these option click on the button on right side of the column .Auto growth that open a dialog box with in they dialog box specify the file growth and max size and click on OK button
8.Use  the column path to specify the physical path where you want to save the file to change the path ,click on the button on right side of the column path
9.After specifying the all the files required for the data base click o OK button to complete the Creation of database and click the dialog box

How to get the information of database in sqlserver

The sql server provides a procedure to get the information of desired data base .i;e SP_HELPDB.
In the below picture we can see the data and log information of the database.Here i have used query to get the data of existing database.

sp_helpdb 'Test krishna'

The result will show the size,administrator  and  status of the DB
 

how to create structure of database in sqlserver

Creating other database structure using existing data base using sqlserver is pretty simple compare to other.As you can see the below image ,the existing data base is appeared in sql server.In this example the existing  DB has 15 tables .Now i will place the same structure in to other DB.

To create structure we need to create a script for existing database.For this we have to select the generate script option.Then we can create script as in below

 

 To create a nee DB with this query we need to update the database name with desired DB name.Here i have changed to TestDB1.
                                      

 

Here you can observe the structure of the new database (TestDB1) which is complely similar to the structure of  existing one

Query to get the rank of student in a class in MYSQL

Based on my requiremnet i need to get the rank of student in million records in SQL database.Initially i just fetch the students records in decending order .Then put it an array in my code and got the result.This method will reduce the application performance.For this i have done complete data filteration in database using below query

SELECT sub_q.* , @rn := @rn + 1 'Rank_id'
FROM
(SELECT * FROM `student` ORDER BY total_marks DESC,DATE DESC) sub_q,(SELECT @rn := 0) r;

In the above query
1.Initialise the variable rn=0
2.The sub query given data of students records in sort order.
3.Here the rn will be incremneted based on the records count(@rn := @rn + 1).

Initial Data:

Resultant Data:

Difference between sqlserver 2008 and sqlserver 2012

Title: Difference between SQL Server 2008 and SQL server 2012

In previous articles i explained sqlserver 2008 concepts like Delete duplicate rows/columns from table,
Sql server 2012:
1.The SQL Server 2012 uses 48 bit precision for spatial
2.In SQL server 2012 has unlimited concurrent connections
3.By default it supports 15,000 partitions
4.Available new string functions CONCATE and FORMAT
5.Available new conversion  functions are PARSE ,TRY_CONVERT,TRY_PARSE
6.Microsoft.SqlServer.Dac and Microsoft.SqlServer.Dac.Extensions are introduced to perform  operations on Packages

Sql server 2008:
1.Maximum number concurrent connections to SQL Server 2008 is 32767
2.The SQL Server 2008 uses 27 bit precision for spatial
3.It can support only 1000 partitions
4.The CONCATE and FORMAT not available
5.Not available conversion function which are mentioned in SQL 2012

How to connecting to SQLserver from asp.net application

Title: How to create connection string in asp.net using C#.net

Description:
If you want to connect with SQLServer from asp.net application ,you can either use the OLEDB class as well as SQL Client classes.While connecting with OLEDB Connection to mention the target data source we specify the name of the provider which has be to be used.But when you use SQL connection or Oracle connection classes the connection string does not require the provider name to be specified.Here i will show an example to get the data from database using connection object

Example

Using system.Data.SqlClient
SqlConnection cn;
SqlCOmmand cmd;
SqlDataReader dr;
under page load
cn=New SqlConnection("user id=sa;password=123;Database=asp.net");
cmd=New SqlCommand("select*from students",cn);
cn.open();
dr=cmd.ExcuteReader();
if(dr.Read())
{
Response.Write(dr[0].Tostring());
}

How to delete Duplicate Rows from Table

Title:How to delete duplicate rows in table using SQL Server

Description:
While working with we applications ,we have to maintain the data base is unique.Now i will describe one example on data redundancy.We just take one registration site ,which is used to register the personal details .When the user give the details multiple times ,we have to check whether the data is exist or not.If the records are exit we can delete using below query.
Note:The Duplication rows in Database tables will exists by running the data repeatedly With out  having the primary key on table.Here i have shown an example to remove the duplicate records in table

Example:

DELETE FROM Employee e1 WHERE ROW_NUMBER()<>(SELECT MIN( ROW_NUMBER())FROM EMployee e2 WHERE e1.empname= e2.empname)