SQL Debate or Discussion

Difference between drop and truncate in sql server

2012-02-03T02:39:00.000-08:00

Hi friends here i will explain the difference between Drop and Truncate

As we have seen in the last post about the difference between Truncate and Delete today we will seen the Difference between Drop and Truncate visually

Let's see we have the table with the following data as given below

now suppose if we perform truncate operation on it all the Records will be remove from it. as you can see it from givenn slide

Above you can see all the data will be deleted from the table but all the columns will be as it is.

Now if you use Drop Query then whole the table will be deleted
let's see the example below

so now as given above if u try select query you have the above errors because table is not exists.

so we have conclusion is that

Drop - deletes the data as well as structure
Truncate - deletes only the data, and resets the auto increment column to 0

Diffrence between Stored Procedure and User Define Function in SQL

2012-02-01T05:32:00.000-08:00

These are the main difference between Stored Procedure and User Define Function

Functions

-can be used with Select statement
-Not returning output parameter but returns Table variables
-You can join UDF
-Can not be used to change server configuration
-Can not be used with XML FOR clause
-Can not have transaction within function

Stored Procedure

-have to use EXEC or EXECUTE

-return output parameter

-can create table but won’t return Table Variables

-you can not join SP

-can be used to change server configuration

-can be used with XML FOR Clause

-can have transaction within SP

Difference between Truncate and Delete in SQL

2012-01-31T11:32:00.000-08:00

Here we will see the first practical example

Suppose we have following table named as tbl_Countries

Now we will use the delete operation on given table

if we want delete specific record in table we have to use the Delete operations
with where clause

so as you have seen in above table you can delete specific row using Delete Operations

if you want to delete all records of the table just use the Truncate operation on your table.

so from the above example you can understand the clear difference between Truncate and Delete

Difference between delete and truncate

-Delete
Can not be rolled back.
DML command.
Does not reset identity of table.
It cannot activates trigger. mention transaction log of deleted rows.

- Truncate
Can be rolled back. DDL Command reset identity of table.
Activates trigger.
Does not mention transaction log.

how to create dynamic page using ms sql procedure

2012-01-31T03:04:00.000-08:00

Hello Friends ,

This post is about how to create dynamic page using procedure in asp.net using MS sql procedure .

for that first create the simple .aspx page named as create-dynamic-page.aspx

its content would be like below image

now write down the inline code or page behind code in your page.
your code behind page that is create-dynamic-page.aspx.vb

Let's create one procedure for dynamic page that is very simple

-- exec prc_GetDynamicPageContent
-- drop proc prc_GetDynamicPageContent
create proc [dbo].[prc_GetDynamicPageContent]
as
begin
select '<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="create-dynamic-page.aspx.vb" Inherits="default.create_dynamic_page" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

</div>
</form>
</body>
</html>'

end

HERE using above procedure it will create dynamic page each time in your code and will use
create-dynamic-page.aspx.vb as code behind so wite down your code in that file.

Procedure or function expects parameter ' which is not supplied

2012-01-30T21:27:00.000-08:00

This post discuss in details at DataHaunting

Hi many of us facing this type of problem when wroking SQL Server .

let me illustrate the example if you are working with the SQL Server

in the given example if you execute the procedure it will show the error as given below because here
we need the pass the parameter as '@name'

now if you are passing the parameter as 'dilip0165' your query will be execute successfully .

if you have need number of arguments to insert into table then you have to pass the number of
parameter as here we have passed .

How to find table which used in many procedures in DataBase using Procedure

2012-01-24T22:57:00.000-08:00

This post discuss in details at DataHaunting

We Many times want to find the table which used in various procedure
Here I have Created simple procedure to find in which Procedure it is used

CREATE PROCEDURE [dbo].[prc_Search]
@StringToSearch varchar(100)
AS
SET @StringToSearch = '%' +@StringToSearch + '%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name

create above procedure in your Database

Now when you want find your table is used in which procedure simply Execute
   prc_Search 'tbl_AdminUsers'
as given below slide you can see the number of procedure in which your procedure is used

How To Create Table in SQL Server

2012-01-24T21:28:00.000-08:00

There are various way we can create table in SQL Server But Here I will Discuss the convenient way to make the Data Table on SQL Server .

Here I will make the Table On SQL Server 2008 with snapshot so which is easy to understand.

As we have seen in last post that how to create Database using Server Explorer .
http://sqldebate.blogspot.com/2012/01/how-to-connect-sql-server-with-visual.html

Today we will see how to create the Table with SQL various functionality .

in the give example we have created New Table Named as "db_test"

Now to create the table right click ont the table you will see "New Table" option click on it.

Here I have created table without any Primary Key but very useful way to create the table.

Let's see the Example

Here we can see the I have Created simple Table with five field .
let's see the advantage of this type of Data Table

First I have Created Column "id" with data type integer here i have unchecked the allow nulls.
Now look at the column property in that focus on "Identity Specification" in that double click on is identity you will see column property as given below exmaples.

Here it will show the auto increment when add the column here "Identity Increment " specify the increment in number and "Identity Seed" specify that from where to start count for the auto increment .

so by using this simple property you can have new distinct "ID" number each time when you have create new rows . so no conflict can be generated and your table will work smoothly

SQL SELECT QUERY

2012-01-24T06:02:00.001-08:00

Sql have Several Query for interect with Database.

Select Query is one of the most important Query here we will see the different Uses of Select Query.

* To simply select the all Datas from Database Table

select * from table_name

above statements select all the rows from Data Table.

* To count just number of rows(all) from table

select count(*) from table_name

above query count all the rows from given table

* now if you want count distinct rows from table you have to specify distinct word before count key word
like

select count( distinct column_name) from table_name

above query remove duplication from count result .

* Use select method to insert data from one table to another table

insert into tbl_insert_table (first_column , last_column)
select first_value , last_value from tbl_select_table

How to connect SQL Server using Server Explorer

2012-01-23T22:33:00.000-08:00

Here we will see to connect SQL server using Server Explorer

First of all when you have created any project suppose named as "DEMO"

Now as per given below click
-> click on View -> Server Explorer

you can create the database right from within Visual Studio 2005. First, if Server Explorer is not visible select View, Server Explorer from the menu as . Now right Click on Data Connections, and pick “Add Connection…”.

Note do NOT pick the Create New SQL Server Database… option, this works with full blown SQL Server such as SQL Server Express.

Here on the next screen you can see that you have to choose Data Source ..
choose the "Microsoft SQL Server" click continue

now after choosing the Data Source

on the next screen as you can see below here Data Source which you have entered by using change button you can select othe Data Source .

Now In the Server Name Dropdown box select your server name and as you can see the next panel that is "Log on to Server" there are given two modes windows authentication and SQl Server Authentication .

if you want use or create Database in your machine locally you have to select windows authentication and in the case you want connect remote server select the SQL Server Authentication here you have to specify the remote server User Name or Password .

next you have to specify the Your Database Name here our database name "Demo"

Returning to the Server Explorer, your new database (which I named Demo) should be listed in your Data Connections tree. Click the + button to expand, and you can see the Tables, System Views (not to be confused with a traditional database view), and a Replication folder.

Note that if you wanted to open an existing database, you’d simply skip the steps associated with the Create button. Instead you’d simply pick “Add Connection…”, key in (or browse for) the database name, enter the password and click Open.

How to Connect Sql Server using web.config file

2012-01-23T11:19:00.001-08:00

Hi Everyone who newly to MS SQL Server. Here I have described Various Mathods to connect with SQL server to use it various Functionality.

1) Using Web.Config File in your Visual Studio

As you create your project in Visual Studio you will find web.config file It is a good practice to store the connection string for your application in a config file rather than as a hard coded string in your code. The way to do this differs between .NET 2.0 and .NET 3.5 (and above). This article cover booth.

Connection string in .NET 2.0 config file

In the appSettings location, add a key named whatever you like to reference your connection string to.

<appSettings>
<add key="myConnectionString" value="server=localhost;database=myDb;uid=myUser;password=myPass;" />
</appSettings>

To read the connection string from code, use the ConfigurationSettings class.

string connStr = ConfigurationSettings.AppSettings("myConnectionString");

Now you have the connection string loaded from web.config into your string variable in code.

Connection string in .NET 3.5 (and above) config file

Do not use appsettings in web.config. Instead use the connectionStrings section in web.config.

<connectionStrings>
<add name="myConnectionString" connectionString="server=localhost;database=myDb;uid=myUser;password=myPass;" />
</connectionStrings>

To read the connection string into your code, use the ConfigurationSettings class.

string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;

Sql Introduction

2012-01-23T02:09:00.000-08:00

Hi ! Wel Come to Sql Discussion Blog.

This is Discussion or Debate Blog on SQL for spreading knowledge for basic fundamentals of SQL Database.
Here Everyone can share their knowledge on Sql by discussion forum .

SQL is a standard language for accessing and manipulating databases which stands for Structured Query Language.

SQL is a Common Language for variety Databases for accessing the Data from DataBase.

The Heart of any Database is the RDBMS.

RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects called tables.
A table is a collection of related data entries and it consists of columns and rows.