[27-09-11]
Alter view–>
http://msdn.microsoft.com/en-us/library/ms173846.aspx –>
Here giving schema name is mandatory wen u are going to alter the view
ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ] Ex: alter view dbo.updatecountry_O as select * from country_other where rowid = 1 http://www.techonthenet.com/sql/views.php -->
Question: Can you update the data in a view?
Answer: A view is created by joining one or more tables. When you update record(s) in a view, it updates the records in the underlying tables that make up the view.
So, yes, you can update the data in a view providing you have the proper privileges to the underlying tables.
EX:
1.
select * from country_other
alter view dbo.updatecountry_O
as
select * from country_other
–update country_other set countryname = ‘USA’ where rowid = 4
select * from updatecountry_O
update updatecountry_O set countryname = ‘USA’ where rowid = 4
–2
alter view dbo.updatecountry_O
as
select co.rowid, CO.countryname as COC, cus.countryname as CUS from country_other CO
inner join country_us CUS on co.rowid = cus.rowid
select * from updatecountry_O
update updatecountry_O set COC = ‘USA’ where rowid = 1
http://www.w3schools.com/sql/sql_union.asp –> [explained with good exmple]
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
union vs unionall
http://sqlwithmanoj.wordpress.com/2010/12/30/why-union-all-is-faster-than-union/
http://msdn.microsoft.com/en-us/library/ms180026.aspx –>
1.Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.
2.The following are basic rules for combining the result sets of two queries by using UNION:
a.The number and the order of the columns must be the same in all queries.
b.The data types must be compatible.
UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.
A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.
Example:
Table 1 : First,Second,Third,Fourth,Fifth
Table 2 : First,Second,Fifth,Sixth
Result Set:
UNION: First,Second,Third,Fourth,Fifth,Sixth (This will remove duplicate values)
UNION ALL: First,First,Second,Second,Third,Fourth,Fifth,Fifth,Sixth,Sixth (This will repeat values)
[20-09-11]
http://searchsqlserver.techtarget.com/tip/Differences-between-varchar-and-nvarchar-in-SQL-Server
[19-09-11]
DB Tuning –>
1.
only insert operations were applied on transaction tables
SCAN –>
proper index if u hav, then wen u run select it won’t chk every record bcos the data already is sorted [based on primary key]
seek –> directly taking the required record in the db file
NonClusteredindex –> table to maintain nci based records sort order and their links
2.heavy dml operations [devide that, i.e seperate data read and write operations] goes to write db
db replication
reduce scan operations inc seek operations
avoiding subqueries
join vs subquery
don’t use select *, if required then only use
minimise usage of views [view which gives result from 10 tables, whichu not required all 10 tables to get ur result], proper indexes, chk execution plans,
Tuning –> middletier/dataaccesslayer
2.
wen u populating grid view just retrieve required records [like 20 per page instead of all 1000], using CTE[common table expressions]
31-08-11
JOINS [******]
http://dotnetslackers.com/articles/sql/SQL-SERVER-JOINs.aspx#cross-join
21-08-11
CTE[Common table expression]
http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
[cursor vs while]
18-08-11
http://beginner-sql-tutorial.com/[*******]
19-07-2011
Normalization–>In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
Denormalization–>is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
09-06-2011
SQL 2008
—————————–SQL Unlock
http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/6185492
http://msdn.microsoft.com/en-us/library/aa259216(v=sql.80).aspx
sql operators
http://beginner-sql-tutorial.com/sql-operators.htm
Difference between sql server 2000 and sql server 2005
http://www.allinterview.com/showanswers/72899.html
Self join–>
http://programming.top54u.com/post/Self-Join-In-Sql-Server-2000-2005.aspx
What is difference between TRUNCATE & DELETE
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to
COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUCATE is faster and doesn’t use as much undo space as a DELETE.
The DROP command removes a table from the database. All the tables’ rows,
indexes and privileges will also be removed. The operation cannot be rolled back.
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
What if we only want to delete the data inside the table, and not the table itself?
Then, use the TRUNCATE TABLE statement:
TRUNCATE TABLE table_name
Sometimes we wish to get rid of all the data in a table. One way of doing this is with DROP TABLE, which we saw in the last section. But what if we wish to simply get rid of the data but not the table itself? For this, we can use the TRUNCATE TABLE command. The syntax for TRUNCATE TABLE is
TRUNCATE TABLE “table_name”
Refeence Links
http://www.geekinterview.com/question_details/24530
ramprasadm 