TeraData in sql server -2008

 

In SQL Server -2008 only

Delete  from TT Where T.T.%% PHYSLOC%%

Not in(Select min(%%PHYSLOC%%)

from T.T group by EID)  

Note: PHYSLOC: Physical location of row[Just like row id]  

Tera data

• Here we must use in the intermediate table to delete the duplicate, Because row id, row number does not work in tera data
• We cannot we order analytical function such as Rank and Qualify Row- number & Qualify to delete the duplicate

Steps

• Take duplicate into an intermediate table(intermediate table should have a similar Structure to the main table)
• Delete the main table.
• Rename the intermediate table to the main table.

So tera data The below 2 ways are recommended

• Case of complete row duplicate
• Take a new table with the set option
• load the duplicate table data, so unique ready loaded
• Drop duplicate table and rename set table name to old table name
• In case of the same column are duplicate

1. Take a new table
2. Load the distinct resource group by into it so unique rows loaded
3. Drop duplicate table and rename new table name to old table name

1.Create B like A

2.Load duplicate of “A” TO “B”

3.Drop ”A”

4.Rename “B” TO “A”

Note: If we want to complete row duplications, then take a set table 

Inclined to build a profession as SQL Server Developer? Then here is the blog post on, explore SQL Server Training

Creating sequence Tera data

We use the identify column to generate the sequence in tera data and so the server create set table test (party ID Integer Generated always as identify Start with 1 Increment by1 Min value 2147483647 Max value 2147483647 No cycle), party name varchar (30)) Primary index(party id);  

For testing:

Insert into test2(‘Vinay’)

Generate always As identify, it will not in insert user given input value

Generated by default as identity, it takes user given input value also. Create set table test 11 (party id integer generated by default as identify(Max value3), Party name varchar(30)) Primary index(party id);  

Conditional expressions

Generally, this conditional expression only where and having clauses (In, not in, BETWEEN AND, Not BETWEEN AND, IS NULL, IS NOT NULL, EXISTS, NOT EXISTS, LIKE, ALL, SAMS, ANY, etc---)

Among in and exists which gives good performance

Ans: Always use exists, because it gives a good performance (once finds the match it stores the process)

Display All the name where % is the 2^nd character

Ans: Select * From party where party name like –z %% ESCAPE ‘Z’

Select * From party where party name like –z-%

Select * From party where party names like%

in Select * From party where party id in (1,2,3) (or) Sel * From party where party id = 1 or party id = 2 or party id =3

Exist not Exists: To the values existed it display the result  

Working with multiple tables

Rows and columns

1. Sub Queries:

• Its operation is Query wise based on one query result it Fetches the other Query

2. Set operations:

• It performs operations on row-wise

3. Joint

•  It performs operations on column-wise

4. Views

• It is a logical object for the physical collection of takes generally for retrieval perspective

5. Procedure

• it is a pre-compile collection of SQL Statements, which performs as a particular process it can pass multiple values outside

6. Function

•  it is also for implementing a particular process, but it returns as a single value as a result

7.  Trigger

•  it performs an action when there is an event that occurred

8.  Cursers

•  it is a stored memory area where the navigation and operations of rows if possible intake area

9. Utilities

• it performs Loading and unloading data

10. Macros

• Collection of statements for implementing a simple process, Generally Recommended frequently used processes and queries.

Sub-Queries

Query inserts a query subquery, in this case, one query is outer Query, the other is inner Query

   

Always we refer to inner Queries by using conditional expressions, like, in, Exists, not in, not Exists, etc….. Operators.  

Types of sub-Queries

• Simple sub- Query
• Nested sub- Query
•  Correlated sub- Query

1.Simple sub- Query

Query insert another Query is called simple sub – Query  

Ex: Display employee details, if the dept id available in department  table Select* From emp where exists (select dept no from dept): Select* From emp where dept id In (select dept id from dept): Select* From emp where exists (select dept no from dept where emp. dept no= dept.dept no):  

2.Nested sub- Query

Sub- Query within another sub- Query is called Nested sub- Query

Ex: Display dept details where Dept ids available in employee table where empid available in the employee address table and those address are valid address in party address table Select* From dept where deptid in (Select dept id from emp where empid in) (Select dept id from emp _address where emp address  in) (Select address from party _Address) ) )

Simple sub Query	Correlated sub Query
1.Inner Query executed First	1.Out Query executed First
2.Inner Query executed only one time Ex: Finding 2nd Maximum   Select max(Party income)From party where party income not in(Select mad(Party income)from the party)	2.Inner Query executed many times (According to Query) [Based on outer Query (for loop)] Ex: Finding 2nd maximum select a party income from the party a were 1st (Select count(Distinct(b: party income))from party b where a. party income(b. party income)

Note: For with max salary need to place n-1 in place of 1 Collated

A30000	PI<B.PI <(F) <(F) <(F)	COUT0	B30000 20000 25000
20000	<T<F <T	2	3000020000 25000
250000	<T<F <T	1	3000020000 25000

Note:

1. We need to avoid the correlate sub-Query sometime in Real-time Because it may decrease the performance of the Query by occupying more spool memory and operating many rows.
2. To identify with max salary Top 10 Salary we can use Rank Qualify, Row- Number Quality (or)Top N Functions

3. Sub Query return always single table columns only (counter QUERY COLUMN)

Set operations

• It performs operations Row-wise between multiple result sets
• It is completely set theory based
• It uses where a set operator for its process
• Union
• Union All(v)
• Intersects(n)
• Except(-) 

Syntax: Select column/* From Table A union/union All/Intersect/except Select column/* From Table B  

Limitations

The number of columns and order of the column data type should be the same in both the data sets it doesn’t bother about the column name.

Union 

it merges the rows from to data sets including duplicate

Union all 

merges the rows from to data sets  including duplicate

Intersects  

Take common rows from the path the data set

Except 

Take exclusive rows from the 1^st dataset[Not including matching or other data set row]

Note: Union doesn’t allow duplicate value union all allows duplicate value Sel EID, EName From emp Union ok union All or intersect or except Sel empid, emp name From emp-Address    

Ex: Using Group by clause We have to take the group by clause individual Query wise rather than entire set operations wise Sel Eid, ename from emp group by eid, ename UNION Sel empid, exlame From emp- Address group by empid; emp name  

Multiple set operation

Sel EID,E Name From emp UNION Sel empid, emp name From emp-Address Intersects Sel EID, Ename from emp10 Except Sal eid, e name from emp

Get In-Depth Knowledge on SQL Server Click on Here:

• Transfer SQL server Objects Task
• Performing Data Manipulations using Disconnected Model
• TeraData in sql server -2008
• SQL Server Integration