14 Rules for Faster-Loading Web Sites

• These rules are the key to speeding up your web pages. They've been tested on some of the most popular sites on the Internet and have successfully reduced the response times of those pages by 25-50%. The key insight behind these best practices is the realization that only 10-20% of the total end-user response time is spent getting the HTML document to the browser. You need to focus on the other 80-90% if you want to make your pages noticeably faster. These rules are the best practices for optimizing the way servers and browsers handle that 80-90% of the user experience.
   
• Rule 1 - Make Fewer HTTP Requests
• Rule 2 - Use a Content Delivery Network
• Rule 3 - Add an Expires Header
• Rule 4 - Gzip Components
• Rule 5 - Put Stylesheets at the Top
• Rule 6 - Put Scripts at the Bottom
• Rule 7 - Avoid CSS Expressions
• Rule 8 - Make JavaScript and CSS External
• Rule 9 - Reduce DNS Lookups
• Rule 10 - Minify JavaScript
• Rule 11 - Avoid Redirects
• Rule 12 - Remove Duplicate Scripts
• Rule 13 - Configure ETags
• Rule 14 - Make AJAX Cacheable

Date Condition in Case statement sql Server

SELECT

        C.CategoryID ,

        CategoryName ,

        CASE WHEN ABS(DATEDIFF(W,GETDATE(),R.CreatedDate)) <= 5  THEN 'New' ELSE                         '' END AS 'NewReq' ,

        CategoryFolderName  

FROM Master.Catagory  C

Difference between SQL Server 2008 and SQL Server 2012

S.No	SQL Server 2008	SQL Server 2012
1	Maximum number of  concurrent connections:  The Maximum number of concurrent connections to SQL Server 2008 is 32767.	Maximum number of  concurrent connections:   SQL server 2012 has unlimited concurrent connections.
2	Precision used for spatial calculations: The SQL Server 2008 uses 27 bit bit precision for spatial calculations.	Precision used for spatial calculations:  The SQL Server 2012 uses 48 bit precision for spatial calculations
3	TRY_CONVERT() and FORMAT() functions:   TRY_CONVERT() and FORMAT() functions are not available in SQL Server 2008	TRY_CONVERT() and FORMAT() functions:    TRY_CONVERT() and FORMAT() functions are newly included in SQL Server 2012
4	ORDER BY Clause with  OFFSET / FETCH options:  ORDER BY Clause does not have OFFSET / FETCH options as in SQL Server 2012	ORDER BY Clause with OFFSET / FETCH options:  ORDER BY Clause now have OFFSET / FETCH options to use paging to show required rows per page in applications and allow the user to scroll through each page of results rather than download the entire set In the sample query below, SQL Server would return 10 records beginning with record 11. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time. SELECT BusinessEntityID, FirstName, LastName FROM Person.Person ORDER BY BusinessEntityID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
5	Code Name:  SQL Server 2008 is code named as Katmai.	Code Name:  SQL Server 2012 is code named as Denali
	In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition.	In SQL Server 2012,support for server auditing is expanded to include all editions of SQL Server.
7	Sequence Object:  Sequence is not available in SQL Server 2008	Sequence Object:  Sequence is included in SQL Server 2012.Sequence is a user defined object that generates a sequence of a number. Here is an example using Sequence. /****** Create Sequence Object ******/ CREATE SEQUENCE MySequence START WITH 1 INCREMENT BY 1; /****** Create Temp Table ******/ DECLARE @Person TABLE ( ID int NOT NULL PRIMARY KEY, FullName nvarchar(100) NOT NULL ); /****** Insert Some Data ******/ INSERT @Person (ID, FullName) VALUES (NEXT VALUE FOR MySequence, 'Umar Ali'), (NEXT VALUE FOR MySequence, 'John Peter'), (NEXT VALUE FOR MySequence, 'Mohamed Iqbal'); /****** Show the Data ******/ SELECT * FROM @Person; The results would look like this: ID FullName 1 Umar Ali 2 John Peter 3 Mohamed Iqbal
8	Full Text Search Capability:  The Full Text Search in SQL Server 2008 does not allow us to search and index data stored in extended properties or metadata.	Full Text Search Capability:  The Full Text Search in SQL Server 2012 has been enhanced by allowing us to search and index data stored in extended properties or metadata. Consider a PDF document that has "properties" filled in like Name, Type, Folder path, Size, Date Created, etc. In the newest release of SQL Server, this data could be indexes and searched along with the data in the document itself. The data does have to be exposed to work, but it's possible now.
9	BISM Model:  Analysis Services in SQL Server does not have BI Semantic Model (BISM) concept.	BISM Model:   Analysis Services will include a new BI Semantic Model (BISM). BISM is a 3-layer model that includes: Data Model Business Logic Data Access BISM will enhance Microsoft's front end analysis experiencing including Excel, Reporting Services and SharePoint Insights. Microsoft has said that BISM is not a replacement for the current BI Models but more of an alternative model. In simple terms, BISM is a relation model that includes BI artifact such as KPIs and hierarchies.

LIKE (Transact-SQL) Sql Server

Wildcard character	Description	Example
%	Any string of zero or more characters.	WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
_ (underscore)	Any single character.	WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).	WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.
[^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef]).	WHERE au_lname LIKE 'de[^l]%' all author last names starting with de and where the following letter is not l.

SELECT * FROM Master.Skills WHERE SkillsName LIKE '%a-z%'

SELECT * FROM Master.Skills WHERE SkillsName LIKE '[a-z]'

SELECT * FROM Master.Skills WHERE SkillsName LIKE '[^f]'

SQL SERVER – TRIM Function to Remove Leading and Trailing Spaces of String

DECLARE @String1 NVARCHAR(MAX)SET @String1 = ' String 'SELECT @String1 OriginalString, RTRIM(LTRIM(@String1)) TrimmedValue
GO

Remove Item From DropDown

string cat="value";

 

string[] cata = cat.Split(',');

for (int d = 0; d < cata.Length - 1; d++)

{

     DDLCategory.Items.Remove(DDLCategory.Items.FindByText(cata[d].ToString()));

}

Replace in Eval Function Asp net

# re: EVAL and replace

EVAL("ID").ToString().Replace("X", "")

or

EVAL("ID").ToString().Replace("X", String.Empty)

differences between SQL Server 2005, 2008 and 2012

With the introduction of SQL Server 2012, it is worth looking at the features available in the different versions of SQL Server, and the benefits that they bring. The following list the major features (and a few negatives) for each upgrade step from SQL 2005 to 2012. Note, SQL Server 2012 SP1 is now available, and brings very few new features, but improves performance and satiability.

SQL Server 2005 to 2008• Transparent Data Encryption. The ability to encrypt an entire database.
• Backup Encryption. Executed at backup time to prevent tampering.
• External Key Management. Storing Keys separate from the data.
• Auditing. Monitoring of data access.
• Data Compression. Fact Table size reduction and improved performance.
• Resource Governor. Restrict users or groups from consuming high levels or resources.
• Hot Plug CPU. Add CPUs on the fly.
• Performance Studio. Collection of performance monitoring tools.
• Installation improvements. Disk images and service pack uninstall options.
• Dynamic Development. New ADO and Visual Studio options as well as Dot Net 3.
• Entity Data Services. Line Of Business (LOB) framework and Entity Query Language (eSQL)
• LINQ. Development query language for access multiple types of data such as SQL and XML.
• Data Synchronizing. Development of frequently disconnected applications.
• Large UDT. No size restriction on UDT.
• Dates and Times. New data types: Date, Time, Date Time Offset.
• File Stream. New data type VarBinary(Max) FileStream for managing binary data.
• Table Value Parameters. The ability to pass an entire table to a stored procedure.
• Spatial Data. Data type for storing Latitude, Longitude, and GPS entries.
• Full Text Search. Native Indexes, thesaurus as metadata, and backup ability.
• SQL Server Integration Service. Improved multiprocessor support and faster lookups.
• MERGE. TSQL command combining Insert, Update, and Delete.
• SQL Server Analysis Server. Stack improvements, faster block computations.
• SQL Server Reporting Server. Improved memory management and better rendering.
• Microsoft Office 2007. Use OFFICE as an SSRS template. SSRS to WORD.

SQL Server 2008 to SQL Server R2• Backup compression is available in R2 standard (was only in Enterprise)
• SQL Server 2008 R2 Express Edition database size limit increased to 10 GB (from 4GB)
• PowerPivot for SharePoint introduced for loading, querying, and managing PowerPivot workbooks that you publish to a SharePoint 2010 server
• PowerPivot for Excel is used to assemble and create relationships in large amounts of data from different sources, and then use that data as the basis for PivotTables and other data visualization objects that support data analysis in Excel.
• Utility Explorer introduced for centralized management of managed SQL instances.
• Master Data Services introduced to manage master (reference) data and maintain an auditable record of that data as it changes over time.
• Enhanced security to prevent an authentication relay attack.

SQL Server 2008R2 to 2012• Inclusion of Visual Studio 2010 (BI edition)
• AlwaysOn technology – allowing automatic replication and switching to mirrored database/server in the event of problems
• In most instances, the performance is 10 times faster than the predecessor (Microsoft claim)
• Buffer rate is high in SQL Server 2012 because of data compression.
• Data visualization tool is available in SQL Server 2012.This allows snapshots of data.
• Support for persistent computed columns and extra geographical approach is possible with spatial features in SQL Server 2012.
• Allows for warm Service Patch patching – meaning SQL or server does not have to be rebooted/restarted to apply patches
• New ‘Contained Databases’ feature allowing easier movement of database instances between servers
• New ‘ColumnStore’ indexes – which turn indexes 90 degrees in use (instead of one index per row, you get multiple column indexes per row which has a major impact on performance)
• New TRY_CONVERT T-SQL command, which will perform data type conversion, but will NULL rather than error where conversion fails.
• New OFFSET/FETCH command which allows automatic capture of pages of rows (like SELECT TOP 90 * from…, but gives a starting row number for the page)
• Redesign of SQL Server management studio – providing new features, better intellisense, new viewing tools and cleaner interface
• Custom user server roles – meaning you can define roles with specific access (such as BI data reader) and assign these custom roles to defined users (therefore faster user maintenance)
• New virtual Windows FileTable commands in T-SQL lets you manage a folder like a table of documents, but still have external control over the contents: UPDATE C:\Docs\*.* SET ReadOnly = 1 WHERE Author = ‘Bob’ AND Created < ’20100101′;)
• NEGATIVE – SQL Server Management Studio will no longer run on XP. Needs Windows Vista, 7 or 8.
• NEGATIVE – SQL Server is no longer licenced on server, but either CALS or Cores in server CPU which could increase costs for large user connection scenarios.
• NEGATIVE – AWE is no longer supported, meaning your SQL Server instance on x86 with 32GB of RAM is only going to be able to use 4GB. Note – SQL now designed to run on 64bit hardware.

Date Formats using CONVERT() Function SQL SERVER

In the Previous article, we learned about the Convert() function,  which can be used to change the date format in what ever format we like. Now, In this article we will see How to get various date formats  using Convert() function.

One of the Most frequently asked questions in any of the Interview or in any of the SQL Server forums is - How to extract only Date value from DATETIME column or How to change the DATETIME value in to a specified format. The answer to all these queries is by using the Convert() function. The CONVERT function's third optional parameter  ‘style’ plays a vital role in formatting the value while  converting the  a datetime value to a VARCHAR.

Let us see various Standard Date Formats that are available in SQL Server.

EXPLICIT Conversion - CONVERT() vs. CAST() SQL SERVER

Convert() and Cast() Both the functions are used to convert the data of a column/output parameter of one data type to another data type explicitly.

The main difference between these two is the syntax.

Syntax :                                                                                   Convert(DataType,Expression,Style)                           CAST(Expression as DataType)

CONVERT function can be  used to convert the output to any format we like. Convert is specific to SQL and more flexible while converting Date time, fractional and monetary values.

The CONVERT function's third optional parameter called ‘style’ plays a key role in formatting the value while  converting the  a date, money, float or real value to a VARCHAR.

If formatting is not an issue then, perhaps the CAST function would be better to use. Cast is more ANSI standard , more portable.

CAST() is required when converting between decimal and numeric to Preserve the Number of Decimal Places in original expression.

Suppose we want to calculate the Exact number of working days of an employee by using his total working hours in a month.Assuming that the number of working hours per day is 9 and the number of working hours of the employee in a month is 185. Now In order to get the Exact Number of working days of an employee, we will divide the total working hours of the employee by the working hours per day i.e.  9

Select 185/9 'No. of working days'

Output:

On observing the above result, we can found that the system has returned the approximate integer value instead of the Exact decimal  value. In order to get the exact value, we have to use the Cast() function where we convert the integer value to decimal.

Select CAST(185 as Decimal)/9  'No. of working days'

Output:

CAST() can also be used to Truncate the Decimal Values. If we used ROUND() function to truncate the decimal values, still we are able to see the zeros after the decimal as shown below.

Select Round(185.23,0) 'Truncate'

Output:

To avoid the zeros, We can use either CAST() or Convert() function. The following examples are to truncate the data to 0 or 2 decimal places

Select CAST(185.235467 as INT) 'Truncate'
Select CAST(185.235467 as DECIMAL(5,2)) 'Truncate' Select CONVERT(DECIMAL(5,2),185.235467)  'Truncate' 

Output:

Similarly CAST() can also be used to Concatenate Different Data Types. Let us take an example of AlphaNumeric Identity Column whose values are the concatenation of an integer and a string.

Here If we tried to concatenate an integer to a  string, it throws an error message as shown below.

Select 'EMP'  + 1

Output:

On using the CAST() function,

Select  'EMP' + CAST(1 as VARCHAR)  'EMPID'

Output:

The Convert() function is used to change the Datetime Datatype to Varchar. It can be used to get the Date and time values separately.

Example:

Select  Convert(VARCHAR(10),GetDate(),101)'Date(mm/dd)'
Select  Convert(VARCHAR(10),GetDate(),103)'Date(dd/mm)'

Output:

Differences Between a View and a Stored Procedure SQL SERVER

View

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

View is a Logical way of Looking at the data that is located in the Physical table. View Contains Only the definition but not the Data.

View Contains only the Select Statement. CREATE,INSERT,UPDATE,DELETE and TRUNCATE Statements are not Allowed.

Views does not Contain any Parameters.

Views does not Contain ‘Select Into’, ‘Group By’ , ‘Order By’ and ‘Having’.

Stored Procedure

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

Stored Procedure is a database object which can used to move the data from the application to the Backend enforcing the Business Rules.

In addition to the Select statement, Stored Procedure contains all the DML statements like INSERT,UPDATE,DELETE and TRUNCATE Statements.

Stored Procedure can contain three types of Parameters IN, OUT and INOUT.

Stored Procedure can have  the clauses like  ‘Group By’ ,’Order By’, ’Having’

How To Display all Tables Of a Database SQL SERVER

In order to display the Names of all Tables Or Details of all Tables of a Database, We have different alternative methods.

1. Using Information_Schema : By using Information_Schema.Tables Or Information_Schema.Columns

Syntax: 

• Select Table_Name From Information_Schema.Tables Where Table_type = 'BASE TABLE'
• Select Distinct Table_Name FromInformation_Schema.Columns

2. Using SysObjects :

Syntax:

• Select * From Sys.Objects Where Type = 'U'
• Select * From SysObjects Where Xtype = 'U'

3. Using System Stored Procedures

Syntax:

• Exec Sp_Tables @Table_Type = "'TABLE'"
• Exec Sp_MsForEachTable 'Print ''?'''

Difference Between Delete and Truncate Commands sql server

Delete Command Is a DML statement and  Maintain log for each of the record that will delete. Delete fires the trigger which was defined on it.Delete maintains the Identity value.Delete can be Roll backed

Truncate Command Is a DDL Statement and does notcreate any log while truncating. That's why Truncate is faster than Delete. Truncate will not fire any trigger.Truncate Reseeds the Identity value. Once Truncate Operation has been done, we can not Roll back the changes. 

Now let us see all the differences between these two commands 

DELETE	TRUNCATE
1.  Is a DML statement	1.  Is a DDL Statement
2.  Will maintain log for  each of the record	2.  Will not create any log
3.  Little bit Slower	3.  Faster than Delete
4.  Will fire the trigger, if any Delete trigger was defined on the table	4.  Will not fire any trigger
5.  Contains the 'Where' clause	5.  Does not contain 'Where' clause
6.  Maintains the Identity value	6.  Reseeds the Identity value
7.  Can be Roll backed	7.  Cannot be Roll backed
8.  Does not require Disabling of Referential Constraints	8.  Requires Disabling the Referential Constraints