Developers Cloud

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. Tables in a database are often related to each other with keys. A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table FULL OUTER JOIN A JOIN is made matching a column on a table to a column on the other table. After a FULL OUTER JOIN, for a given value (red), for a given row with this value on one table ([ red | 9999 ]), one row is created for each row that matches on the other table ([ red | OOOOOO ] and [ red | LLLLLL ]). If a value exists in only one table, then a row is created and is completed with NULL columns. FROM table_1 FULL OUTER JOIN table_2 ON table_1 . common_value = table_2 . common_value ...

First Part :  http://www.developerscloud.org/2013/09/tips-to-increase-your-transact-sql.html 11. Use 'BETWEEN' operator instead of >= and <= operators to select data in range. 12. Wisely use the EXISTS, IN clauses in sub query select statement. - IN has the slowest performance as data is filtered between the range. - IN is efficient when most of the filter criteria is in the sub-query. - EXISTS is efficient when most of the filter criteria is in the main query. 13. Avoid 'NOT IN' in select clause. Because when we use “NOT IN” in SQL queries, the query optimizer uses 'Nested table scan' technique  to perform the activity 14. Use Stored Procedure, functions(UDF) and views instead of heavy-duty queries. - The application must first convert the binary value into a character string (which doubles its size, thus increasing network traffic and taking more time) before it can be sent to the server. And when the  server receives the charac...

Given below are little known tips that you can use to ensure your Transact-SQL queries are performing in the  most efficient manner possible. 1. Avoid '*' in select query.      Restrict the queries result set by returning only the particular columns from the table and not all the  table's columns. The sql query becomes faster if you use the actual column names in SELECT  statement instead of than '*'. 2. Avoid COUNT(*) in select statement to check the existence of records in table.       Instead use IF EXISTS() to check records. - Write the query as: IF EXISTS (SELECT * FROM table_name WHERE column_name = ‘xxx’) - Instead of : SELECT COUNT(*) FROM table_name WHERE column_name = ‘xxx’ 3. Use alternate of SELECT COUNT(*).      Use an alternative way instead of the SELECT COUNT(*) statement to count the number     of records in  table.         - SELECT CO...

Create Statement The CREATE statement is used to create a new table with no record. Let's create the table office . The records in the office table will contain a technical id, the name of the office, a description, the number of available places, the availability and the date for the next office security control: Query : CREATE TABLE office ( id_office INTEGER PRIMARY KEY NOT NULL , name VARCHAR ( 20 ) NOT NULL , description VARCHAR ( 255 ) , place_number INTEGER NOT NULL , available SMALLINT NOT NULL DEFAULT 1 , next_inspection DATE NOT NULL ) ; The table after the statement : office id_office INTEGER name VARCHAR(20) description VARCHAR(255) place_number INTEGER available SMALLINT next_inspection DATE  ALTER statement  The ALTER statement is used to modify a table. It can be used on a table with records in it.

Database YourDB has full backup YourBaackUpFile.bak. It can be restored using following two steps. Step 1:  Retrive the Logical file name of the database from backup. RESTORE  FILELISTONLY  FROM DISK =  'D:BackUpYourBaackUpFile.bak' GO Step 2:  Use the values in the LogicalName Column in following Step. Step 2.1: ----Checking if the drive exist(for ldf n mdf files) in this particular server EXEC master.dbo.xp_fileexist 'YourLDFLogicalName'  EXEC master.dbo.xp_fileexist ' YourMDFLogicalName It will give 3 columns, for file, directory and root directory. Check if it exist or not for both LDF and MDF files. Step 2.2 ---If not then change the file path using these lines --For data file SELECT TOP 1 ms . physical_name FROM master . sys . master_files ms WHERE ms . file_id = 2 ORDER BY ms . database_id DESC --For log file SELECT TOP 1 ms . physical_name FROM master . sys . master_files ms WHERE m...

Follow @harshit_parshii SQL JOIN The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. Tables in a database are often related to each other with keys. A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table FULL OUTER JOIN A JOIN is made matching a column on a table to a column on the other table. After a FULL OUTER JOIN, for a given value (red), for a given row with this value on one table ([ red | 9999 ]), one row is created for each row that matches on the other table ([ red | OOOOOO ] and [ red | LLLLLL ]). If a value exists in only one table, then a row is created and is completed with NULL columns. FROM table_1 FULL OUTER JOIN table_2 ON table_1...

Introduction A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc , sproc , StoPro , StoredProc , sp or SP ) is actually stored in the database data dictionary. Typical use for stored procedures include data validation (integrated into the database) or access control mechanisms . Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another. Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement. SQL Dialect...

Before learning SQL, relational databases have several concepts that are important to learn first. Databases store the data of an information system. We regroup data by groups of comparable data (all the employees, all the projects, all the offices...). For each group of comparable data, we create a table . This table is specially designed to suit this type of data (its attributes). For instance, a table named employee which stores all the employees would be designed like this: employee the table id_employee the primary key an integer firstname a column a string of characters a column type lastname a string of characters phone 10 numbers mail a string of characters And the company employees would be stored like this: employee id_employee firstname lastname phone mail 1 a column value Big BOSS 936854270 big.boss@company.com 2 John DOE 936854271 john.doe@company.com 3 Linus TORVALDS 936854272 linus.torvalds@co...