Posted by: keemtipuri | January 8, 2010

Multi Tenants Database Architecture

Multi Tenants Database Architecture

It is used to address the problem of SAAS which can serve multiple clients. Multi-Tenants database architecture is very useful when one instance of database is serving to multiple clients. Only one set of hardware resources is needed to fulfill the requirements of all users. Multi-tenant is based on subscriber model, so user has freedom to avail the facility as per business requirement or can turnoff.

There are different approaches to the advantage out of the multi-tenants database. These are

  • Dedicated database: Separate databases per tenant.
  • Dedicated table and different schema:  Shared database and separate schema.
  • Share table/schema: Same database and same table.

Now, it is very important to select appropriate approach for your application depending upon the following factors.

  • Size of tenant database
  • Number of tenant
  • Number of users per tenant
  • Growth rate of tenant
  • Growth rate of tenant database
  • Security
  • Cost

1 . Dedicated database:

It is straight forward approach where each tenant has its own database. Each tenant has its own set of data that remains logically isolated from data that belongs to all other tenants.


  • More secure data
  • Easy to customize for vendor specific needs
  • Easy to maintain e.g. backups, restore etc…


  • Relatively high hardware and maintenance requirements
  • This approach tends to lead to higher costs for maintaining equipment and backing up tenant data.

2. Dedicated table and different schema:

Serving multiple tenants under same database, where each tenant has its own sets of tables grouped with schema as required by tenant.


  • Good for small database application where number of tables per tenant is small.
  • Cost is low as compared to dedicated database approach.
  • Moderate logical isolation level is there for vendors having security as a concern.


  • Tenant data is harder to restore incase of failure.
  • Difficult to manage large database application.

3. Shared Table/Schema:

This approach involves using the same database and the same set of tables to host multiple tenants’ data. A given table can include records from multiple tenants stored in any order; a Tenant ID column associates every record with the appropriate tenant. Any application accessing the row must refer to this column in every query to ensure that one tenant is not able to see another tenant’s data.

TenantID Col1 Col2 Col3 Col4
1 Abc .. .. ..
21 Cdw .. .. ..
.. .. .. .. ..


  • Lowest hardware cost as compared to other approaches.
  • Can serve more tenants per server.
  • Ability to update the schema in one place and affect all tenants.


  • More security is required to make sure no one can access cross-tenant data.
  • Can affect query performance because of more rows.
  • Can only update the schema in one place and thereby affect all tenants.

Extension Table

Incase, there is need to increase number of fields as per tenant requirement under approach 3 then?

As all tenants will share same table/schema. It is very difficult to customize the number of fields.

One way to avoid these limitations is to allow tenants to extend the data model arbitrarily, storing custom data in a separate table and using metadata to define labels and data types for each tenant’s custom fields.

TenantID FN Field TagID
101 Kim Trade 221
202 Tim HR 433
342 Rim Fin 510

(Data Table)

TenantID ExtID Label DataType
342 3990 Age int
120 3122 Status bool
202 1200 LName string

(Metadata Table)

TagID ExtID Value
433 1200 Border
500 321 abc
510 3990 23

(Extension Table)

Here, a metadata table stores important information about every custom field defined by tenant, including the field’s name (label) and data type. These fields are created dynamically on front end (GUI) with unique id and value entered by end user corresponds to these fields are stored in different table Extension table.

So corresponding to data table we need to create two new tables “MetaData” and “Extension”.

This approach allows each tenant to create as many custom fields as necessary to meet its business need. When the end user retrieves a customer record, it performs a lookup in the extension table, selects all rows corresponding to the record ID, and returns a value for each custom field used. To associate these values with the correct custom fields and cast them to the correct data types, the application looks up the custom field information in metadata using the extension IDs associated with each value from the extension table.

This approach adds a level of complexity for database functions, such as indexing, querying, and updating records.



Posted by: keemtipuri | December 22, 2009

Dot Net

What is difference between foreground and background thread?
• Foreground threads have the ability to prevent the current application from terminating. The CLR will not shut down an application (which is to say, unload the hosting AppDomain) until all foreground threads have ended.

• Background threads (sometimes called daemon threads) are viewed by the CLR as expendable paths of execution that can be ignored at any point in time (even if they are currently laboring over some unit of work). Thus, if all foreground threads have terminated, any and all background threads are automatically killed when the application domain unloads.

What is the difference between STA and MTA?

In single threaded apartment (STA) each thread is isolated in a separate apartment underneath the process. The process can have any number of apartments that share data through a proxy. The application defines when and for how long the thread in each apartment should execute. All requests are serialized through the Windows message queue such that only a single apartment is accessed at a time and thus only a single thread will be executing at any one time. STA is the

threading model that most Visual Basic developers are familiar with because this is the threading model available to VB applications prior to VB.NET. You can think of it like an apartment building full of a row of one room apartments that are accessible one at a time through a single hallway. The advantage this provides over single

threaded is that multiple commands can be issued at one time instead of just a single command, but the commands are still sequentially executed. The free threaded/Multi Threaded Apartment (MTA) model has a single apartment created underneath the process rather than multiple apartments. This single apartment holds

multiple threads rather than just a single thread. No message queue is required because all of the threads are a part of the same apartment and can share data without a proxy. You can think of it like a building with multiple rooms that are all accessible once you are inside the building. These applications typically execute faster than

single threaded and STA because there is less system overhead and can be optimized to eliminate system idle time.

What do you mean by Share Point Portal ?

Here I have taken information regarding  Share Point Portal Server 2003 provides mainly access to the crucial business information and applications.With the help of Share Point Server we can server  information between  Public Folders, Data Bases, File Servers  and the websites that are based on Windows server 2003. This Share Point Portal is  integrated with MSAccess and Windows servers,So we can get  a Wide range of document management functionality. We can also create a full featured portal with readymade navigation and structure.

1.6.2What is cross page posting in ASP.NET2.0 ?

When we have to post data from one page to another in application we used server.transfer method but in this the URL remains the same but in cross page posting there is little different there is normal post back is done but in target page we can access values of server control in the source page.This is quite simple we have to only set the PostBackUrl property of Button,LinkButton or imagebutton which specifies the target page.In target page we can access the PreviousPage property.And we have to use the @PreviousPageType directive.We can access control of PreviousPage by using the findcontrol method.When we set the PostBackURL property ASP.NET framework bind the HTML and Javascript function automatically.

What you thing about the WebPortal ?

Web portal is nothing but a page that allows a user to customize his/her homepage. We can use Widgets to create that portal we have only to drag and drop widgets on the page. The user can set his Widgets on any where on the page where he has to get them. Widgets are nothing but a page area that helps particular function to response. Widgets example are address books, contact lists, RSS feeds, clocks, calendars, play lists, stock tickers, weather reports, traffic reports, dictionaries, games and another such beautiful things that we can not imagine. We can also say Web Parts in Share Point Portal. These are one of Ajax-Powered.

How to start Outlook,NotePad file in AsP.NET with code ?

Here is the syntax to open outlook or notepad file in ASP.NET VB.NET Process.Start(“Notepad.exe”) Process.Start(“msimn.exe”); C#.NET System.Diagnostics.Process.Start(“msimn.exe”); System.Diagnostics.Process.Start(“Notepad.exe”);

What is the purpose of IIS ?

We can call IIS(Internet Information Services) a powerful Web server that helps us creating highly reliable, scalable and manageable infrastructure for Web application which runs on Windows Server 2003. IIS helps development center and increase Web site and application availability while lowering system administration costs. It also runs on Windows NT/2000 platforms and also for above versions. With IIS, Microsoft includes a set of programs for building and administering Web sites, a search engine, and support for writing Web-based applications that access database. IIS also called http server since it process the http request and gets http response.

What is main difference between GridLayout and FormLayout ?

GridLayout helps in providing absolute positioning of every control placed on the page.It is easier to devlop page with absolute positioning because control can be placed any where according to our requirement.But FormLayout is little different only experience Web Devloper used this one reason is it is helpful for wider range browser.If there is absolute positioning we can notice that there are number of DIV tags.But in FormLayout whole work are done through the tables.

How Visual SourceSafe helps Us ?
One of the powerful tool provided by Microsoft to keep up-to-date of files system its keeps records of file history once we add files to source safe it can be add to database and the changes ade by diffrenet user to this files are maintained in database from that we can get the older version of files to.This also helps in sharing,merging of files.

Can you define what is SharePoint and some overview about this ?

SharePoint helps workers for  creating  powerful personalized interfaces only by dragging and drop pre-defined Web Part Components. And these Web Parts components also helps non programmers to get information which care  and customize the appearance of Web pages. To under stand it we take an example  one Web Part might display a user’s information another might create a graph showing current employee status  and a third might show a list of Employees Salary. This is also possible that each functions has a link to a video or audio presentation.So now  Developers are unable to  create these Web Part components and make them available to SharePoint users.

What is different between WebUserControl and in WebCustomControl?

Web user controls: – Web User Control is Easier to create and another thing is that its support is limited for users who use a visual design tool one gud thing is that its contains static layout one more thing a seprate copy is required for each application.
Web custom controls: -Web Custom Control is typical to create and gud for dynamic layout and another thing is it have full tool support for user and a single copy of control is required because it is placed in Global Assembly cache.

What is Sandbox in SQL server and explain permission level in Sql Server ?

Sandbox is place where we run trused program or script which is created  from the third party. There are three type of Sandbox where user code run.
Safe Access Sandbox:-Here we can only create stored procedure,triggers,functions,datatypes etc.But we doesnot have acess memory ,disk etc.
External Access Sandbox:-We cn access File systems outside the box. We can not play with threading,memory allocation etc.
Unsafe Access Sandbox:-Here we can write unreliable and unsafe code.

How many types of cookies are there in .NET ?

Two type of cookeies.
a) single valued eg request.cookies(”UserName”).value=”dotnetquestion”
b)Multivalued cookies. These are used in the way collections are used example

When we get Error ‘HTTP 502 Proxy Error’ ?

We get this error when we execute ASP.NET Web pages in Visual Web Developer Web server, because the URL randomly select port number and proxy servers did not recognize the URL and return this error. To resolve this problem we have to change settings in Internet Explorer to bypass the proxy server for local addresses, so that the request is not sent to the proxy.

What do you mean by three-tier architecture?

The three-tier architecture was comes into existence to improve management of code and contents and to improve the performance of the web based applications.There are mainly three layers in three-tier architecture.the are define as follows
(2)Business Logic
(1)First layer Presentation contains mainly the interface code, and this is shown to user. This code could contain any technology that can be used on the client side like HTML, JavaScript or VBScript etc.
(2)Second layer is Business Logic which contains all the code of the server-side .This layer have code to interact with database database and to query, manipulate, pass data to user interface and handle any input from the UI as well.
(3)Third layer Data represents the data store like MS Access, SQL Server, an XML file, an Excel file or even a text file containing data also some addtional database are also added to that layers.

What is Finalizer in .NET define Dispose and Finalize ?
We can say that Finalizer are the methods that’s helps in cleanp the code that is executed before object is garbage collected .The process is called finalization . There are two methods of finalizer Dispose and Finalize .There is little diffrenet between two of this method .
When we call Dispose method is realse all the resources hold by an object as well as all the resorces hold by the parent object.When we call Dispose method it clean managed as well as unmanaged resources.
Finalize methd also cleans resources but finalize call dispose clears only the unmanged resources because in finalization the garbase collecter clears all the object hold by managed code so finalization fails to prevent thos one of methd is used that is: GC.SuppressFinalize.

Define SMTPclient class in DotNet framework class libarary ?

Each classes in dotnet framework inclue some properties,method and events.These properties ,methods and events are member of a class.SMTPclient class mainly concern with sending mail.This class contain the folling member.
Host:-The name or IP address of email server.
Port:-Port that is use when sending mail.
Send:-Enables us to send email synchronously.
SendAsynchronous:-Enables us to send an email asynchronously.
SendCompleted:-This event raised when an asynchronous send opertion completes.

Does .NET CLR and SQL SERVER run in different process ?

Dot Net CLR and all .net realtes application and Sql Server run in same process or we can say that that on the 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 is Com Marshler and its importance in .NET ?

Com Marshler is one of useful component of CLR. Its Task is to marshal data between Managed and Unmanaged environment .It helps in representation of data accross diffrenet execution enviroment.It performs the conversion of data format between manage and unmanaged code.By the helps of Com Marshlar CLR allows manage code to interoperate with unmanaged code.

What is CSU and its description ?

CSU stands for comma separate values also called comma delimited.It is plain text file which stores spreadsheets or basic datatype in very simple format.One record in each line and each field separted with comma’s it is often used to transfer large ammount spreadsheet data or database information between program.

The IHttpHandler and IHttpHandlerFactory interfaces ?

The IHttpHandler interface is implemented by all the handlers. The interface consists of one property called IsReusable. The IsReusable property gets a value indicating whether another request can use the IHttpHandler instance. The method ProcessRequest() allows you to process the current request. This is the core place where all your code goes. This method receives a parameter of type HttpContext using which you can access the intrinsic objects such as Request and Response. The IHttpHandlerFactory interface consists of two methods – GetHandler and ReleaseHandler. The GetHandler() method instantiates the required HTTP handler based on some condition and returns it back to ASP.NET. The ReleaseHandler() method allows the factory to reuse an existing handler.

What is Viewstate?

View state is used by the ASP.NET page framework to automatically save the values of the page and of each control just prior to rendering to the page. When the page is posted, one of the first tasks performed by page processing is to restore view state.
State management is the process by which you maintain state and page information over multiple requests for the same or different pages.
Client-side options are:

* The ViewState property * Query strings
* Hidden fields * Cookies

Server-side options are:

* Application state * Session state * DataBase

Use the View State property to save data in a hidden field on a page. Because ViewState stores data on the page, it is limited to items that can be serialized. If you want to store more complex items in View State, you must convert the items to and from a string.
ASP.NET provides the following ways to retain variables between requests:
Context.Handler object Use this object to retrieve public members of one Web form’s class from a subsequently displayed Web form.
Query strings Use these strings to pass information between requests and responses as part of the Web address. Query strings are visible to the user, so they should not contain secure information such as passwords.
Cookies Use cookies to store small amounts of information on a client. Clients might refuse cookies, so your code has to anticipate that possibility.
View state ASP.NET stores items added to a page’s ViewState property as hidden fields on the page.
Session state Use Session state variables to store items that you want keep local to the current session (single user).
Application state Use Application state variables to store items that you want be available to all users of the application.


While excuting the page, it will go under the fallowing steps(or fires the events) which collectivly known as Page Life cycle.
Page_Init — Page Initialization
LoadViewState — View State Loading
LoadPostData — Postback data processing
Page_Load — Page Loading
RaisePostDataChangedEvent — PostBack Change Notification
RaisePostBackEvent — PostBack Event Handling
Page_PreRender — Page Pre Rendering Phase
SaveViewState — View State Saving
Page_Render — Page Rendering
Page_UnLoad — Page Unloading

What is Satellite Assemblies ?

Satellite assemblies are often used to deploy language-specific resources for an application. These language-specific assemblies work in side-by-side execution because the application has a separate product ID for each language and installs satellite assemblies in a language-specific subdirectory for each language. When uninstalling, the application removes only the satellite assemblies associated with a given language and .NET Framework version. No core .NET Framework files are removed unless the last language for that .NET Framework version is being removed. For example, English and Japanese editions of the .NET Framework version 1.1 share the same core files. The Japanese .NET Framework version 1.1 adds satellite assemblies with localized resources in a \ja subdirectory. An application that supports the .NET Framework version 1.1, regardless of its language, always uses the same core runtime files.

What is CAS ?

CAS is the part of the .NET security model that determines whether or not a piece of code is allowed to run, and what resources it can use when it is running. For example, it is CAS that will prevent a .NET web applet from formatting your hard disk. How does CAS work? The CAS security policy revolves around two key concepts – code groups and permissions. Each .NET assembly is a member of a particular code group, and each code group is granted the permissions specified in a named permission set. For example, using the default security policy, a control downloaded from a web site belongs to the ‘Zone – Internet’ code group, which adheres to the permissions defined by the ‘Internet’ named permission set. (Naturally the ‘Internet’ named permission set represents a very restrictive range of permissions.)

Automatic Memory Management ?

Automatic Memory Management: From a programmer’s perspective, this is probably the single biggest benefit of the .NET Framework. No, I’m not kidding. Every project I’ve worked on in my long career of DOS and Windows development has suffered at some point from memory management issues. Proper memory management is hard. Even very good programmers have difficulty with it. It’s entirely too easy for a small mistake to cause a program to chew up memory and crash, sometimes bringing the operating system to a screeching halt in the process.

Programmers understand that they’re responsible for releasing any memory that they allocate, but they’re not very good at actually doing it. In addition, functions that allocate memory as a side effect abound in the Windows API and in the C runtime library. It’s nearly impossible for a programmer to know all of the rules. Even when the programmer follows the rules, a small memory leak in a support library can cause big problems if called enough.

The .NET Framework solves the memory management problems by implementing a garbage collector that can keep track of allocated memory references and release the memory when it is no longer referenced. A large part of what makes this possible is the blazing speed of today’s processors. When you’re running a 2 GHz machine, it’s easy to spare a few cycles for memory management. Not that the garbage collector takes a huge number of cycles–it’s incredibly efficient.
The garbage collector isn’t perfect and it doesn’t solve the problem of mis-managing other scarce resources (file handles, for example), but it relieves programmers from having to worry about a huge source of bugs that trips almost everybody up in other programming environments.
On balance, automatic memory management is a huge win in almost every situation.

ASP.NET Authentication providers and IIS Security?

ASP.NET implements authentication using authentication providers, which are code modules that verify credentials and implement other security functionality such as cookie generation. ASP.NET supports the following three authentication providers:

Forms Authentication: Using this provider causes unauthenticated requests to be redirected to a specified HTML form using client side redirection. The user can then supply logon credentials, and post the form back to the server. If the application authenticates the request (using application-specific logic), ASP.NET issues a cookie that contains the credentials or a key for reacquiring the client identity. Subsequent requests are issued with the cookie in the request headers, which means that subsequent authentications are unnecessary.

Passport Authentication: This is a centralized authentication service provided by Microsoft that offers a single logon facility and membership services for participating sites. ASP.NET, in conjunction with the Microsoft® Passport software development kit (SDK), provides similar functionality as Forms Authentication to Passport users.

Windows Authentication: This provider utilizes the authentication capabilities of IIS. After IIS completes its authentication, ASP.NET uses the authenticated identity’s token to authorize access.

Posted by: keemtipuri | December 22, 2009

SQL Server 2005

Find the Nth highest salary without using of Top Keyword.

select max(empsal) from emp e1

where N <= (Select count(distinct empsal) from emp e2

where e1.empsal <= e2.empsal)

Write SQL for creating duplicate table

Select * into emp2 from emp

>>emp2 is new table ; emp is existing table

Write SQL for copying data from one table to another table

insert into emp3 select * from emp
>> emp3 is empty existing table

Delete the duplicate data from below table “SalesHistory”

Product SaleDate SalePrice

Computer 1919-03-18 00:00:00.000 1008.00

BigScreen 1927-03-18 00:00:00.000 91.00

PoolTable 1927-04-01 00:00:00.000 139.00

Computer 1919-03-18 00:00:00.000 1008.00

BigScreen 1927-03-25 00:00:00.000 92.00

PoolTable 1927-03-25 00:00:00.000 108.00

Computer 1919-04-01 00:00:00.000 150.00

BigScreen 1927-04-01 00:00:00.000 123.00

PoolTable 1927-04-01 00:00:00.000 139.00

Computer 1919-04-08 00:00:00.000 168.00

The new CTE feature in SQL Server 2005 makes it very easy to remove these duplicates, with or without a primary key.

The script below defines my CTE. I am using a windowing function named DENSE_RANK to group the records together based on the Product, SaleDate, and SalePrice fields, and assign them a sequential value randomly. This means that if I have two records with the exact same Product, SaleDate, and SalePrice values, the first record will be ranked as 1, the second as 2, and so on.

;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)




Product, SaleDate, SalePrice,

Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC)

FROM SalesHistory



WHERE Ranking > 1

What is CTE?

Common Table Expressions, or CTE, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query. Moreover, CTEs can be recursively defined, allowing a recursive entity to be enumerated without the need for recursive-stored procedures.

What is the basic functions for master, msdb, model, tempdb databases?

The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in the instance

What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

What is SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What is User Defined Functions?

User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

What kind of User-Defined Functions can be created?

There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.

What is the difference between a local and a global variable?

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

What is log shipping?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db can be used this as the Disaster Recovery plan. The key feature of log shipping is that is will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

What is @@ERROR?

The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

What is Raiseerror?

Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.

What is difference between DELETE & 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.

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back using logs.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.

DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back using logs.
DELETE is DML Command.
DELETE does not reset identity of the table.

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 rowset. This can be used in JOINs with other tables.
Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

When is the use of UPDATE_STATISTICS command?

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like 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. HAVING criteria is applied after the the grouping of rows has occurred.

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.
Storped 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’s the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

Posted by: keemtipuri | November 20, 2009

SAAS – Delivery Model

SAAS (Software as a Service) comprises of technologies SOA, Web services and Ajax as a new approach to build enterprise application. It is deployed as hosted service and access over internet for all vendors. Basic idea behind SAAS is to deliver business application including collaboration software and line of business to vendors for running their business.

 SAAS is often divided into two categories.

  •  Business services which refer to business solutions offered to companies and enterprises, and sold or made available to these enterprises on a subscription basis. Applications covered under this category include business processes such as supply-chain management programs, customer relations applications and others.
  • Customer-oriented services which are offered to the general public either on a subscription basis or (more often than not) offered for free but are supported by advertising. Web-based email services such as those cited above fall into this general category.

 Properties of SAAS

  •  Software Delivery Model: On – Demand delivered over the web and managed by service provider.
  • Release Cycle: Completely transparent with continuously releases and updates.
  • Architecture: Web based Technologies – SOA, Agile, Ajax and web services.
  • Payment: Based on license model or consumption model or subscriptions etc.

 Essentials for SAAS

  •  Multiple Client Support: SAAS should have the ability to support multiple clients from a single instance to reduce infrastructure, hosting and management costs.
  • Performance: SAAS should have the ability to support many users with minimal resources to derive efficiencies and economies of scale.
  • Availability: 24*7 supports, application availability requires an investment in the solution’s infrastructure, processes and people.
  • Security: Need to secure multiple vendors data.
  • Integration with Back Office Applications: SAAS need to integrate with back office systems to avoid duplication of work and create consistency of data across systems.
  •  Multi-Browser Compatibility: Since every company has its own standard for supported browsers and operating systems, it is extremely important that SAAS comply with multiple browser platforms and versions.
  • Manageability: A hosted application requires an infrastructure to support billing, logging and provisioning across multiple customers.

 How SAAS differs from ASP

 In the 1990s, ASP (application Service Providers) took software delivery to the next level by offering packaged or shrink-wrapped software to customers over the internet. SAAS is closely related to the ASP and on-demand computing software delivery models. SAAS differs from ASP in many ways.

  • Upgrade and Enhancement: ASP depend on commercial Software providers, their ability to upgrade the application was limited. Whereas in SAAS, upgrades are often possible. Since no software is deployed on the customer site, enhancements may be implemented at the SAAS data center and made available to all vendors.
  • Interface: Most ASP-supported applications were immense client-server programs with simple HTML Web interfaces. SAAS solutions today are designed specifically for the Web environment, which improves usability and manageability.
  • Approach: ASP vendors were ill prepared and rushed their offerings to market before performance, security, customization and integration issues were solved, and before many organizations were ready to adopt the ASP model. Users are better equipped to take an advantage of SAAS with the majority of these issues being resolved. The decrease in technical provisions and increase in user awareness and cost savings is providing the momentum for companies to adopt a SAAS approach.
  • IT Support: Inclusive in SAAS.
  • Scalability: very limited to ASP but open in SAAS. 
  • Hardware Compliance: Most SAAS products are purpose-built for the Internet. Also SAAS hosting hardware is developed for use on the Internet. An ASP would take a software package that was not written to be hosted and put it on the Web. This often caused hardware compliance problems.

 Players in the Market

  • Salesforce
  • Amazon

 Related Topics

  • Multi-Tenants Database Architecture
  • Cloud Computing – Infrastructure
  • PAAS  – Platform as a Service