Tuesday, December 29, 2009
SQL interview Questions and Answers III
What are the advantage of User Defined function over store procedure
There are no of benefits of SQL Server User-Defined functions. Some of these are here we can use these functions in so many different places with comparison to SQL Server stored procedure. Two of user define function acts like a table (Inline and Multi-statement functions) helps developers to reduce the code and break complex logic in short code blocks. On the other hand Scalar User-Defined Function have ability so that we use this function anywhere where we need some single value result or some of operation. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User-Defined function a very powerful tool.
What are the advantages of using Stored Procedures? Stored procedure can reduced network traffic and latency, boosting application performance. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead. Stored procedures help promote code reuse. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients. Stored procedures provide better security to your data.
What is a table called, if it has neither Cluster nor Non‐cluster Index? What is it used for? Un-indexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Un-indexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and then do bulk of inserts and to restore those indexes after that.
What command do we use to rename a db, a table and a column?
To rename db
sp_renamedb ‘oldname’ , ‘newname’
If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.
E.g.
USE master;
GO
EXEC sp_dboption AdventureWorks, 'Single User', True
GO
EXEC sp_renamedb 'AdventureWorks', 'AdventureWorks_New'
GO
EXEC sp_dboption AdventureWorks, 'Single User', False
GO
To rename Table
We can change the table name using sp_rename as follows,
sp_rename ‘oldTableName’ ‘newTableName’
E.g.
SP_RENAME ‘Table_First’, ‘Table_Last’ GO
To rename Column
The script for renaming any column :
sp_rename ‘TableName.[OldcolumnName]’, ‘NewColumnName’, ‘Column’
E.g.
sp_RENAME ‘Table_First.Name’, ‘NameChange’ , ‘COLUMN’ GO
Define basic functions for master and msdb and tempdb databases in SQL Server
(1)master:-It contains system level information for a SQL Server system and also contains login accounts and all system configuration settings. master is the database that records the existence of all other databases, including the location of the database files.
(2) tempdb - This database holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQ
L Server. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database.
(3)mode - The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.
(4)msdb - The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.
What is Cursor: Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.
What is Collate in SQL SERVER2000: The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types. The physical storage of character strings in Microsoft� SQL Server� 2000 is controlled by collations. A collation specifies the bit patterns to represent each character and the rules by which characters are sorted and compared with another character.
Exception Handling in SQL Server 2000: Basically, in Microsoft SQL Server 2000, there exists no structured exception handling. We need to depend on @@ERROR for any errors that occur. No TRY CATCH Block is available in 2000.
create procedure dbo.sp_emp_insert
(
@empno int,
@ename varchar(20),
@sal float,
@deptno int
)
as
begin
declare @Error int
begin transaction
insert into emp (empno,ename,sal,deptno) values (empno,@ename,@sal,@deptno)
set @Error = @@ERROR
if @Error <> 0 --if error is raised
begin
goto LogError
end
commit transaction
goto ProcEnd
LogError:
rollback transaction
declare @ErrMsg varchar(1000)
select @ErrMsg = [description] from master.dbo.sysmessages
where error = @Error
insert into error_log (LogDate,Source,ErrMsg)
values (getdate(),'sp_emp_insert',@ErrMsg)
ProcEnd:
end
GO
To execute the above program, you need to issue the following statement in query analyzer:
exec sp_emp_insert 1003,'ccc',4000,30
Exception Handling in SQL Server 2005
DropProcedure dbo.sp_emp_insert
go
createprocedure [dbo].[sp_emp_insert]
(
@empno int,
@ename varchar(20),
@sal float,
@deptno int
)
as
begin
begintry
begin transaction
insert into emp (empno,ename,sal,deptno)
values (@empno,@ename,@sal,@deptno)
commit transaction
endtry
begincatch
rollback transaction
insert into error_log (LogDate,Source,ErrMsg)
values (getdate(),'sp_emp_insert',error_message())
endcatch
end
Execute:
exec sp_emp_insert 1003,'ccc',4000,30
What is a Linked Server? Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T‐SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.
What is Collation? Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width.
What is User Defined Functions? What kind of User-Defined Functions can be created?
User‐Defined Functions allow defining its own T‐SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.
Different Kinds of User‐Defined Functions created are:
• Scalar User‐Defined Function
• A Scalar user‐defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported.
These are the type of user‐defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.
What is Difference between Function and Stored Procedure? UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another row-set. This can be used in JOINs with other tables. Inline UDF's can be thought of as views that take parameters and can be used in JOINs and other Row-set operations.
What is sub-query? Explain properties of sub-query? Sub‐queries are often referred to as sub‐selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub‐query is executed by enclosing it in a set of parentheses. Sub‐queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.
A sub-query is a SELECT statement that is nested within another T‐SQL statement. A sub-query SELECT statement if executed independently of the T‐SQL statement, in which it is nested, will return a result-set. Meaning a sub-query SELECT statement can standalone and is not depended on the statement in which it is nested. A sub-query SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T‐SQL statement. A Sub-query can also be used as a parameter to a function call. Basically a sub-query can be used anywhere an expression can be used.
What are the properties and different Types of Sub-Queries?
Properties of Sub‐Query
• A sub‐query must be enclosed in the parenthesis.
• A sub‐query must be put in the right hand of the comparison operator, and
• A sub‐query cannot contain an ORDER‐BY clause.
• A query can contain more than one sub‐query.
Types of Sub‐query
• Single‐row sub‐query, where the sub‐query returns only one row.
• Multiple‐row sub‐query, where the sub‐query returns multiple rows,. and
• Multiple column sub‐query, where the sub‐query returns multiple columns
What are primary keys and foreign keys?
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key.
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.
Candidate key: If any unique data column(s) in table is called candidate key. Maybe one or more unique data column
(s).We can select any one unique (candidate key column) as a primary key.
Alternate key: If one table contains more than one candidate keys, remaining candidate keys column(s) which is not selected as a primary key is called Alternate Key.
Composite key: One primary key Contains more than one columns is called Composite key.
What is UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
How to get @@ERROR and @@ROWCOUNT at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error‐checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
Inline Table-Value User-Defined Function
An Inline Table‐Value user‐defined function returns a table data type and is an exceptional alternative to a view as the user‐defined function can pass parameters into a T‐SQL select command and in essence provide us with a parameterized, non‐updateable view of the underlying tables.
Multi-statement Table-Value User-Defined Function
A Multi‐Statement Table‐Value user‐defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T‐SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized, non‐updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user‐defined function, It can be used in the FROM clause of a T‐SQL command unlike the behavior found when using a stored procedure which can also return record sets.
What is Identity? Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.
What is Data-Warehousing? Subject‐oriented, meaning that the data in the database is organized so that all the data elements relating to the same real‐world event or object are linked together;
Time‐variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
Non‐volatile, meaning that data in the database is never over‐written or deleted, once committed, the data is static, read‐only, but retained for future reporting.
Integrated, meaning that the database contains data from most or all of an organization's operational applications, and that this data is made consistent.
What are the difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
What are the different index configurations a table can have?
A table can have one of the following index configurations:
o indexes
A clustered index
A clustered index and many non-clustered indexes
A non-clustered index
Many non-clustered indexes
What are different types of Collation Sensitivity?
Case sensitivity ‐ A and a, B and b, etc.
Accent sensitivity ‐ a and á, o and ó, etc.
Kana Sensitivity ‐ When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
Width sensitivity ‐ A single‐byte character (half‐width) and the same character represented as a double‐byte character (full‐width) are treated differently than it is width sensitive.
SQL interview Questions and Answers II
What are the null values in SQL SERVER: Before understand the null values we have some overview about what the value is. Value is the actual data stored in a particular field of particular record. But what is done when there are no values in the field. That value is something like .Nulls present missing information. We can also call null propagation.
Write a Role of SQL Server 2005 in XML Web Services: SQL Server 2005 creates a standard method for getting the database engine using SOAP via HTTP. By this method, we can send SOAP/HTTP requests to SQL Server for executing T-SQL batch statements, stored procedures, extended stored procedures, and scalar-valued user-defined functions may be with or without parameters.
What are the different types of Locks
There are three main types of locks that SQL Server
• Shared locks are used for operations that do not allow changing or updating data, such as a SELECT statement.
• Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
• Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.
What is Write ahead log in SQL Server 2000: Before understanding it we must have an idea about the transaction log files. These files are the files which hold the data for change in database. Now we explain when we are doing some SQL Server 2000 query or any SQL query like SQL insert query, delete SQL query, update SQL query and change the data in SQL server database it cannot change the database directly to table .SQL server extracts the data that is modified by SQL server 2000 query or by SQL query and places it in memory. Once data is stores in memory user can make changes to that a log file is garneted this log file is garneted in every five minutes of transaction is done. After this SQL server writes changes to database with the help of transaction log files. This is called Write-ahead log.
What does u mean by Extents and types of Extents: An Extent is a collection of 8 sequential pages to hold database from becoming fragmented. Fragment means these pages relates to same table of database these also holds in indexing. To avoid for fragmentation SQL Server assign space to table in extents. So that the SQL Server keep up to date data in extents. Because these pages are continuously one after another. There are usually two types of extends:-Uniform and Mixed. Uniform means when extent its own by a single object means all collection of 8 ages hold by a single extends is called uniform. Mixed mean when more than one object is comes in extents is known as mixed extents.
What is different in Rules and Constraints: Rules and Constraints are similar in functionality but there is a An little difference between them. Rules are used for backward compatibility. One the most exclusive difference is that we can bind rules to a datatype whereas constraints are bound only to columns. So we can create our own datatype with the help of Rules and get the input according to that.
What is defaults in SQL Server and types of Defaults: Defaults are used when a field of columns is almost common for all the rows for example in employee table all living in Delhi that value of this field is common for all the row in the table if we set this field as default the value that is not fill by us automatically fills the value in the field it’s also work as intellisense means when user inputting d it will automatically fill the Delhi. There are two types of defaults object and definitions. Object default:-These defaults are applicable on particular columns. These are usually denied at the time of table designing. When u set the object default field in column state this column in automatically field when u left this filed blank. Definition default:-When we bind the datatype with default let we named this as Dotnet .Then every time we create column and named its datatype as Dotnet it will behave the same that we set for Dotnet datatype.
Transaction: Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails the transaction fails. Therefore a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction. Users can group two or more Transact-SQL statements into a single transaction using the following statements: * Begin Transaction * Rollback Transaction * Commit Transaction If anything goes wrong with any of the grouped statements all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction all changes are recorded together in the database.
CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
-- Delete the Employee's phone numbers
DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID
-- Delete the Employee record
DELETE FROM Employees WHERE EmployeeID = @EmployeeID
-- If we reach here, success!
COMMIT
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
What is COMMIT and ROLLBACK statement in SQL: Commit statement helps in termination of the current transaction and does all the changes that occur in transaction persistent and this also commits all the changes to the database. COMMIT we can also use in store procedure. ROLLBACK do the same thing just terminate the current transaction but one another thing is that the changes made to database are ROLLBACK to the database.
What is difference between OSQL and Query Analyzer: Both are the same but there is little difference OSQL is command line tool which is execute query and display the result same a query analyzer but query analyzer is graphical and OSQL is a command line tool. OSQL have not ability like query analyzer to analyze queries and show statics on speed of execution and other useful thing about OSQL is that its helps in scheduling.
What are Data Integrity and its categories: Enforcing data integrity ensures the quality of the data in the database. For example, if an employee is entered with an employee_id value of 123, the database should not allow another employee to have an ID with the same value. If you have an employee_rating column intended to have values ranging from 1 to 5, the database should not accept a value of 6. If the table has a dept_id column that stores the department number for the employee, the database should allow only values that are valid for the department numbers in the company.
Two important steps in planning tables are to identify valid values for a column and to decide how to enforce the integrity of the data in the column. Data integrity falls into these categories:
1) Entity integrity 2) Domain integrity 3) Referential integrity 4) User-defined integrity
Entity Integrity: Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).
Domain Integrity: Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules).
Referential Integrity: Referential integrity preserves the defined relationships between tables when records are entered or deleted. In Microsoft� SQL Server� 2000, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints). Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database. When you enforce referential integrity, SQL Server prevents users from: � Adding records to a related table if there is no associated record in the primary table. � Changing values in a primary table that result in orphaned records in a related table. � Deleting records from a primary table if there are matching related records. For example, with the sales and titles tables in the pubs database, referential integrity is based on the relationship between the foreign key (title_id) in the sales table and the primary key (title_id) in the titles table.
User-Defined: Integrity User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).
SQL Server runs on which TCP IP port and from where can you change the default port: SQL Server runs on port 1433 but we can also change it for better security and from the network Utility TCP/IP properties -->Port number, both on client and the server.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE
Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
When do you use SQL Profiler: SQL Profiler utility allows us to basically track Connections to the SQL Server and also determine activities, such as which SQL Scripts are running, failed jobs etc?
ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.
Atomicity is an all-or-none proposition.
Consistency guarantees that a transaction never leaves your database in a half-finished state.
Isolation keeps transactions separated from each other until they’re finished.
Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.
Above four rules are very important for any developers dealing with databases.
Can you explain the role of each service: SQL SERVER - is for running the databases SQL AGENT - is for automation such as Jobs, DB Maintenance, Backups DTC - Is for linking and connecting to other SQL Servers.
Difference between Triggers and Stored procedures
A STORED PROCEDURE is a set of structured query language statements that you assign a name and store it in to the database in a compiled form so that it can share between numbers of programs.
Some advantages of Store Procedure.-they allow faster execution. -they can reduce network traffic.
Triggers are basically used to implement business rules. A trigger is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database. Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
Creates a DML, DDL, or logon trigger.
DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.
DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.
Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established.
Note: These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.
CREATE TRIGGER tr_InetLog_INSERT
ON InetLog
FOR INSERT
AS
IF EXISTS (SELECT * FROM inserted WHERE Target = 'AboutUs.htm')
BEGIN
UPDATE LogSummary
SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = 'AboutUs.htm')
WHERE LogSum_Category = 'About Us'
END
IF EXISTS (SELECT * FROM inserted WHERE Target = 'Services.htm')
BEGIN
UPDATE LogSummary
SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = 'Services.htm')
WHERE LogSum_Category = 'Services'
END
Go
Types:
1. After Trigger
2. Multiple After Triggers
3. Instead Of Triggers
4. Mixing Triggers Type
What is Normalization: The logical design of the database, including the tables and the relationships between them, is the core of an optimized relational database. A good logical database design can lay the foundation for optimal database and application performance. A poor logical database design can impair the performance of the entire system. Normalizing a logical database design involves using formal methods to separate the data into multiple, related tables.
A greater n umber of narrow tables (with fewer columns) is characteristic of a normalized database. A few wide tables (with more columns) is characteristic of an non-normalized database. Reasonable normalization often improves performance. When useful indexes are available, the Microsoft� SQL Server� 2000 query optimizer is efficient at selecting rapid, efficient joins between tables. Some of the benefits of normalization include: �Faster sorting and index creation. � A larger number of clustered indexes. For more information, Narrower and more compact indexes. �Fewer indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements. �Fewer null values and less opportunity for inconsistency, which increase database compactness. As normalization increases, so do the number and complexity of joins required to retrieve data. Too many complex relational joins between too many tables can hinder performance.
Reasonable normalization often includes few regularly executed queries that use joins involving more than four tables. Sometimes the logical database design is already fixed and total redesign is not feasible. Even then, however, it might be possible to normalize a large table selectively into several smaller tables. If the database is accessed through stored procedures, this schema change could take place without affecting applications. If not, it might be possible to create a view that hides the schema change from the applications.
What are different normalization forms?
1NF: Eliminate Repeating Groups: Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data: If an attribute depends on only part of a multi‐valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key: If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
BCNF: Boyce‐Codd Normal Form: If there are non‐trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships: No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships: There may be practical constrains on information that justify separating logically related many‐to‐many relationships.
ONF: Optimal Normal Form: A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain‐Key Normal Form: A model free from all modification anomalies is said to be in DKNF.
Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.
Can you explain what View is in SQL: View is just a virtual table nothing else which is based or we can say develop with SQL SELECT query? So we can say that it’s a real database table (it has columns and rows just like a regular table), but one difference is that real tables store data, but views cant. View data is generated dynamically when the view is referenced. And view can also reference one or more existing database tables or other views. We can say that it is filter of database.
How to get which Process is Blocked in SQL SERVER: There are two ways to get this sp_who and sp_who2 . You cannot get any detail about the sp_who2 but its provide more information than the sp_who . And other option from which we can find which process is blocked by other process is by using Enterprise Manager or Management Studio, these two commands work much faster and more efficiently than these GUI-based front-ends.
Can you tell me the difference between DELETE and TRUNCATE commands
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
What are the Global Temporary Tables: We can create global temporary tables but these are not using much in sql and the name of this table start with two pound signs? For example, ##interviewqsn is a global temporary table. As the name suggest these table is Global temporary tables and visible to all SQL Server connections. When we create any one of these all users can see it.
Write a Role of SQL Server 2005 in XML Web Services: SQL Server 2005 creates a standard method for getting the database engine using SOAP via HTTP. By this method, we can send SOAP/HTTP requests to SQL Server for executing T-SQL batch statements, stored procedures, extended stored procedures, and scalar-valued user-defined functions may be with or without parameters.
What are the different types of Locks
There are three main types of locks that SQL Server
• Shared locks are used for operations that do not allow changing or updating data, such as a SELECT statement.
• Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
• Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.
What is Write ahead log in SQL Server 2000: Before understanding it we must have an idea about the transaction log files. These files are the files which hold the data for change in database. Now we explain when we are doing some SQL Server 2000 query or any SQL query like SQL insert query, delete SQL query, update SQL query and change the data in SQL server database it cannot change the database directly to table .SQL server extracts the data that is modified by SQL server 2000 query or by SQL query and places it in memory. Once data is stores in memory user can make changes to that a log file is garneted this log file is garneted in every five minutes of transaction is done. After this SQL server writes changes to database with the help of transaction log files. This is called Write-ahead log.
What does u mean by Extents and types of Extents: An Extent is a collection of 8 sequential pages to hold database from becoming fragmented. Fragment means these pages relates to same table of database these also holds in indexing. To avoid for fragmentation SQL Server assign space to table in extents. So that the SQL Server keep up to date data in extents. Because these pages are continuously one after another. There are usually two types of extends:-Uniform and Mixed. Uniform means when extent its own by a single object means all collection of 8 ages hold by a single extends is called uniform. Mixed mean when more than one object is comes in extents is known as mixed extents.
What is different in Rules and Constraints: Rules and Constraints are similar in functionality but there is a An little difference between them. Rules are used for backward compatibility. One the most exclusive difference is that we can bind rules to a datatype whereas constraints are bound only to columns. So we can create our own datatype with the help of Rules and get the input according to that.
What is defaults in SQL Server and types of Defaults: Defaults are used when a field of columns is almost common for all the rows for example in employee table all living in Delhi that value of this field is common for all the row in the table if we set this field as default the value that is not fill by us automatically fills the value in the field it’s also work as intellisense means when user inputting d it will automatically fill the Delhi. There are two types of defaults object and definitions. Object default:-These defaults are applicable on particular columns. These are usually denied at the time of table designing. When u set the object default field in column state this column in automatically field when u left this filed blank. Definition default:-When we bind the datatype with default let we named this as Dotnet .Then every time we create column and named its datatype as Dotnet it will behave the same that we set for Dotnet datatype.
Transaction: Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails the transaction fails. Therefore a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction. Users can group two or more Transact-SQL statements into a single transaction using the following statements: * Begin Transaction * Rollback Transaction * Commit Transaction If anything goes wrong with any of the grouped statements all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction all changes are recorded together in the database.
CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
-- Delete the Employee's phone numbers
DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID
-- Delete the Employee record
DELETE FROM Employees WHERE EmployeeID = @EmployeeID
-- If we reach here, success!
COMMIT
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
What is COMMIT and ROLLBACK statement in SQL: Commit statement helps in termination of the current transaction and does all the changes that occur in transaction persistent and this also commits all the changes to the database. COMMIT we can also use in store procedure. ROLLBACK do the same thing just terminate the current transaction but one another thing is that the changes made to database are ROLLBACK to the database.
What is difference between OSQL and Query Analyzer: Both are the same but there is little difference OSQL is command line tool which is execute query and display the result same a query analyzer but query analyzer is graphical and OSQL is a command line tool. OSQL have not ability like query analyzer to analyze queries and show statics on speed of execution and other useful thing about OSQL is that its helps in scheduling.
What are Data Integrity and its categories: Enforcing data integrity ensures the quality of the data in the database. For example, if an employee is entered with an employee_id value of 123, the database should not allow another employee to have an ID with the same value. If you have an employee_rating column intended to have values ranging from 1 to 5, the database should not accept a value of 6. If the table has a dept_id column that stores the department number for the employee, the database should allow only values that are valid for the department numbers in the company.
Two important steps in planning tables are to identify valid values for a column and to decide how to enforce the integrity of the data in the column. Data integrity falls into these categories:
1) Entity integrity 2) Domain integrity 3) Referential integrity 4) User-defined integrity
Entity Integrity: Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).
Domain Integrity: Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules).
Referential Integrity: Referential integrity preserves the defined relationships between tables when records are entered or deleted. In Microsoft� SQL Server� 2000, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints). Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database. When you enforce referential integrity, SQL Server prevents users from: � Adding records to a related table if there is no associated record in the primary table. � Changing values in a primary table that result in orphaned records in a related table. � Deleting records from a primary table if there are matching related records. For example, with the sales and titles tables in the pubs database, referential integrity is based on the relationship between the foreign key (title_id) in the sales table and the primary key (title_id) in the titles table.
User-Defined: Integrity User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).
SQL Server runs on which TCP IP port and from where can you change the default port: SQL Server runs on port 1433 but we can also change it for better security and from the network Utility TCP/IP properties -->Port number, both on client and the server.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE
Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
When do you use SQL Profiler: SQL Profiler utility allows us to basically track Connections to the SQL Server and also determine activities, such as which SQL Scripts are running, failed jobs etc?
ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.
Atomicity is an all-or-none proposition.
Consistency guarantees that a transaction never leaves your database in a half-finished state.
Isolation keeps transactions separated from each other until they’re finished.
Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.
Above four rules are very important for any developers dealing with databases.
Can you explain the role of each service: SQL SERVER - is for running the databases SQL AGENT - is for automation such as Jobs, DB Maintenance, Backups DTC - Is for linking and connecting to other SQL Servers.
Difference between Triggers and Stored procedures
A STORED PROCEDURE is a set of structured query language statements that you assign a name and store it in to the database in a compiled form so that it can share between numbers of programs.
Some advantages of Store Procedure.-they allow faster execution. -they can reduce network traffic.
Triggers are basically used to implement business rules. A trigger is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database. Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
Creates a DML, DDL, or logon trigger.
DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.
DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.
Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established.
Note: These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.
CREATE TRIGGER tr_InetLog_INSERT
ON InetLog
FOR INSERT
AS
IF EXISTS (SELECT * FROM inserted WHERE Target = 'AboutUs.htm')
BEGIN
UPDATE LogSummary
SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = 'AboutUs.htm')
WHERE LogSum_Category = 'About Us'
END
IF EXISTS (SELECT * FROM inserted WHERE Target = 'Services.htm')
BEGIN
UPDATE LogSummary
SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = 'Services.htm')
WHERE LogSum_Category = 'Services'
END
Go
Types:
1. After Trigger
2. Multiple After Triggers
3. Instead Of Triggers
4. Mixing Triggers Type
What is Normalization: The logical design of the database, including the tables and the relationships between them, is the core of an optimized relational database. A good logical database design can lay the foundation for optimal database and application performance. A poor logical database design can impair the performance of the entire system. Normalizing a logical database design involves using formal methods to separate the data into multiple, related tables.
A greater n umber of narrow tables (with fewer columns) is characteristic of a normalized database. A few wide tables (with more columns) is characteristic of an non-normalized database. Reasonable normalization often improves performance. When useful indexes are available, the Microsoft� SQL Server� 2000 query optimizer is efficient at selecting rapid, efficient joins between tables. Some of the benefits of normalization include: �Faster sorting and index creation. � A larger number of clustered indexes. For more information, Narrower and more compact indexes. �Fewer indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements. �Fewer null values and less opportunity for inconsistency, which increase database compactness. As normalization increases, so do the number and complexity of joins required to retrieve data. Too many complex relational joins between too many tables can hinder performance.
Reasonable normalization often includes few regularly executed queries that use joins involving more than four tables. Sometimes the logical database design is already fixed and total redesign is not feasible. Even then, however, it might be possible to normalize a large table selectively into several smaller tables. If the database is accessed through stored procedures, this schema change could take place without affecting applications. If not, it might be possible to create a view that hides the schema change from the applications.
What are different normalization forms?
1NF: Eliminate Repeating Groups: Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data: If an attribute depends on only part of a multi‐valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key: If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
BCNF: Boyce‐Codd Normal Form: If there are non‐trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships: No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships: There may be practical constrains on information that justify separating logically related many‐to‐many relationships.
ONF: Optimal Normal Form: A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain‐Key Normal Form: A model free from all modification anomalies is said to be in DKNF.
Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.
Can you explain what View is in SQL: View is just a virtual table nothing else which is based or we can say develop with SQL SELECT query? So we can say that it’s a real database table (it has columns and rows just like a regular table), but one difference is that real tables store data, but views cant. View data is generated dynamically when the view is referenced. And view can also reference one or more existing database tables or other views. We can say that it is filter of database.
How to get which Process is Blocked in SQL SERVER: There are two ways to get this sp_who and sp_who2 . You cannot get any detail about the sp_who2 but its provide more information than the sp_who . And other option from which we can find which process is blocked by other process is by using Enterprise Manager or Management Studio, these two commands work much faster and more efficiently than these GUI-based front-ends.
Can you tell me the difference between DELETE and TRUNCATE commands
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
What are the Global Temporary Tables: We can create global temporary tables but these are not using much in sql and the name of this table start with two pound signs? For example, ##interviewqsn is a global temporary table. As the name suggest these table is Global temporary tables and visible to all SQL Server connections. When we create any one of these all users can see it.
SQL interview Questions and Answers
MS-SQL Server
Database:
A database is similar to a data file in that it is a storage place for data. Like a data file, a database does not present information directly to a user; the user runs an application that accesses data from the database and presents it to the user in an understandable format. Database systems are more powerful than data files in that data is more highly organized. In a well-designed database, there are no duplicate pieces of data that the user or application must update at the same time.
Related pieces of data are grouped together in a single structure or record, and relationships can be defined between these structures and records. When working with data files, an application must be coded to work with the specific structure of each data file. In contrast, a database contains a catalog that applications use to determine how data is organized. Generic database applications can use the catalog to present users with data from different databases dynamically, without being tied to a specific data format.
A database typically has two main parts: first, the files holding the physical database and second, the database management system (DBMS) software that applications use to access data. The DBMS is responsible for enforcing the database structure, including: - maintaining relationships between data in the database. Ensuring that data is stored correctly and that the rules defining data relationships are not violated. - recovering all data to a point of known consistency in case of system failures.
Relational Database: Although there are different ways to organize data in a database, relational databases are one of the most effective. Relational database systems are an application of mathematical set theory to the problem of effectively organizing data. In a relational database, data is collected into tables (called relations in relational theory).
A table represents some class of objects that are important to an organization. For example, a company may have a database with a table for employees, another table for customers, and another for stores. Each table is built of columns and rows (called attributes and tuples in relational theory). Each column represents some attribute of the object represented by the table. For example, an Employee table would typically have columns for attributes such as first name, last name, employee ID, department, pay grade, and job title.
Each row represents an instance of the object represented by the table. For example, one row in the Employee table represents the employee who has employee ID 12345. When organizing data into tables, you can usually find many different ways to define tables. Relational database theory defines a process called normalization, which ensures that the set of tables you define will organize your data effectively.
To Create DB: CREATE DATABASE DatabaseName
What is SQL what its uses and its component
Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. QL is Non-Procedural language. It allow the user to concentrate on specifying what data is required rather than concentrating on the how to get it.
The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables
What is the difference between SQL and T-SQL? Why would a company use one over the other?
ANSI SQL is the standard Structured Query Language. Most database vendors support SQL. T-SQL is Microsoft's "flavor" of SQL; it is ANSI SQL with Microsoft's extensions. A company could choose to use T-SQL over SQL if they have a database application that communicates with a MS SQL Server database only. Therefore a developer could use the "extras" for improved performance, ease of SQL coding, etc. A company could chose SQL because a front-end may have to communicate with several relational database management systems (RDBMs) therefore a programmer could rely on the fact that most database understand ANSI SQL.
Provide all the built in string function of SQL SERVER:
ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX, SPACE, CHARINDEX, REPLACE, STR, DIFFERENCE, QUOTENAME, STUFF, LEFT, REPLICATE, SUBSTRING, LEN, REVERSE, UNICODE, LOWER, RIGHT, UPPER, LTRIM, RTRIM.
How to get number of Maximum connection can be establish to SQL
Select @@MAX_Connections.
Different index configurations table in SQL:
In SQL table can have following index configurations just like: - No indexes, A clustered index, A clustered index and many non-clustered indexes, A non-clustered index, Many non-clustered indexes.
How many objects SQL Server contains: Here is the list of some of the more important database objects database, The transaction log, Assemblies, Tables, Reports, Filegroups, Full-text catalogs, Diagrams, User-defined data types, Views, Roles, Stored procedures, Users, User Defined Functions.
Difference between Extents and Page: Extents: Multiple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.
Why we use SET ROWCOUNT in SQL: This syntax is used in SQL Server to stop processing the query after the specified number of rows is returned.
Why we use Unicode in SQL server: Unicode data is stored using the nchar, nvarchar, and ntext data types in SQL Server. Use these data types for columns that store characters from more than one character set. The SQL Server Unicode data types are based on the National Character data types in the SQL-92 standard.
What is a join and List different types of joins: Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins:
• INNER JOIN,
• OUTER JOIN,
• CROSS JOIN.
OUTER JOIN is further classified as
• LEFT OUTER JOINS,
• RIGHT OUTER JOINS and
• FULL OUTER JOINS.
How does .NET and SQL SERVER thread is work
There are two types of threading pre-emptive and Non-preemptive but SQL Server support Non-preemptive and .NET thread model is different. Because SQL have to handle thread in different way for SQLCLR this different thread are known as Tasking of Threads. In this thread there is a switch between SQLCLR and SQL SERVER threads .SQL SERVER uses blocking points for transition to happen between SQLCLR and SQL SERVER threads.
Difference between temp table and table variable
• Temp Tables are created in the SQL Server TEMPDB database and therefore require more IO resources and locking. Table Variables and Derived Tables are created in memory.
• Temp Tables will generally perform better for large amounts of data that can be worked on using parallelism whereas Table Variables are best used for small amounts of data (I use a rule of thumb of 100 or less rows) where parallelism would not provide a significant performance improvement.
• You cannot use a stored procedure to insert data into a Table Variable or Derived Table. For example, the following will work: INSERT INTO #MyTempTable EXEC dbo.GetPolicies_sp whereas the following will generate an error: INSERT INTO @MyTableVariable EXEC dbo.GetPolicies_sp.
• Derived Tables can only be created from a SELECT statement but can be used within an Insert, Update, or Delete statement.
• In order of scope endurance, Temp Tables extend the furthest in scope, followed by Table Variables, and finally Derived Tables.
How many types of local table in SQL define with syntax
There are 2 types of temporary tables, local and global. Local temporary tables are created using a single pound (#) sign and are visible to a single connection and automatically dropped when that connection ends. Global temporary tables are created using a double pound (##) sign and are visible across multiple connections and users and are automatically dropped when all SQL sessions stop referencing the global temporary table.
CREATE TABLE #MyTempTable ( PolicyId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,LastName VARCHAR(50) NOT NULL )
What is SQL tuning: SQL tuning is the process of getting that the SQL statements that an application that will issue that's run in the fastest possible time.
What is SQL injection: SQL injection is a security vulnerability that occurs in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.
What is SET operator in SQL SERVER: SET operators mainly used to combine same type of data from two or more tables. And another thing is that columns and their data type should be same as all the queries have. The column names from the first query will appear in the result.
UNION - It produce rows of first query + rows of 2nd query minus duplicate rows
UNION ALL - It produce rows from both the queries including duplicate rows.
select empid from emp union all select empid from emp_att group by empid
MINUS - Rows that are unique for the 1st query will be retrieved
INTERSECT - common rows from both the queries will be retrieved.
Join is used to select columns from two or more tables.
Can you define ROLLUP in SQL SERVER 2005: ROLLUP work with the "Group By" clause its main functioning comes into existence when we use Group by. We can get sub-total of row by using the Rollup function. When result is return by Group By class first row display the grand total or we can say that the main total.
Syntax: - select firstcolumn,secondcolumn,sum(thirdcolumn) from tablename group by firstcolumn,secondcolumn with rollup order by firstcolumn.
How many records can take clustered index in SQL: A clustered index is a special type of index that reorders the way the records in the table are physically stored. Therefore the table can have only one clustered index.
What is Cascade and Restrict when we use DROP table in SQL SERVER
When we are using Drop table in SQL the syntax is simple. Drop table table_name (CASCADE / RESTRICT) we use cascade to drop table although it have some dependencies just like triggers, storedprocedure, primarykey, foreignkey it will delete first. But if we use restrict a error message is shown on using of DROP if the table have relation Trigger, storedprocedure.
Some Tips when Optimizing SQL Server 2005 Query
1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level
What is SQL Cache Dependency in ASP.NET 2.0: SQL cache dependencies are new technique in ASP.NET 2.0 which can automatically invalidate a cached data object just like a Dataset. When the related data is modified in the database. So for instance if you have a dataset which is tied up to a database tables any changes in the database table will invalidate the cached data object which can be a dataset or a data source. To enable this we need a syntax that is as follows: - aspnet_regSQL -ed -E -d Northwind.
How many types of local tables in SQL SERVER: There are 2 types of temporary tables, local and global. Local temporary tables are created using a single pound (#) sign and are visible to a single connection and automatically dropped when that connection ends. Global temporary tables are created using a double pound (##) sign and are visible across multiple connections and users and are automatically dropped when all SQL sessions stop referencing the global temporary table.
Where .NET CLR and SQL SERVER run: All .net application and SQL Server runs in same process or we can say that on same address because there is no issue of speed because if these two process are run in different process then there may be a speed issue created one process goes fast and other slow may create the problem.
What are Checkpoint in SQL Server: When we done operation on SQL SERVER that is not committed directly to the database. All operation must be logged in to Transaction Log files after that they should be done on to the main database. Checkpoint are the point which alert SQL Server to save all the data to main database if no Check point is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER. When we stop the SQL Server it will take long time because Checkpoint is also fired.
Explain integration between SQL Server 2005 and Visual Studio 2005: This integration provides wider range of development with the help of CLR for database server. Because CLR helps developers to get flexibility for developing database applications and also provides language interoperability just like Visual C++, Visual Basic .Net and Visual C# .Net. The CLR helps developers to get the arrays, classes and exception handling available through programming languages such as Visual C++ or Visual C# which is use in stored procedures, functions and triggers for creating database application dynamically and also provide more efficient reuse of code and faster execution of complex tasks. We particularly liked the error-checking powers of the CLR environment, which reduces run-time errors.
What is the difference between SQL and PL-SQL: We can get modify, Retrieve by single command or statement in SQL but PL/SQL process all SQL statements one at a time. With PL/SQL, an entire block of statements process in a single command line.SQL is structured query language, various queries are used to handle the database in a simplified manner. While PL/SQL is procedural language contains various types of variable, functions and procedures and other major difference is SQL as the name suggest it is just structured query language whereas PLSQL is a combination of Programming language & SQL.
What is the difference between UNION ALL Statement and UNION
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION, the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.
Write some disadvantage of Cursor: Cursor plays there row quite nicely but although there are some disadvantage of Cursor. Because we know cursor doing roundtrip it will make network line busy and also make time consuming methods. First of all select query grenade output and after that cursor goes one by one so roundtrip happen. Another disadvantage of cursor is there are too costly because they require lot of resources and temporary storage so network is quite busy.
What is Log Shipping and its purpose: In Log Shipping the transactional log file from one server is automatically updated in backup database on the other server and in the case when one server fails the other server will have the same DB and we can use this as the DDR(disaster recovery) plan.
Monday, December 28, 2009
SQL Server Date format
SQL Server Date format | ||
FORMAT | QUERY | SAMPLE |
1 | select convert(varchar, getdate(), 1) | 12/30/06 |
2 | select convert(varchar, getdate(), 2) | 06.12.30 |
3 | select convert(varchar, getdate(), 3) | 30/12/06 |
4 | select convert(varchar, getdate(), 4) | 30.12.06 |
5 | select convert(varchar, getdate(), 5) | 30-12-06 |
6 | select convert(varchar, getdate(), 6) | 30 Dec 06 |
7 | select convert(varchar, getdate(), 7) | Dec 30, 06 |
10 | select convert(varchar, getdate(), 10) | 12-30-06 |
11 | select convert(varchar, getdate(), 11) | 06/12/30 |
101 | select convert(varchar, getdate(), 101) | 12/30/2006 |
102 | select convert(varchar, getdate(), 102) | 2006.12.30 |
103 | select convert(varchar, getdate(), 103) | 30/12/2006 |
104 | select convert(varchar, getdate(), 104) | 30.12.2006 |
105 | select convert(varchar, getdate(), 105) | 30-12-2006 |
106 | select convert(varchar, getdate(), 106) | 30 Dec 2006 |
107 | select convert(varchar, getdate(), 107) | Dec 30, 2006 |
110 | select convert(varchar, getdate(), 110) | 12-30-2006 |
111 | select convert(varchar, getdate(), 111) | 2006/12/30 |
Time Formats | ||
8 or 108 | select convert(varchar, getdate(), 8) | 00:38:54 |
9 or 109 | select convert(varchar, getdate(), 9) | Dec 30 2006 12:38:54:840AM |
14 or 114 | select convert(varchar, getdate(), 14) | 00:38:54:840 |
You can also format the date or time without dividing characters, as well as concatenate the date and time string: | ||
Sample statement | Output | |
select replace(convert(varchar, getdate(),101),'/','') | 12302006 | |
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','') | 12302006004426 | |
Stored Procedure vs User Define Function in SQL
Stored Procedures vs. User Defined Functions
Stored Procedure:
A stored procedure is an already written SQL statement that is saved in the database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure. When you put this SQL statement in a stored procedure, you can then run the stored procedure from the database's command environment.
Stored procedure can reduced network traffic and latency, boosting application performance. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead. Stored procedures help promote code reuse. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients. Stored procedures provide better security to your data.
Create Procedure GetSalesById
(
@Id int,
@Sales money OUTPUT
)
As
SELECT @Sales = sales
FROM sample
WHERE id = @Id
Running the stored procedure:
GetSalesById.
Passing it two parameters:
@Id = 1
@Sales = (an output parameter)
It returned the value: 5000.
CREATE PROCEDURE usp_adduser
@login varchar(20),
@pswd varchar(20),
@f_name varchar(25),
@l_name varchar(35) //datatype and size should match the table definition
AS
INSERT INTO USERLIST (login, pswd, f_name, l_name)
VALUES (@login, @pswd, @f_name, @l_name)
To Execute:
exec usp_adduser 'dnelson', 'dean2003', 'Dean'
User-Defined Functions:
Microsoft has introduced the concept of User-Defined Functions that allow you to define your own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.There are three types of User-Defined functions in SQL Server 2000 and they are
1. Scalar,
2. Inline Table-Valued and
3. Multi-statement Table-valued.
2. Inline Table-Valued and
3. Multi-statement Table-valued.
Scalar User-Defined Function
A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value. Below is an example that is based in the data found in the NorthWind Customers Table.Below is an example that is based in the data found in the NorthWind Customers Table.
CREATE FUNCTION whichContinent
(@Country nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
declare @Return varchar(30)
select @return = case @Country
when 'Argentina' then 'South America'
when 'Belgium' then 'Europe'
when 'Brazil' then 'South America'
when 'Canada' then 'North America'
when 'Denmark' then 'Europe'
when 'Finland' then 'Europe'
when 'France' then 'Europe'
else 'Unknown'
end
return @return
end
Execute:
print dbo.WhichContinent('Argentina')
Because this function returns a scalar value of a varchar(30) this function could be used anywhere a varchar(30) expression is allowed such as a computed column in a table, view, a T-SQL select list item. Below are some of the examples that I was able to use after creating the above function definition.
Inline Table-Value User-Defined Function
An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.
Multi-statement Table-Value User-Defined Function
A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.
Benefits of User-Defined Functions
The benefits to SQL Server User-Defined functions are numerous. First, we can use these functions in so many different places when compared to the SQL Server stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar User-Defined Function, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User-Defined function a very powerful tool.Difference:
SQL Server user-defined functions and stored procedure offer similar functionality. Both allow you to create bundles of SQL statements that are stored on the server for future use. This offers you a tremendous efficiency benefit, as you can save programming time by:
Reusing code from one program to another, cutting down on program development time
Hiding the SQL details, allowing database developers to worry about SQL and application developers to deal only in higher-level languages Centralize maintenance, allowing you to make business logic changes in a single place that automatically affect all dependent applications
At first glance, functions and stored procedures seem identical. However, there are several subtle, yet important differences between the two:
Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.
Procedure can return zero or n values whereas function can return one value which is mandatory.
Procedures can have input, output parameters for it whereas functions can have only input parameters.
Procedure allows select as well as DML statement in it whereas function allow only select statement in it.
Functions can be called from procedure whereas procedures cannot be called from function.
Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
We can go for transaction management in procedure whereas we can't go in function.
Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.
Thursday, December 24, 2009
upload image to database (image format) in MS-Sql server and MY-SQL
MS-SQL server
create table:
create table imgtab(idd int identity(1,1), imgsrc image)
aspx.vb
Dim intImageSize As Int64
Dim strImageType As String
Dim ImageStream As Stream
' intImageSize = PersonImage.PostedFile.ContentLength
intImageSize = fup1.PostedFile.ContentLength
strImageType = fup1.PostedFile.ContentType
ImageStream = fup1.PostedFile.InputStream
Dim ImageContent(intImageSize) As Byte
Dim intStatus As Integer
intStatus = ImageStream.Read(ImageContent, 0, intImageSize)
Dim myCommand As New SqlCommand("insert into imgtab values ( @imgsamgetimage )'", Conn)
myCommand.Parameters.Add("@imgsamgetimage", SqlDbType.Image, ImageContent.Length).Value = ImageContent
myCommand.ExecuteNonQuery()
Conn.Close()
MY-SQL Server:
Create Table:
http://www.programmingado.net/c-27/a-141/Insert-blob-into-MySQL.aspx
http://bytes.com/topic/asp-net/answers/342898-how-store-retrieve-array-bytes-mysql
http://www.dotnetspider.com/forum/194010-save-retrive-image-from-to-mysql-database-using-asp-net-c-language.aspx
create table:
create table imgtab(idd int identity(1,1), imgsrc image)
aspx.vb
Dim intImageSize As Int64
Dim strImageType As String
Dim ImageStream As Stream
' intImageSize = PersonImage.PostedFile.ContentLength
intImageSize = fup1.PostedFile.ContentLength
strImageType = fup1.PostedFile.ContentType
ImageStream = fup1.PostedFile.InputStream
Dim ImageContent(intImageSize) As Byte
Dim intStatus As Integer
intStatus = ImageStream.Read(ImageContent, 0, intImageSize)
Dim myCommand As New SqlCommand("insert into imgtab values ( @imgsamgetimage )'", Conn)
myCommand.Parameters.Add("@imgsamgetimage", SqlDbType.Image, ImageContent.Length).Value = ImageContent
myCommand.ExecuteNonQuery()
Conn.Close()
MY-SQL Server:
Create Table:
Source:CREATE TABLE `cust_file` (
`id` int(11) NOT NULL auto_increment,
`customer_id` int(11) default NULL,
`filename` varchar(255) default NULL,
`filedata` blob,
`contenttype` varchar(255) default NULL,
`length` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
C#:public void Mysql_File_Save(string sConnString, int nCustId, byte[] bData, string sName, string sContentType, int nContentLength)
{
using (MySql.Data.MySqlClient.MySqlConnection oConn =
new MySql.Data.MySqlClient.MySqlConnection(sConnString))
{
oConn.Open();
MySql.Data.MySqlClient.MySqlCommand oCommand = oConn.CreateCommand();
oCommand.Connection = oConn;
//Add new
oCommand.CommandText = "insert into cust_file(customer_id, filename, filedata, contenttype, length) " +
"values( ?in_customer_id, ?in_filename, ?in_filedata, ?in_contenttype, ?in_length)";
//oCommand.CommandType = CommandType.StoredProcedure;
MySql.Data.MySqlClient.MySqlParameter oParam =
oCommand.Parameters.Add("?in_customer_id",
MySql.Data.MySqlClient.MySqlDbType.Int64);
oParam.Value = nCustId;
oParam = oCommand.Parameters.Add("?in_filename",
MySql.Data.MySqlClient.MySqlDbType.VarChar, 255);
oParam.Value = sName;
oParam = oCommand.Parameters.Add("?in_filedata",
MySql.Data.MySqlClient.MySqlDbType.Blob);
oParam.Value = bData;
oParam = oCommand.Parameters.Add("?in_contenttype",
MySql.Data.MySqlClient.MySqlDbType.VarChar, 255);
oParam.Value = sContentType;
oParam = oCommand.Parameters.Add("?in_length",
MySql.Data.MySqlClient.MySqlDbType.Int64);
oParam.Value = nContentLength;
oCommand.ExecuteNonQuery();
oConn.Close();
}
}
Anyway it allows us to write code such as:byte[] bData = FileToArray("f:\\n1jpg.jpg");
Mysql_File_Save("Database=sakila;Data Source=192.168.10.4;User id=stefan;Password=pekka",
1, bData, "N1jpg", MimeType("f:\\n1jpg.jpg"), bData.Length);
http://www.programmingado.net/c-27/a-141/Insert-blob-into-MySQL.aspx
http://bytes.com/topic/asp-net/answers/342898-how-store-retrieve-array-bytes-mysql
http://www.dotnetspider.com/forum/194010-save-retrive-image-from-to-mysql-database-using-asp-net-c-language.aspx
Thumbnail photos in gridview using handler file
thumnail.aspx
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AutoGenerateColumns="False" Visible="False" Width="50%">
<Columns>
<asp:TemplateField HeaderText="Absentees">
<ItemTemplate>
<asp:CheckBox ID="chk_absentees2" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Photo">
<ItemTemplate>
<asp:Image ID="Image1" runat="server" Height="20px"
ImageUrl='<%# "Handler_Student.ashx?id=" & Eval("stud_id") %>' Width="20px" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="stud_name" HeaderText="Name"
SortExpression="stud_name" />
<asp:BoundField DataField="stud_id" HeaderText="Student ID"
SortExpression="stud_id" />
<asp:BoundField DataField="Course" HeaderText="Course"
SortExpression="Course" />
<asp:BoundField DataField="department" HeaderText="Department"
SortExpression="department" />
<asp:BoundField DataField="stud_year" HeaderText="Year"
SortExpression="stud_year" />
</Columns>
<HeaderStyle HorizontalAlign="Left" />
</asp:GridView>
thumnail.aspx.vb
Public Sub bind_year()
str = Convert.ToString(System.DateTime.Now.ToShortDateString())
If List_dept.Visible = False Then
If List_year.SelectedIndex = -1 Then
Else
lbl_status.Visible = True
btn_absent.Visible = True
lbl_status.Text = List_course.SelectedItem.Text & " " & List_year.SelectedItem.Text & " Year Student Details."
passquery = "select stud_id,stud_name,course,department,stud_year from student_details where course='" & List_course.SelectedValue.ToString() & "' and stud_year='" & List_year.SelectedItem.Text & "' and Joined_in='" & branchid & "' and stud_id not in(select studid from student_attendance where att_date = '" + str + "')"
Call DBConnect(Conn)
Obj.Pr_Bind_Grid(Conn, passquery, GridView1, "No Details available")
If GridView1.Rows.Count = 0 Then
btn_absent.Visible = False
lbl_grid.Visible = True
End If
End If
Else
If List_dept.Text = "" Then
lbl_status.Visible = False
GridView1.Visible = False
btn_absent.Visible = False
Response.Write("<script>alert ('Select Department')</script>")
Else
If List_year.Visible = False Then
Else
If List_year.SelectedIndex = -1 Then
Else
'List_year.SelectedIndex = 0
lbl_status.Visible = True
btn_absent.Visible = True
lbl_status.Text = List_course.SelectedItem.Text & " (" & List_dept.SelectedItem.Text & ") " & List_year.SelectedItem.Text & " Year Student Details."
passquery = "select stud_id,stud_name,course,department,stud_year from student_details where course='" & List_course.SelectedValue.ToString() & "' and department='" & List_dept.SelectedValue.ToString() & "' and stud_year='" & List_year.SelectedItem.Text & "' and Joined_in='" & branchid & "' and stud_id not in(select studid from student_attendance where att_date = '" + str + "')"
Call DBConnect(Conn)
Obj.Pr_Bind_Grid(Conn, passquery, GridView1, "No Details available")
If GridView1.Rows.Count = 0 Then
btn_absent.Visible = False
lbl_grid.Visible = True
End If
End If
End If
End If
End If
GridView1.DataBind()
Conn.Close()
End Sub
Handler_Student.ashx
<%@ WebHandler Language="VB" Class="Handler_Student" %>
Imports System
Imports System.Web
Imports System.Data.SqlClient
Imports System.Configuration
Public Class Handler_Student : Implements IHttpHandler
Dim con As New SqlConnection
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
Try
Call DBConnect(con)
Dim sql As String = "Select photo from student_details where stud_id=@staff_id"
Dim cmd As New SqlCommand(sql, con)
cmd.Parameters.Add("@staff_id", System.Data.SqlDbType.VarChar, 50).Value = context.Request.QueryString("id")
cmd.Prepare()
Dim dr As SqlDataReader = cmd.ExecuteReader()
dr.Read()
context.Response.BinaryWrite(DirectCast(dr("photo"), Byte()))
Catch ex As Exception
End Try
End Sub
Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property
End Class
Thursday, December 17, 2009
Regular Expression in asp.net
This table lists the regular expressions which were used in this sample code, the data type each is intended to recognize, and some examples of strings that each regular expression accepts (matches) and rejects (does not match).
| data type | regular expression | accepts | rejects |
| whitespace | /^\s+$/ | spaces, tabs, etc. | 0 a |
| single letter | /^[a-zA-Z]$/ | a A | 0 . - |
| alphabetic string | /^[a-zA-Z]+$/ | aAbB | a1 23 |
| single digit | /^\d/ | 0 | 00 1a |
| single letter or digit | /^([a-zA-Z]|\d)$/ | 0 a | 00 1a |
| alphanumeric string | /^[a-zA-Z0-9]+$/ | a1b2 | 2.5 1.a |
| integer | /^\d+$/ | 0 23 | 23.5 |
| signed integer | /^(+|-)?\d+$/ | -1 +12 | -1.1 |
| floating point number | /^((\d+(\.\d*)?)|((\d*\.)?\d+))$/ | 1.1 .9 8. | -1.1 . |
| signed float | /^(((+|-)?\d+(\.\d*)?)|((+|-)?(\d*\.)?\d+))$/ | -1.1 1.1 .9 8. | 1a |
| email address | /^.+\@.+\..+$/ | a@b.c | a@b |
JavaScript
Javascript function to check textbox empty.
function UnamePwdValidation()
{
if (document.getElementById("<%=Txt_UserName.ClientID%>").value=="")
{
alert("Enter Username");
document.getElementById("<%=Txt_UserName.ClientID%>").focus();
return false;
}
if(document.getElementById("<%=Txt_Password.ClientID %>").value=="")
{
alert("Enter Password");
document.getElementById("<%=Txt_UserName.ClientID %>").focus();
return false;
}
}
< asp:ImageButton ID="Submit" OnClientClick="return UnamePwdValidation()" runat="server" ImageUrl="images\Go.jpg" TabIndex="3" />
Javascript function to allow only alphabets (lower and upper case).
function AllowCharacterOnly(myevent)
{
var Val = (myevent.which) ? myevent.which : myevent.keyCode
if ((Val > 64 && Val <> 96 && Val < type="text" id="txtChracter" onkeypress="return AllowCharacterOnly(event)" style="color: rgb(255, 102, 0); font-weight: bold;">Javascript to allow user to enter only numeric value.
function AllowNumericValue(myevent)
{
var charVal = (myevent.which) ? myevent.which : myevent.keyCode
if (charVal > 31 && (charVal <> 57))
{
return false;
}
return true;
}
input type="text" id="Text2" onkeypress="return AllowNumericCharacters(event)"
Javascript function to show an alert if user entered a non numeric value.
function ValidationCheckforNumeric(txtVal)
{
var len = txtVal.value.length;
var charVal = 0;
for (var i=0;i
{
charVal = txtVal.value.charCodeAt(i);
if (!((charVal >= 48 && charVal <= 57))) { alert("Only Numeric Values are Allowed"); txtVal.value = ''; txtVal.focus(); return false; } } return true; } input type="text" id="txtNumber" onchange='ValidationCheckforNumeric(this)' Javascript code for checking null value in Textbox.
function CheckForEmptyTextbox()
{
var s;
var txtValue;
txtValue = document.getElementById('TextBox1');
if (txtValue.value == '')
{
s = confirm('TextBox1 is blank, do you want to continue?');
return s;
}
return true;
}
Button1.Attributes.Add("onclick", "javascript: CheckForEmptyTextbox();");
On button click - how to bring up browsers emailing box using javascript.
INPUT TYPE="button" VALUE="Send Email." onClick = "parent.location= 'mailto:mymailid@gmail.com'"
Show Timer on status bar using Javascript
function showtime ()
{
var now = new Date();
var hours = now.getHours();
var minutes = now.getMinutes();
var seconds = now.getSeconds()
var timeValue = "" + ((hours >12) ? hours -12 :hours)
timeValue += ((minutes <>= 12) ? " P.M." : " A.M."
window.status = timeValue;
}
Call this function on load of the form.
Check the browser type using javascript.
if(navigator.appName=="Netscape")
{
alert("Browser is Netscape Navigator.");
}
else if (navigator.appName == "Microsoft Internet Explorer")
{
alert("Browser is Microsoft Internet Explorer.");
}
else
{
alert("Neither Netscape Navigator nor Internet Explorer.");
}
Set Control Focus using Javascript code.
body onload="document.forms[0]['TextBox1'].focus();"
PopUp Window
window.open('pop_up.html', 'popup', 'toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=no,resizable=no,copyhistory=no,width=200,height=130')
Cookies (JavaScript)
http://www.pages.org/javascript/index.html
function UnamePwdValidation()
{
if (document.getElementById("<%=Txt_UserName.ClientID%>").value=="")
{
alert("Enter Username");
document.getElementById("<%=Txt_UserName.ClientID%>").focus();
return false;
}
if(document.getElementById("<%=Txt_Password.ClientID %>").value=="")
{
alert("Enter Password");
document.getElementById("<%=Txt_UserName.ClientID %>").focus();
return false;
}
}
< asp:ImageButton ID="Submit" OnClientClick="return UnamePwdValidation()" runat="server" ImageUrl="images\Go.jpg" TabIndex="3" />
Javascript function to allow only alphabets (lower and upper case).
function AllowCharacterOnly(myevent)
{
var Val = (myevent.which) ? myevent.which : myevent.keyCode
if ((Val > 64 && Val <> 96 && Val < type="text" id="txtChracter" onkeypress="return AllowCharacterOnly(event)" style="color: rgb(255, 102, 0); font-weight: bold;">Javascript to allow user to enter only numeric value.
function AllowNumericValue(myevent)
{
var charVal = (myevent.which) ? myevent.which : myevent.keyCode
if (charVal > 31 && (charVal <> 57))
{
return false;
}
return true;
}
input type="text" id="Text2" onkeypress="return AllowNumericCharacters(event)"
Javascript function to show an alert if user entered a non numeric value.
function ValidationCheckforNumeric(txtVal)
{
var len = txtVal.value.length;
var charVal = 0;
for (var i=0;i
{
charVal = txtVal.value.charCodeAt(i);
if (!((charVal >= 48 && charVal <= 57))) { alert("Only Numeric Values are Allowed"); txtVal.value = ''; txtVal.focus(); return false; } } return true; } input type="text" id="txtNumber" onchange='ValidationCheckforNumeric(this)' Javascript code for checking null value in Textbox.
function CheckForEmptyTextbox()
{
var s;
var txtValue;
txtValue = document.getElementById('TextBox1');
if (txtValue.value == '')
{
s = confirm('TextBox1 is blank, do you want to continue?');
return s;
}
return true;
}
Button1.Attributes.Add("onclick", "javascript: CheckForEmptyTextbox();");
On button click - how to bring up browsers emailing box using javascript.
INPUT TYPE="button" VALUE="Send Email." onClick = "parent.location= 'mailto:mymailid@gmail.com'"
Show Timer on status bar using Javascript
function showtime ()
{
var now = new Date();
var hours = now.getHours();
var minutes = now.getMinutes();
var seconds = now.getSeconds()
var timeValue = "" + ((hours >12) ? hours -12 :hours)
timeValue += ((minutes <>= 12) ? " P.M." : " A.M."
window.status = timeValue;
}
Call this function on load of the form.
Check the browser type using javascript.
if(navigator.appName=="Netscape")
{
alert("Browser is Netscape Navigator.");
}
else if (navigator.appName == "Microsoft Internet Explorer")
{
alert("Browser is Microsoft Internet Explorer.");
}
else
{
alert("Neither Netscape Navigator nor Internet Explorer.");
}
Set Control Focus using Javascript code.
body onload="document.forms[0]['TextBox1'].focus();"
PopUp Window
window.open('pop_up.html', 'popup', 'toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=no,resizable=no,copyhistory=no,width=200,height=130')
Cookies (JavaScript)
http://www.pages.org/javascript/index.html
Subscribe to:
Posts (Atom)