SQL Tables,Temporary Tables,Table Variables
Dec01

SQL Tables,Temporary Tables,Table Variables

Welcome to CodeSpread! SQL provides a variety of Tables. Lets summarize each one of them. Permanent Tables: Why do we require it? It is required for data persistence across the database. Once tables are created and filled with the data, this data is available for all the stored procedures and any DML statements can be run on them. Example: Create a Student table having id,name as attributes. Create table Student (Id int, Name varchar(50)) Here Table Student will have two columns, Id and Name. Id will accept only integers and Name will accept  maximum 50 characters. Any Table created will have row and columns. Please refer the image below for visual representation. Student Id | Name Temporary Tables: This tables have a limited time duration and they are required to be dropped once they have served their purpose of existence. They are used to store data while defining a stored procedure and data is required to be persist beyond the procedure. We have two type of temporary tables Local Temporary Tables. (#table_name) Global Temporary Tables.(##table_name) Their name only describe their existence period and their usefulness. The symbols mentioned above is how they are described individually. Local Temporary Tables:  These tables are created within a procedure and stored in the temp_db provided by SQL server. They can be identified with a session specific identifier. It can be used when data is coming from another stored procedure. It also reduces the amount of locking required and also involves less logging. Still, there are few limitations such as character limit is 116 and transactions can create unnecessary locks in temp_db. Syntax: Create table #Student (Id int, Name varchar(50)) Global Temporary Tables: These tables are same as local temporary table but the difference lies in the lifetime as it is available for all the sessions. This can be useful when the same set of data is required by one or more users. But the issue will come when the user, who should not be given access to this data, will have access to it as it is a global table and available for all users. Syntax: Create table ##Student (Id int, Name varchar(50)) See the difference of two #. Table Variables: Same structure as a normal table but only difference is the shortest life time among all the varieties. This table is created and stored in memory and its lifetime is decided by the stored procedure who have created it. Once stored procedure/DML statement exits, this table gets auto cleaned and memory gets free. Apart from that, log activity is truncated immediately. An important note, If we have a requirement to use a table structure in user defined function then we have only one option...

Read More

How to create database with SQL express bundled with Visual Studio?

Yes, Visual studio comes bundled with a copy of SQL express edition which gets installed during visual studio installation. These steps are required to create database with SQL express bundled with Visual Studio. So guys, I request you all to please perform these steps while reading this article. View Server Explorer Add a Server Server Details Create Database Database details Choose Add Connection Add Connection Connection Properties Click on at least one image to start the slideshow Refer Slide 1,To view the SQL express,Click on ‘View’ -> ‘Server Explorer’. Refer Slide 2, Right Click on ‘Servers’ and select ‘Add a Server’. Refer Slide 3, Write Server Name in the Dialog, In this case, you have to write your machine name. Refer Slide 4,To create a database,Right Click on Data Connections and select “Create SQL server database”. Refer Slide 5, In the dialog box, select  server name from the list, select the authentication you want to use and provide a new database name. Refer Slide 6, To create a connection,Right Click on Data Connections and select “Add Connection”. Refer Slide 7,In “Choose Data Source” dialog box, select “Microsoft SQL Server”, data provider will get filled simultaneously.Click continue. Refer Slide 8, Fill Connection properties in the dialog box, select server name from the list. Once server is selected, “Connect to database” section gets activated,  select database and click “Test Connection”. Comments...

Read More