Differences Between Stored Procedure and User Defined Function

The Differences Between a Stored Procedure and a User Defined Function are :

1. User Defined Function Can be executable from SQL Select statement where as Stored Procedure cannot be executable from SQL select statement. We have to use execute Or Exec Keyword to run the Stored Procedure.
2. Stored Procedure Supports Output Parameters where as User Defined Function does not Return Output Parameter.
3. Stored Procedure Can’t return a Table variable while the User Defined Function Can.
4. Stored Procedure Can’t Join to a stored Procedure while a user defined function can join to a UDF.
5. Stored procedure can be used to change some of Server Environment & Operating Environment where as this is not possible in case of User Defined Function.
6. T-SQL ignores the Errors and Processes next statement in case of stored Procedure But it stops the process in case of User Defined function.
7. Stored Procedure can be used in XML for Clause while Used defined function cannot.

Split value Comma Separated String in to Separate records SQL SERVER

/******  [dbo].[Split] Script  ******/

CREATE FUNCTION [dbo].[Split]
(
@RowData NVARCHAR(MAX),
@Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE
(
ID INT IDENTITY(1,1),
Data NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @Iterator INT
SET @Iterator = 1

DECLARE @FoundIndex INT
SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)

WHILE (@FoundIndex>0)
BEGIN
INSERT INTO @RtnValue (data)
SELECT
Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))

SET @RowData = SUBSTRING(@RowData,
@FoundIndex + DATALENGTH(@Delimeter) / 2,
LEN(@RowData))

SET @Iterator = @Iterator + 1
SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
END

INSERT INTO @RtnValue (Data)
SELECT Data = LTRIM(RTRIM(@RowData))

RETURN
END

***************************************************************
use :

SELECT ID , Data FROM dbo.Split('manish,chetan,bala,nitesh,nital',',')

Frequently Asked Interview Questions And Answers SQL SERVER

What is a Database?                                                          A database is a structured collection of information that is organized so that it can easily be accessed, managed, and updated. The information stored in a database has to continue to exist even after the application that saved and manipulated the data have ceased to run.

What is a Relational Database?                                             A Relational Database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. Each table contains one or more data categories in columns. Each row contains a unique instance of data for the categories defined by the columns.

What is DBMS ?                                                                       DBMS is a set of Prewritten programs that controls theorganization, storage, management, and retrieval of data in adatabase. DBMS are categorized according to their data structures or types. The DBMS accepts requests for data from the application program and instructs the operating system to transfer the appropriate data.

What is RDBMS ?                                                                     An RDBMS is a Relational Data Base Management System. which adds the additional condition that the system supports a tabular structure for the data with enforced relationships between the tables. Database in which all data is stored in Relations which are tables, with rows and columns. Each table is composed of records (called Tuples) and each recordis identified by a field (called attribute) containing a uniquevalue. Every table shares at least one field with another table in 'one to one,' 'one to many,' or 'many to many' relationships. These relationships allow the database users to access the data in almost an unlimited number of ways, and to combine the tables as building blocks to create complex and very largedatabases.

What is the Difference between DBMS and RDBMS?

DBMS	RDBMS
A Set of Prewritten Programs dedicated to control the data storage	A DBMS which Supports Tabular structure of Database. Only way to view the data is as a set of tables
Entity Based where all the tables are treated as different entities	Relation Based where tables are dependent and the user can establish various integrity constraints on these tables, so that the ultimate data used by the user remains correct
Single User system, Only one user can access one database at a time.	Multi-User system, Multiple users simultaneously access the same database
DBMS does not impose any constraints or security with regard to data manipulation It is the user's or programmer's  responsibility to ensure the ACID Properties of the database	RDBMS is more Secure than DBMS by defining the integrity constraints for the purpose of holding ACID Properties
Does not necessarily Support Client/Server Architecture & Distributed Databases	Supports Client/Server Architecture & Distributed Databases
Examples: FoxPro data files and earlier Ms Access	Examples: ORACLE, SQL Server and DB2

What is Normalization?                                                           ARelational database is basically composed of tables that contains Related data. So the Process of organizing this Related data into tables is referred to as Normalization. Alternatively, The process of refining tables, keys, columns, and relationships to create an efficient database is calledNormalization.

What are the advantages of Normalization ?                 Normalization Used to reduce Data Redundancy and permits Simple and rapid retrieval of all necessary information. Some of the benefits of Normalization are:                           

• Data Integrity (As there is No Redundant Data)
• Optimized Queries (As Normalized tables produce rapid and efficient  Joins)                                               
• Faster Index creation and sorting (As the tables have fewer   Columns)                                      
• Faster UPDATE Performance (As there are fewer indexes per table)                                       
• Improved Concurrency(As table locks will affect less Data)
• Eliminates Redundancy

What are the Disadvantages of Normalization ?             As a result of Normalization, Data is stored in multiple tables.To retrieve or modify the information, we have to establish joins across multiple tables.Joins are expensive from I/O standpoint.  Also, Multi table Joins can have adverse impact on the performance of the system.

What are Different levels of Normalization?                    There are a few rules for database normalization. Each rule is called a "Normal form." If the first rule is observed, the database is said to be in First Normal Form. Similarly, If the first three rules are observed, then the database is considered to be in Third Normal Form. Although other levels of normalization are possible, Third normal form is considered to be the highest level necessary for most applications.

First Normal Form(1NF):    Remove Repetitive groups  from Record Design.For this,Create a separate Table for each set of Related data and Identify each set of Related data with a primary key.

Second Normal Form(2NF):  Each and every field is Functionally Dependant on Primary Key. Here, Create separate tables for sets of values that apply to multiple records and Relate these tables with a foreign key. Records should not depend on anything other than a table's primary key. 

Third Normal Form(3NF): Transitive dependencies are removed from Record Design. For this,Eliminate fields that do not depend on the key.

Boyce-Codd Normal Form(BCNF): A relation is in Boyce/Codd Normal Form if and only if the only determinants are candidate key. (A determinant is any attribute on which some other attribute is (fully) functionally dependent.)

4th Normal Form(4NF): A table is in 4NF if it is in BCNF and if it has No multi-valued dependencies. 

5th Normal Form(5NF or PJNF): A table is in 5NF, also called "Projection-Join Normal Form", if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.

Domain/key Normal Form(DKNF): A key uniquely identifies each row in a table. A domain is the set of permissible values for an attribute. By enforcing key and domain restrictions, the database is assured of being freed from modification anomalies. DKNF is the normalization level that most designers aim to achieve.

How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is DeNormalization and when would you go for it?DeNormalization is the Reverse process of Normalization. It allows the designers to keep redundant data in the system. This improves the query performance as the number of joins could be reduced. We will go for the DeNormalization, where the data does not change much frequently.

What are all the Basic things we need to observe before DeNormalizing a Database?                                                

• Be sure that we have a good Over all understanding of logical design of system
• Don’t make an attempt to DeNormalize entire Database at a time
• Determine whether we need virtual columns
• Understand Data Integrity issues & storage techniques for the data
• Determine the frequency at which data might change

What are the Essential DeNormalization Techniques?Keeping Redundant Data                                            Using Virtual Columns                                              Performing Horizontal Data Partition                     Performing Vertical Data Partition

What are Constraints? Explain Different types of Constraints?                                                                         Constraints enable the RDBMS to enforce the integrity of the database automatically, without creating the triggers, rule or defaults. Again,Constraints can defined as Restrictions Placed either at a Column level or Table level which ensures that the data meets certain Data integrity rules

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY.

What is  Entity Integrity Constraint?                              Entity Integrity Constraint ensures that each and every row should have a unique value for a column or combination of columns.

What is  Domain Integrity Constraint?                              Domain Integrity Constraint refers to a pool of Data, for a column of a table, from which the column will take its values.

Define Candidate key, Primary Key,  Alternate key and Composite key.                                                                        A Candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest all are called alternate keys. A key formed by combining at least two or more columns is called composite key.

Define Foreign key                                                      When Primary key of one table appears as an attribute in another table, then it is called Foreign key in the second table.

What is the Referential Integrity? Referential integrity refers to the consistency that must be maintained between primary and foreign keys. Referential Integrity ensures that for every value of foreign key, there is a matching value of primary key.

What is the Difference between a Primary Key and a Unique Key?                                                                              Both primary key and Unique key enforce uniqueness of the column on which they are defined. The differences between these two are as shown below:

Primary Key	Unique Key
Does not  Allow Null Values	Allows Null Values
Only one Primary key exists in a table	More than one Unique key exists in a table
Creates a Clustered index on the column	Creates a Non Clustered Index on the column

What is DDL, DML, DCL and TCL Commands.               Data Definition Language(DDL): Data Definition Language statements are used to define the database structure or schema.
Ex : CREATE,ALTER,DROP,TRUNCATE,RENAME

Data Manipulation Language(DML) :  Data Manipulation Language statements are used for manipulate or manage the data within the schema objects
Ex : INSERT,UPDATE,DELETE

Data Control Language(DCL) :  Data Control Language statements are used to create roles, permissions, and referential integrity
Ex : GRANT,REVOKE

Transaction Control Language(TCL) : Transaction Control Language statements are used to manage different transactions occurring within a database.
Ex : COMMIT,SAVEPOINT,ROLLBACK

What is a Transaction?                                                             Transaction can be defined as a logical unit of work a grouping of one or more DBMS commands that are treated as a whole.

What are ACID Properties.                                                   Atomicity                                                      Consistency                                                        Isolation                                                              Durability

Explain Different Isolation Levels                                      An isolation level determines the degree of isolation of data between concurrent transactions.                                The Isolation levels are :

• Read Committed
• Read Uncommitted
• Repeatable Read
• Serializable

What are the Different Data types  in SQL Server ?Different types of Data types :                                 Exact & Approximate Numerics :                           TinyInt, SmallInt, Int, BigInt, Numeric, Decimal, Float,Real,Bit

Character & Unicode Character Strings :                     Char, Varchar, text, nchar, nVarchar, ntext                     

Binary Strings :                                                           Binary, varbinary, Image                                                

Date and time :                                         SmallDatetime, Datetime                                            

Monetary :                                                            SmallMoney, Money                                               

Other Data Types :                                               Cursor, unique identifier, Timestamp, Rowversion, Sysname, table, sql_variant, xml,User Defined Data type

How many bytes will TinyInt, SmallInt, Int and BigInt  Occupy?                                                                                TinyInt, SmallInt, Int and BigInt will Occupy 1,2,4,8 bytes  respectively.

What is the Difference between Numeric and Decimal?    In general,There is no difference between Numeric and Decimal. we can use both of them interchangeably to store integer and floating-point numbers scaled from 1 to 38 places, inclusive of both sides of a decimal. Generally we will use this data type to control the accuracy of our calculations in terms of the number of decimal digits.

What is the Difference between Float and Real?           The only difference between float and real data types are their minimum and maximum values and their storage values. Float ranges from -1.79E + 308 to 1.79 + 308, while real ranges from     -3.40E + 38 to 3.40E + 38. The storage size of Float is 8 bytes while that of Real it is 4 bytes. The real data type is the same as float(24) — a floating data type with 24 digits to the right of the decimal point.

What is a Bit data type?                                                   Bit is used to store the logical information like True/False or  Yes/No. These Information will be stored as an Integer data type that can take a value of 1, 0, or NULL. Bit Occupies 1 byte for every 8 bit columns.

Difference Between Char/Varchar/nvarchar datatype?Char[(n)]     - Fixed-length, Non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes.
Varchar[(n)] - Variable-length, Non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length.                                                            nvarchar(n)  - Variable-length, Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length.                                                                     

What is the Difference between text and Image Data type?
Individual text, ntext, and image values can be a maximum of 2GB, which is too long to store in a single data row.All that is stored in the row is not the actual data but it is a pointer to the database pages that contain the Actual data. Coming to the difference between text and Image, Text data type is used for character data for storing more than 8000 characters. Image is used for binary large objects (BLOBs) such as digital images.

Name the Datatype which cannot be assigned to a table column.                                                              Cursor data type is the only system data type that cannot be assigned to a table column.

What is a Cursor? Is it a Data type or database object? 
Cursor data type is the only system data type that cannot be assigned to a table column in a CREATE TABLE statement. It can be used only with variables and stored procedure output parameters that contain a reference to a cursor. That means,  It is either declared with the FOR clause that will bind it to a SELECT statement or it will act as an alias for another cursor, with the SET statement.

What is UniqueIdentifier Data type?                            The UniqueIdentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites

What is a Table Data type?
The Table data type is an alternative way to create a temporary table. The variable declaration requires the column definition for the table and it creates a temporary table in tempdb.Table datatype is used to store a result set for later processing. We cannot use this datatype for a column in a CREATE TABLE statement. You should use DECLARE @local_variable to declare variables of type table. Table variables should be used instead of temporary tables, whenever possible, because table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined and these table variables require less locking and logging resources.

What is the difference between Timestamp and Rowversion?                                                                   Timestamp and Rowversion are two different names for the exact same data type. Timestamp is the synonym for the Rowversion data type. Microsoft deprecated timestamp data type and replaced the same with Rowversion In SQL 2005.In general These types has nothing at all to do with dates or times.Rowversion Is a data type that exposes automatically generated, unique binary numbers within a database and generally used as a mechanism for version-stamping table rows.The storage size is 8 bytes.

What is the difference between UniqueIdentifier  and Identity?

TIMESTAMP	UNIQUEIDENTIFIER
Size of TIMESTAMP value is 8 bytes	size of UNIQUEIDENTIFIER is 16 bytes
not based on system date or time	based on the computer's MAC addresses and system date time.
purpose to TIMESTAMP is to track the operation in tables on the database level	purpose is to have a unique value assigned to a row. It remains unique in any system in the world.
It should not be a primary key	it may be a primary key

What is SQL_variant Data type?                              Sql_variant data type is used to store values of various SQL Server supported data types, except text, ntext, timestamp, and sql_variant. The maximum length of sql_variant data type is 8016 bytes. You can store in one column of type sql_variant, the rows of different data types. For example in the Address column of sql_variant data type, we can store int, bigint, char and varchar values.

What is User defined data type?                                 User defined Data type can be used to create custom data types that are based on system data types using the system stored procedure sp_addtype.

What is Sysname datatype?                                              sysname data type is used for table columns, variables, and stored procedure parameters that store object names.Sysname is functionally the same as nvarchar(128) except that, sysname is NOT NULL.

What are the Different Types of Database Objects?Table, View, Procedure, User Defined Function, Trigger, Rule, Check Constraint, Default Constraint, PRIMARY KEY  Constraint, FOREIGN KEY Constraint, UNIQUE Constraint, Synonym, System Base Table

List all Specified Week Days Between two given Dates SQL SERVER

Declare @StartDate Datetime, @EndDate Datetime

Set @StartDate = '01/01/2009' Set @EndDate = '01/30/2009';

WITH WeekDay(FirstDay,NextDay) AS
(SELECT @StartDate AS StartDate,                      DATEADD(d,1,@StartDate) AS NextDay
UNION ALL
SELECT NextDay AS FirstDay,                                  DATEADD(D,1,NextDay) AS NextDay FROM WeekDay
WHERE NextDay BETWEEN @StartDate AND @EndDate)

SELECT FirstDay 'Sundays' FROM WeekDay WHERE  DATENAME(DW,FirstDay) = 'Sunday'

Count the Comma Separated Values in SQL SERVER

this is usefull when you write data like(abc,zxy)in table

SELECT

      LEN(field name) - LEN(REPLACE(field name, ',', ''))+1 total

From [your table name]

SELECT

      LEN(word) - LEN(REPLACE(word, ',', ''))+1 total

From dbo.test

Set Expiration Values for ASP.NET Page Caching

c#

Response.Cache.SetExpires(DateTime.Now.AddSeconds(60));
Response.Cache.SetCacheability(HttpCacheability.Public);
Response.Cache.SetValidUntilExpires(true);

vb

Response.Cache.SetExpires(DateTime.Now.AddSeconds(60))
Response.Cache.SetCacheability(HttpCacheability.Public)
Response.Cache.SetValidUntilExpires(True) 

very useful htaccess tips & tricks

Apache web servers has a great way to manipulate information using .htaccess files. .htaccess (hypertext access) is the default name of a directory-level configuration file that allows for decentralized management of web server configuration. The .htaccess file is placed inside the web tree, and is able to override a subset of the server’s global configuration; the extent of this subset is defined by the web server administrator. The original purpose of .htaccess was to allow per-directory access control (e.g. requiring a password to access the content), hence the name. Nowadays .htaccess can override many other configuration settings, mostly related to content control, e.g. content type and character set, CGI handlers, etc.

Following are few very useful htaccess tricks.

 

 

1. Custom Directory Index Files

DirectoryIndex index.html index.php index.htm

You can change a default index file of directory by using above snippet in your htaccess file. If a user request /foo/, Apache will serve up /foo/index.html, or whatever file you specify.

2. Custom Error Pages

ErrorDocument 404 errors/404.html

You may want to redirect your users to an error page is any of the http errors like 404 occurs. You can use above snippet in htaccess file to map 404 error to error page errors/404.html. Also you may want to write a common page for all the http errors as follows:

ErrorDocument 404 /psych/cgi-bin/error/error?404

3. Control access at files & directory level

.htaccess is most often used to restrict or deny access to individual files and folders. A typical example would be an “includes” folder. Your site’s pages can call these included scripts all they like, but you don’t want users accessing these files directly, over the web. In that case you would drop an .htaccess file in the includes folder with content something like this.

# no one gets in here! deny from all

which would deny ALL direct access to ANY files in that folder. You can be more specific with your conditions, for instance limiting access to a particular IP range, here’s a handy top-level rule for a local test server.

# no nasty crackers in here! order deny,allow deny from all allow from 192.168.0.0/24 # this would do the same thing.. #allow from 192.168.0

Generally these sorts of requests would bounce off your firewall anyway, but on a live server they become useful for filtering out undesirable IP blocks, known risks, lots of things.
Sometimes, you will only want to ban one IP, perhaps some persistent robot that doesn’t play by the rules.

# someone else giving the ruskies a bad name.. order allow,deny deny from 83.222.23.219 allow from all

4. Modifying the Environment Variable

Environment variables contain information used by server-side includes and CGI. Set / Unset environment variables using SetEnv and UnSetEnv.

SetEnv SITE_WEBMASTER "Jack Sprat" SetEnv SITE_WEBMASTER_URI mailto:Jack.Sprat@characterology.com UnSetEnv REMOTE_ADDR

5. 301 Redirect using htaccess

If you want to redirect from an old document to new:

Redirect 301 /old/file.html http://yourdomain.com/new/file.html

Use following for redirecting Entire Directory.

RedirectMatch 301 /blog(.*) http://yourdomain.com/$1

6. Implementing a Caching Scheme with .htaccess

Cache the static files and improve your website’s performance.

# year <FilesMatch "\.(ico|pdf|flv|jpg|jpeg|png|gif|swf|mp3|mp4)$"> Header set Cache-Control "public" Header set Expires "Thu, 15 Apr 2010 20:00:00 GMT" Header unset Last-Modified </FilesMatch> #2 hours <FilesMatch "\.(html|htm|xml|txt|xsl)$"> Header set Cache-Control "max-age=7200, must-revalidate" </FilesMatch> <FilesMatch "\.(js|css)$"> SetOutputFilter DEFLATE Header set Expires "Thu, 15 Apr 2010 20:00:00 GMT" </FilesMatch>

7. Compress output using GZIP

Add following snippet into your htaccess file and compress all the css, js, html files with GZip compression.

<IfModule mod_gzip.c>     mod_gzip_on       Yes     mod_gzip_dechunk  Yes     mod_gzip_item_include file      \.(html?|txt|css|js|php|pl)$     mod_gzip_item_include handler   ^cgi-script$     mod_gzip_item_include mime      ^text/.*     mod_gzip_item_include mime      ^application/x-javascript.*     mod_gzip_item_exclude mime      ^image/.*     mod_gzip_item_exclude rspheader ^Content-Encoding:.*gzip.* </IfModule>

Above code works only if mod_gzip module is enabled in your webserver. You may want to add following snippet if your webserver provides mod_deflate support.

<Location>     SetOutputFilter DEFLATE       SetEnvIfNoCase Request_URI  \         \.(?:gif|jpe?g|png)$ no-gzip dont-vary     SetEnvIfNoCase Request_URI  \         \.(?:exe|t?gz|zip|gz2|sit|rar)$ no-gzip dont-vary </Location>

If your webserver does not support mod_deflate then you may want to use following snippet.

<FilesMatch "\.(txt|html|htm|php)">     php_value output_handler ob_gzhandler </FilesMatch>

8. Redirect browser to https (ssl)

Add following snippet to your htaccess and redirect entire website to https.

RewriteEngine On RewriteCond %{HTTPS} !on RewriteRule (.*) https://%{HTTP_HOST}%{REQUEST_URI}

9. Rewrite URLs using htacccess

Rewriting product.php?id=12 to product-12.html

RewriteEngine on RewriteRule ^product-([0-9]+)\.html$ product.php?id=$1

Rewriting product.php?id=12 to product/ipod-nano/12.html

RewriteEngine on RewriteRule ^product/([a-zA-Z0-9_-]+)/([0-9]+)\.html$ product.php?id=$2

Redirecting non www URL to www URL

RewriteEngine On RewriteCond %{HTTP_HOST} ^viralpatel\.net$ RewriteRule (.*) http://www.viralpatel.net/$1 [R=301,L]

Rewriting yoursite.com/user.php?username=xyz to yoursite.com/xyz

RewriteEngine On RewriteRule ^([a-zA-Z0-9_-]+)$ user.php?username=$1 RewriteRule ^([a-zA-Z0-9_-]+)/$ user.php?username=$1

Redirecting the domain to a new subfolder of inside public_html

RewriteEngine On RewriteCond %{HTTP_HOST} ^test\.com$ [OR] RewriteCond %{HTTP_HOST} ^www\.test\.com$ RewriteCond %{REQUEST_URI} !^/new/ RewriteRule (.*) /new/$1

10. Prevent Directory Listing

Add any of the following snippet to avoid directory listing.

Options -Indexes

or

IndexIgnore *

11. Adding new MIME types

The type of file depends on the filename extension. Unrecognized file extensions are treated as text data, and corrupted on download.

AddType application/x-endnote-connection enz AddType application/x-endnote-filter enf AddType application/x-spss-savefile sav

12. Deny access to static file data

Denies any request for static files (images, css, etc) if referrer is not local site or empty.

RewriteCond %{HTTP_REFERER} !^$ RewriteCond %{REQUEST_URI} !^/(wp-login.php|wp-admin/|wp-content/plugins/|wp-includes/).* [NC] RewriteCond %{HTTP_REFERER} !^http://www.askapache.com.*$ [NC] RewriteRule \.(ico|pdf|flv|jpg|jpeg|mp3|mpg|mp4|mov|wav|wmv|png|gif|swf|css|js)$ - [F,NS,L]

13. Specify Upload file limit for PHP in htaccess

php_value upload_max_filesize 20M php_value post_max_size 20M php_value max_execution_time 200 php_value max_input_time 200

In the above .htaccess file, uploading capability is increased by the four parameter first one is maximum file size for uploading, second one is maximum size of the post data , third one is maximum time in seconds a script is allowed to run before it is terminated by the parser and last one is maximum time in seconds a script is allowed to parse input data such as like file uploads, POST and GET data.

14. Disallow Script Execution

Options -ExecCGI AddHandler cgi-script .php .pl .py .jsp .asp .htm .shtml .sh .cgi

15. Change Charset and Language headers

AddDefaultCharset UTF-8 DefaultLanguage en-US

16. Set Timezone of the Server (GMT)

SetEnv TZ America/Indianapolis

17. Force “File Save As” Prompt

AddType application/octet-stream .avi .mpg .mov .pdf .xls .mp4

18. Protecting a single file

Normally .htaccess applies to the entire directory. With the directive you can restrict it to specific files:

<Files quiz.html> order deny,allow deny from all AuthType Basic AuthName "Characterology Student Authcate" AuthLDAP on AuthLDAPServer ldap://directory.characterology.com/ AuthLDAPBase "ou=Student, o=Characterology University, c=au" require valid-user satisfy any </Files>

19. Set Cookie using htaccess

Set Cookie with environment variable

Header set Set-Cookie "language=%{lang}e; path=/;" env=lang

Set Cookie based on Request. This code sends the Set-Cookie header to create a cookie on the client with the value of a matching item in 2nd parentheses.

RewriteEngine On RewriteBase / RewriteRule ^(.*)(de|es|fr|it|ja|ru|en)/$ - [co=lang:$2:.yourserver.com:7200:/]

20. Send Custom Headers

Header set P3P "policyref=\"http://www.askapache.com/w3c/p3p.xml\"" Header set X-Pingback "http://www.askapache.com/xmlrpc.php" Header set Content-Language "en-US" Header set Vary "Accept-Encoding"

21. Blocking request based on User-Agent Header

SetEnvIfNoCase ^User-Agent$ .*(craftbot|download|extract|stripper|sucker|ninja|clshttp|webspider|leacher|collector|grabber|webpictures) HTTP_SAFE_BADBOT SetEnvIfNoCase ^User-Agent$ .*(libwww-perl|aesop_com_spiderman) HTTP_SAFE_BADBOT Deny from env=HTTP_SAFE_BADBOT