SQL with Manoj

Watch & Subscribe my SQL videos on YouTube | Join me on Facebook

SQL Basics – Backup and Restore Database in SQL Server

June 5, 2010 1 comment

–> SQL Script to take Backup of a database:

USE [master]
GO

BACKUP DATABASE [AdventureWorks2014]
	TO  DISK = N'D:\SQL\AdventureWorks2014.bak' WITH NOFORMAT, 
	NOINIT,  
	NAME = N'AdventureWorks2014-Full Database Backup', 
	SKIP, 
	NOREWIND, 
	NOUNLOAD,  
	STATS = 10
GO

–> SQL Script to Restore a Backup file:

USE [master]
GO

RESTORE DATABASE [TestManDB2] FROM  DISK = N'D:\SQL\TestManDB.bak' WITH FILE = 1,  
	MOVE N'TestManDB' TO N'D:\MSSQL\DATA\TestManDB2.mdf',  
	MOVE N'TestManDB_log' TO N'D:\MSSQL\DATA\TestManDB2_log.ldf',  
	NOUNLOAD,  
	STATS = 5
GO

–> Video on how to backup and restore a database:

SQL basics – Temporary Tables vs Table Variables

May 15, 2010 9 comments

I see lot of people debating on usage of Temporary Tables & Table Variables. And everyone cites their own definition and examples, and most of them conflicts with each other. So I thought to put the differences & points that are valid and tested:

–> Temporary Tables:

1. Syntax: CREATE TABLE #T (..)

2. A Temporary Table or Temp-Table is created on disk in the tempDB system database. The name of this Temp-Table is suffixed with a session-specific ID so that it can be differentiated with other similar named tables created in other sessions. The name is limited to 116 chars.

3. The Scope of this Temp-Table is limited to its session, like a Stored Procedure, or a set of nested Stored Procedures.

4. The Temp-Table gets Dropped automatically when the session ends or the Stored Procedure execution ends or goes out of scope.

5. One of the main benefits of using a #temp table, as opposed to a permanent table, is the reduction in the amount of locking required (since the current user is the only user accessing the table), and also there is much less logging involved.

6. Global Temporary Tables (##) operate much like Local Temporary Tables; they are also created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all the sessions, until the creating session goes out of scope.

7. One can create desired Indexes on Temporary Tables (like permanent tables) and these make use of Statistics, thus resulting in better query plan compared to Table variables.

–> Table Variables:

1. Syntax: DECLARE @T TABLE (…)

2. A Table Variable is also created on disk in the tempDB system database. But the name of this Table Variable is generated completely by the SQL engine and it also differs with other similar named tables created in same or other sessions.

3. The Scope of Table Variables is limited to its BATCH only like other variables. Contrary to the temporary tables, they are not visible in nested stored procedures and in EXEC(@SQLstring) statements.

4. The Table Variable gets Dropped automatically when the BATCH ends (after the GO batch separator) or the Stored Procedure or Function execution ends or goes out of scope.

5. A Table Variable is created in memory, this is a myth. They are also treated as Temp-Tables and created in tempdb, but they performs slightly better than Temp-Tables because there is even less locking and logging in a Table Variable.

6. Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a UDF (User Defined Function). You can create a Table Variable within a UDF, and modify the data using one of the DML statements, this is not possible with Temp-Tables.

7. A Table Variable will always have a cardinality of 1, thus statistics are not tracked for them and may result in bad query plan.

–> Limitations with Table variables:

8. Table Variables do not participate in TRANSACTIONS and locking.

9. You cannot use a Table Variable in either of the following situations:
a. INSERT @table EXEC spSomeProcedure (Starting in SQL Server 2005, this limitation was removed and table variables can now be used as the destination for INSERT EXEC commands.)
b. SELECT * INTO @table FROM someTable

10. You cannot Truncate a Table Variable.

11. Table Variables cannot be Altered after they have been declared.

12. You cannot explicitly add an index to a Table Variable, however you can create an inline index through a PRIMARY KEY CONSTRAINT, and multiple indexes via UNIQUE CONSTRAINTs.

13. You cannot create a named Constraint on Table Variables. You cannot use a user-defined function (UDF) in a CHECK CONSTRAINT, computed column or DEFAULT CONSTRAINT.

14. You cannot use a user-defined type (UDT) in a column definition.

15. Unlike a #temp table, you cannot DROP a Table Variable when it is no longer necessary, you just need to let it go out of scope.

16. You can’t build the Table Variable inside Dynamic SQL. This is because the rest of the script knows nothing about the temporary objects created within the dynamic SQL. Like other local variables, table variables declared inside of a dynamic SQL block (EXEC or sp_executeSQL) cannot be referenced from outside, and vice-versa. So you would have to write the whole set of statements to create and operate on the table variable, and perform it with a single call to EXEC or sp_executeSQL.

17. Table variables are not visible to the calling procedure in the case of nested Stored Procs. It is possible with temp tables.

18. You cannot insert explicit values into an IDENTITY column of a Table variable (the Table Variables does not support the SET IDENTITY_INSERT ON).

–> Now the question is when to use either of them?

– Temporary Tables: When you are dealing with large volume of data sets use Temp-Tables, as you can create Indexes on them and they use Statistics for accurate cardinality estimations, thus providing a better query plan.

– Table Variables: When you are dealing with smaller data sets, use Table Variables, as they would not acquire locks and are Transaction free, and may not be affected by the absence of Indexes and Stats.

–> Check the full demo here:

Categories: Differences, tempdb Tags: Global Temp, Table Variables, Temporary Tables

Basic UNIX/Linux commands for Interview Questions – Part 2

April 15, 2010 1 comment

Some basic UNIX/Linux commands (Part-2): [Part-1]
… from college notes !

Q36: Display record length of the 1st line of file emp.1st.

Ans: [student@localhost student]$ head -1 emp1.1st |wc –c
41

Q37: Display record length of the 1st line of file emp.1st & store result in a variable.

Ans: [student@localhost student]$ p=`head -1 emp1.1st|wc -c`
[student@localhost student]$ echo “$p”
41

Q38: Display line no 5 & 7 from emp2.1st

Q39: Get the year of joining from file emp2.1st of all the emp.

Ans: [student@localhost student]$ cut –d “|” -f 5.7, 5.8 emp2.1st
50
47
58
62
50
55

Q40: Create a file shortlist by starting 1st 5 lines of emp1.1st.

Q41: Extract field numbers 2 & 3 from shortlist save as cut1.

Q42: Extract fields 1,4,5 from shortlist save as cut2.

Ans: [student@localhost student]$ cut -d”|” -f 1,4,5 shortlist >cut2
[student@localhost student]$ cat cut2
9876|production|12/03/50
2365|personnel|11/05/47
5423|admin|30/08/56
6213|accounts|05/06/62/6300\
4290|production|07/09/50

Q43: Put all fields of cut1 & cut2 together.

Q44: Sort on 2nd field of shortlist in two ways.

Q45: Create a sorted output file named sortlist.

Q46: Sort on 3rd field & then by 2nd field of shortlist.

Q47: Sort according to year of birth in shortlist.

Q48: Create a file dept.1st.

Ans: [student@localhost student]$ cat >dept.1st

Q49: Convert ‘|’ to ‘~’ in emp1.1st.

Ans: [student@localhost student]$ tr ‘|’ ‘~’ <emp1.1st
2233~a.k. shukla~g.m~sales~12/52/52~6000
5678~sumit chakrabarty~d.g.m ~marketing~19/04/43~6000
1006~chanchal singhvi ~ director~sales~03/09/38~6700
1265~s.n dasgupta~manager~sales~12/09/63~5600
2476~anil aggarwal~manager~sales~01/05/59~5000
6521~lalit chowdary~director~marketing~26/09/45~8200
2345~j.b.saxena~g.m~marketing~12/03/45~8000
0110~v.k.agarwal~g.m~marketing~31/12/40~9000

Q50: Display frequency of occurance of all the lines.

Ans: [student@localhost student]$ uniq –c

Q51: Display lines beginning with 2.

Ans: [student@localhost student]$ grep "^2" emp1.1st
2233|a.k. shukla|g.m|sales|12/52/52|6000
2476|anil aggarwal|manager|sales|01/05/59|5000
2345|j.b.saxena|g.m|marketing|12/03/45|8000

Q52: Display lines where salary lie between 7000 & 7999.

Ans: [student@localhost student]$ grep “7…$” emp1.1st
6521|lalit chowdury |director |marketing|26/09/45|8200
2345|j.b.saxena |g.m. |marketing|12/03/45|8000
0110|v.k.agarwal |g.m. |marketing|31/12/40|9000

Q53: Remove consecutive occurrences of spaces in emp1.1st.

Ans: [student@localhost student]$ tr –s ‘ ‘ <emp.1st
2233|a.k.shukla|g.m.|sales|12/12/52|6000
5678|sumit chakrobarty|d.g.m|marketing|19/04/43|6000
1006|chanchal singhvi|director|sales|03/09/38|6700
1265|s.n.dasgupta|manager|sales|12/09/68|5600
2476|anil aggarwal|manager|sales|01/05/59|5000
6521|lalit chowdury|director|marketing|26/09/45|8200
2345|j.b.saxena|g.m.|marketing|12/03/45|8000
0110|v.k.agarwal|g.m.|marketing|31/12/40|9000

Q54: Convert the contents of emp1.1st to uppercase.

Ans: [student@localhost student]$ sort –f emp1.1st
2233|A.K.SHUKLA |G.M. |SALES |12/12/52|6000
5678|SUMIT CHAKROBARTY |D.G.M |MARKETING|19/04/43|6000
1006|CHANCHAL SINGHVI |DIRECTOR |SALES |03/09/38|6700
1265|S.N.DASGUPTA |MANAGER |SALES |12/09/68|5600
2476|ANIL AGGARWAL |MANAGER |SALES |01/05/59|5000
6521|LALIT CHOWDURY |DIRECTO |MARKETING|26/09/45|8200
2345|J.B.SAXENA |G.M. |MARKETING|12/03/45|8000
0110|V.K.AGARWAL |G.M. |MARKETING|31/12/40|9000

Q55: Display lines containing jai sharma with a variable.

Ans: [student@localhost student]$ a=grep ‘jai sharma’ emp.1st
0

Q56: Display lines containing jai sharma without a variable.

Ans: [student@localhost student]$ grep ‘jai sharma’ emp.1st

Q57: Display lines containing ‘Agarwal’ , ‘agarwal’,& ‘aggarwal’ from emp.1st.

Ans: [student@localhost student]$ grep –e “Agarwal” -e “agarwal” –e
“aggarwal”
2476|anil aggarwal |manager |sales |01/05/59|5000
0110|v.k.agarwal |g.m. |marketing|31/12/40|9000

Q58: Display one copy of redundant records of file dept.1st.

Ans: [student@localhost student]$ uniq –d dept.1st
01|accounts |6213
02|admin |5423
03|marketing |6521
04|personnel |2365
05|production|9876
06|sales |1006

Categories: Interview Q, Linux Tags: Interview Questions, Linux Commands, UNIX Commands, Unix Interview Questions

Basic UNIX/Linux commands for Interview Questions – Part 1

April 11, 2010 1 comment

Some basic UNIX/Linux commands:
… from College notes !

Q1. Start UNIX/Linux while logging in remotely to telnet

Ans: [student@localhost student]$ telnet 192.168.0.4

Q2. Enter the user name as student and password as student.

Ans: [student@localhost student]$

Q3. Display all files starting with a dot and filename more than three characters.

Ans: [student@localhost student]$ ls .???*

Q4. Create files chap01,chap02,chap05,chap07,chap*,chap[0-3]

Ans: [student@localhost student]$ cat > chap01
[student@localhost student]$ cat > chap02
[student@localhost student]$ cat > chap05
[student@localhost student]$ cat > chap07
[student@localhost student]$ cat > chap0*

Q5. Display all files starting with an alphabet irrespective of the case.

Ans: [student@localhost student]$ ls [a-Za-z]*

Q6. Try the command pwd to see the present working directory

Ans: [student@localhost student]$ /home/student

Q7. Create files namely abc.txt ,aby.txt ,xdf ,x02,x04,ab4.

Ans: [student@localhost student]$ cat > abc.txt
[student@localhost student]$ cat > aby.txt
[student@localhost student]$ cat > xdf
[student@localhost student]$ cat > x02
[student@localhost student]$ cat > x04
[student@localhost student]$ cat > ab4

Q8. Display the files starting with a or t and second character b or x and length of the files should be only 3 characters.

Ans: [student@localhost student]$ ls [a,t][b,x]?

Q9. Create directories namely dir1,dir2,dir3

Ans: [student@localhost student]$ mkdir dir1,dir2,dir3.

Q10. Create three files in each of the directory.

Ans: [student@localhost student]$ cat > /home/student/dir1/file01
[student@localhost student]$ cat > /home/student/dir2/file02
[student@localhost student]$ cat > /home/student/dir3/file03

Q11. Copy all the files from these directories to the current directory.

Ans: [student@localhost student]$

Q12. Copy recursively the three directories include , bin, lib from / to directory.

Ans: [student@localhost student]$ cp –R { nitin1,nitin2,nitin3 }
/home/student/kap

Q13. Remove the file chap*

Ans: [student@localhost student]$ rm chap*

Q14. Display the file contents of file chap[0-3].

Ans: [student@localhost student]$ cat chap[0-3]

Q15. Create a file hello.

Ans: [student@localhost student]$ cat > hello

Q16. Create a hard link of the file hello name it as hai.

Ans: [student@localhost student]$ ln hello hai

Q17. Display the listing of both hello and hai files along either their i-node number.

Ans. [student@localhost student]$ ls –i hello hai

Q18. Create the symbolic link of file hello as hellohai

Ans. [student@localhost student]$ ln –s hello hai

Q19. Display the listing of both hello and hellohai files along with their i-node number.

Ans. [student@localhost student]$ ls –i hello hellohai

Q20. Display the process status of all the processes running on linux.

Ans. [student@localhost student]$ ps

Q21. Display the hidden files and also marked them as executable and directory.

Ans. [student@localhost student]$ ls –Fa

Q22. Display all the directories, sub- directories and files.

Ans. [student@localhost student]$ls -R

Q23. Create a file name in file at 3 to 4 line of text to it.

Ans. [student@localhost student]$cat >infile

Q24. Count number of lines, words and characters in the filename infile.

Ans. [student@localhost student]$wc –lwm infile

Q25. Count number of words in infile with and without redirection.

Ans. [student@localhost student]$wc –w<infile

Q26. In a single command except data as well as display contents of the file infile.

Ans. [student@localhost student]$cat infile –

Q27. Display all the files starting with an alphabetic irrespective of the case.

Ans. [student@localhost student]$ls [a-zA-Z]*

Q28. Display the number of users logged on.

Ans. [student@localhost student]$who | wc-l

Q29. Display the number of files present in the current directory.

Ans. [student@localhost student]$ls –l | wc-l

Q30. Count number of bytes of all the .c files individually present in the current directory.

Ans. [student@localhost student]$wc –c *.c

Q31. Count total number of bytes of all the .c files present in the current directory.

Ans. [student@localhost student]$wc –c *.c | tail -1 –

Q32. Display the statement as there are _ files in the current directory using command substitution.

Ans. [student@localhost student]$echo “There are `ls –l –R | wc –l` files in current dir.”

Q33. Try the two statement echo the average pay is $1000
echo ‘the average pay is $1000’
echo “the average pay is $1000”

Ans. [student@localhost student]$echo ‘the average pay is $1000’
the average pay is $1000
echo “the average pay is $1000”
the average pay is 000

Q34. Create a shell variable as work and store the present working directory in it and display the same.

Ans. [student@localhost student]$ work=`pwd`

Q35. Try the command cat alone without using an argument.

Ans. [student@localhost student]$cat
(With only cat command with no file name it only read standard input.)

Check [Part-2] for next set of Questions.

Categories: Interview Q, Linux, Others Tags: Interview Questions, Linux Commands, UNIX Commands, Unix Interview Questions

SQL DBA – Change a Database Collation in SQL Server

March 25, 2010 3 comments

In previous post I discussed about collation differences b/w different databases/tables and how to query them. The solution was not permanent and every time one has to apply the default collation with the column names while matching them at where clause.

I thought to modify the collation of my database and tables on my new database and make it compatible to my old database. However this is not advisable for existing DBs, because collation setting within databases is done intentionally and for some purpose.

I issued the following sql statement to alter the database collation level:

alter database someDatabase collate SQL_Latin1_General_CP437_BIN

But it ended up on the following error:

Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'someDatabase' 
cannot be set to SQL_Latin1_General_CP437_BIN.

Then I came to know that the database has to be in single user mode, so that I can apply the changes. So I issued the following sql statements:

ALTER DATABASE someDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE someDatabase COLLATE SQL_Latin1_General_CP437_BIN
ALTER DATABASE someDatabase SET MULTI_USER

… and yes they get successfully executed. I checked the collation and it got changed to the new one.
But for the existing tables I had to manually change the collation of each & every tables by applying following sql statements:

alter table someTable alter column [someColumnX] varchar(9) COLLATE SQL_Latin1_General_CP437_BIN

Categories: DBA Stuff Tags: COLLATE, Collation, Database Collation, SQL_Latin1_General_CP437_BIN

Newer Entries Older Entries

SQL with Manoj

SQL Basics – Backup and Restore Database in SQL Server

SQL basics – Temporary Tables vs Table Variables

Basic UNIX/Linux commands for Interview Questions – Part 2

Basic UNIX/Linux commands for Interview Questions – Part 1

SQL DBA – Change a Database Collation in SQL Server

Follow Us

SQL Tags

Categories

Archives

Top Posts

Blog Stats, since Aug 2010

Current Visitors

StatCounter …since April 2012

Leisure blog: Creek & Trails

Disclaimer

Meta

Follow Blog via Email

Alexa Rank