Unix Commands: mv (Move)

mv 

mv(move) is used to move a file.

> mv filename1 filename2 

  mv(move) command moves the file from filename1 to filename2. 

  If both the files are in the same directory then it renames the file filename1  to filename2.

> mv filename1 filename2 filename3 filename4 dirName

This command will move all the files mentioned to directory dirName

> mv *.dat dirName

This command will move all the files with an extension(.dat) to directory dirName

> mv dirName myDir

This command will move one directory to another ex: dirName to directory myDir

> mv -i filename1 filename2

mv: overwrite 'filename2'? n

This command will prompt you before overwriting the file.

> mv -b filename1 filename2

This command will create a backup of filename2 if existing.

> mv -S .bkp -b filename1 filename2

This command will create a backup of filename2 if existing with a suffix as .bkp.

Name of existing file will be then filename2.bkp.

Also a new file with filename2 will be created.

Unix Commands: cp (Copy)

cp

cp(Copy) :To copy the file contents of one file to

another

> cp filename1 filename2

This will copy content of filename1 to filename2  

Both the files should be in the same directory.

> cp dir1/filename1 dir2

  To copy the contents of filename1 of dir1 to the filename1 of dir2 directory

> cp ../filename1 filename1

  To copy content of filename1 from parent directory to current directory as filename1

> cp –r dir1 dir2

  To copy directory from one location to another.

Unix Commands: cd (Change Directory)

cd

cd (change directory): This command is used to change the directory

> cd newdir  

  To change the current directory to newdir

> “cd” / “cd ~”

  To change the current directory to home directory.

> cd -

  To change directory to previous working directory

> cd ..

  To change directory to the parent directory.

> cd ~username

  To change the current directory to username’s home directory.

> cd <pattern1> <pattern2>

  To change the current directory to a directory 

with  difference in one of the intermediate directory’s 

name

  Ex:  FROM directory  “/home/myChar/dir”

To directory  “/home/myInt/dir” 

Command

 > cd Char Int        

Unix Commands: rmdir

rmdir

rmdir (remove directory) is used to remove a directory,

> rmdir Dirname

To delete the empty directory Dirname.

This command can be used only when the directory is empty.

  

We can delete non empty directory as below:

i.e. recursively

> rm –r Dirname

Unix Commands: mkdir

mkdir

mkdir (make directory) is used to create a new directory,

  We can pass more than one parameter, unix will interpret each parameter as another directory to create.

> mkdir Dirname

  To create the directory Dirname

> mkdir -m <Permission> Dirname

  To create the directory Dirname with the given permission

  Ex: mkdir -m 777 Dirname

> mkdir Dirname1 Dirname2 Dirname3

  To create the multiple directory at one go

i.e. Dirname1, Dirname2, Dirname3

Clustered and Non Clustered Index: Benefits and Side Effects of Index

Benefits and Side Effects of Index


A table without a clustered-index is called a “heap table”.
A heap table has not its data sorted.
The SQL server has to scan the entire table in order to locate the data, in a process called a “scan”.
In the case of a clustered index, the data are sorted on the key values (columns) of the index.
The SQL server is now able to locate the data by navigating down from the root node, to the branch and finally to the leaf nodes of the B-tree structure of the index. This process is called a “seek”.
The later approach is much faster, when you want to filter or sort the data you want to retrieve.
A non-clustered index, on the other hand, is a completely different object in the table.
It contains only a subset of the columns.
It also contains a row locator looking back to the table’s rows, or to the clustered index’s key.
Because of its smaller size (subset of columns), a non-clustered index can fit more rows in an index page, therefore resulting to an improved I/O performance.
Furthermore a non-clustered index can be allocated to a different File Group, which can utilize a different physical storage in order to improve performance even more.
The side effects of indexes are related to the cost of INSERT, UPDATE, MERGE and DELETE statements.
Such statements can take longer to execute, in the presence of indexes, as it alters the data in the table, thus to the indexes too.
Imagine the situation of an INSERT statement.
It has to add new rows in a table with a clustered index.
In such case the table rows may need to be repositioned


Remember…? The clustered index needs to order the data pages themselves! This will cause overhead.

So, it is crucial to take into account the overhead of
INSERT, UPDATE and DELETE statements before designing your indexing strategy.
Although there is an overhead to the above statements, you have to take into
account that many times, an UPDATE or DELETE statement will execute in a subset of data.
This subset can be defined by a WHERE clause, where indexing may outweigh the additional cost
of index updates, because the SQL server will have to find the data before updating them.
As explained above, a non-clustered index includes the clustered index’s key as its row locator,
in the presence of a clustered index in the table.

This comes with a cost and a benefit:

The cost has to do with the non-clustered index bookmark lookup.
What if a query has to return more columns that the ones hosted in the index itself?
In the case of a HEAP table, the SQL server would have to check the RID of the non-clustered index,
in order to navigate directly to the row, where the rest of the columns belong
In the case of a clustered index, the SQL server would have to check the row locator of the non-clustered index, in order to do an additional navigation to the B-tree structure of the clustered index,
to retrieve the desired row. You see, the row locator does not contain the RID,
but the clustered-index key.
On the other hand, there is a benefit. It has to do with the clustered index updates.
Imagine the following situation: Two new rows with index key values of A2 and A3 have to be added in the clustered index below.
Because this is a clustered index page, its physical structure has to be reallocated in order to fit A2 and A3 between A1 and A4.
It has to maintain index’s order. Since there is no free space in the index page to accommodate these changes, a page split will occur. Now, there is enough space to fit A2 and A3 between A1 and A4.
The goal achieved and the order maintained within the index.
But imagine what would have happened if the non-clustered index was looking at the RID,
instead of the clustered index’s key? It would have to change its row locators to reflect
the changes. This could have been a huge performance hit!
Especially, in the case of large clustered indexes.
Instead of the RID, the row locators now point at the clustered index key.
Meaning, that there is no longer needed to change its values.
This is quite a benefit if you think of the large clustered indexes, that are usually maintained in many tables.

Clustered and Non Clustered Index: Non Clustered Index

Non-Clustered Index


Non-clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key value entry has a pointer to the data row that contains the key value.


The pointer from an index row in a non-clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.


You can add non key columns to the leaf level of the non-clustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries.

Non-clustered indexes are implemented in the following ways:

UNIQUE constraints

When you create a UNIQUE constraint, a unique non-clustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.

Index independent of a constraint


By default, a non-clustered index is created if clustered is not specified. The maximum number of non-clustered indexes that can be created per table is 999. This includes any indexes created by PRIMARY KEY or UNIQUE constraints, but does not include XML indexes.


Non-clustered index on an indexed view


After a unique clustered index has been created on a view, non-clustered indexes can be created.


Permissions : Requires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the db_ddladmin anddb_owner fixed database roles.

> How to create a non-clustered index by using the Table Designer

In Object Explorer, expand the database that contains the table on which you want to create a non-clustered index.


Expand the Tables folder.


Right-click the table on which you want to create a non-clustered index and select Design.


On the Table Designer menu, click Indexes/Keys.


In the Indexes/Keys dialog box, click Add.


Select the new index in the Selected Primary/Unique Key or Index text box.


In the grid, select Create as Clustered, and choose No from the drop-down list to the right of the property.


Click Close.


On the File menu, click Save table_name.


> How to create a non-clustered index by using Object Explorer


In Object Explorer, expand the database that contains the table on which you want to create a non-clustered index.


Expand the Tables folder.


Expand the table on which you want to create a non-clustered index.


Right-click the Indexes folder, point to New Index, and select Non-Clustered Index….


In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.


Under Index key columns, click Add….


In the Select Columns from table_name dialog box, select the check box or check boxes of the table column or columns to be added to the non-clustered index.


Click OK.


In the New Index dialog box, click OK.


> How To create a non-clustered index on a table


In Object Explorer, connect to an instance of Database Engine.


On the Standard bar, click New Query.


Use the following example into the query window and click Execute.

-----------------------------------------------------------------

USE LearnDb;

GO

-- Find and delete it if found an index already existing named IX_Employee_EmployeeId.

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'IX_Employee_EmployeeId')

DROP INDEX IX_Employee_EmployeeId ON Company.Employee;

GO

-- Create a nonclustered index called IX_Employee_EmployeeId

-- on the Company.Employee table using the DeptId column.

CREATE NONCLUSTERED INDEX IX_Employee_EmployeeId

ON Company.Employee (DepartmentID);

GO