We can think of common table expression as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It lasts only for the duration of the query and can be referenced multiple times in the same query.
What is linked server?
Linked Servers allows us to connect to other database instances on the same server or on another machine or remote servers. It allows SQL Server to execute SQL scripts against OLE DB data sources on remote servers using OLE DB providers.
After setting up the Linked Servers we can easily access the other server tables, procedures etc.
Once the connection is established, we can start with the CRUD operations. The advantage is about security; its works on Windows as well as SQL Server Authentications.
In Sql Server we refer as linked servers and in Oracle we refer them as database links.
What is stored procedure
A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in database as a group, so it can be used and shared by multiple programs any number of times.
What is a “Table”, “Record”, “Field” ?
A table is a collection of records of specific type.
Ex:Employee Table, Salary Table.
A record is a collection of values/specific entity.
Ex:Employee, Salary.
A field is an area within a record for a specific piece of data.
Ex:Employee Name, Employee Id.
What is a foreign key?
When a ‘one’ table’s primary key field is added to a related “many” table in order to create a common field which relates the two tables , it is called foreign key in the “many” table.
What is a primary key?
A primary key is a column whose values uniquely identify every row in a table. It should be unique and not null.
How to reset column identity in SQL server?
Sometimes we delete rows from a table and the identity will not start as expected from one, it would assign a random value. In order to reset the value we have to use the following command.
DBCC CHECKIDENT ('[TableName]', RESEED, 0);
GOUsing Column Names “FROM” and “TO” in a table and trying to lnsert data into table
When in a database if you have a table with column names “FROM” and “TO” you should be careful while trying to reference these column names in your sql command.
You should enclose these column names in square brackets so that SQL will identify them as column names and our command works as we expected without any errors.
You should enclose as shown here:
Select CompanyName,[From],[To],Skillset from WorkExperience