STUFF function in SQL

  Recently in one of our assignment we have utilized STUFF function of SQL Server 2005. So just want to share the same. Stuff is very simple function to delete specified length of symbols and insert new symbols in string. We can say it’s a next version for REPLACE function. Following is simple test environment for STUFF function Above SQL script is simple to understand; so use this in your...

Xml Basics in SQL Server 2005

Xml Basics – SQL Server 2005 Hello All, We have used some basic features for Xml structure in SQL Server 2005 in one of our assignment; just want to share the same. When we heard Xml phenomena in context of SQL Server 2005; it creates lot of curiosity about motivation after using the same. In this article we will concentrate on some Xml standard methods which are part of SQL Server 2005, and...

10 reasons why SQL Server 2008 is going to rock!

Differentiator Features of SQL sever 2008 Here are the top 10 reasons why 10 reasons why SQL Server 2008 is going to rock. 10.  Plug-in model for SSMS.   SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind.  Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom. ...

SQL Error: Database diagram support objects cannot be installed

Solution to MS SQL Error: Database diagram support objects cannot be installed If you ever get a message like this when trying to create a diagram in SQL 2005 “Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database...

Microsoft Visual Studio LightSwitch – Introduction

What is Microsoft Visual Studio LightSwitch?   Microsoft has finally announced the Visual Studio LightSwitch, which is featured as the newest member of the Visual Studio product family. Microsoft Visual Studio LightSwitch gives you a simpler and faster way to create professional-quality business applications for the desktop, the web, and the cloud. Microsoft has revealed that LightSwitch dramatically...

Understanding INNER join in detail

Understanding INNER join in detail The objective of this article is dig into detail of how INNER joins produce the result set. The INNER join is the common join type used in many cases. Even though it is used very often I have seen most people are not certain how it produces the result set when changing the join condition.   Before go into the article contents, I need to create the two tables...

Delete All Stored Procedures and Views in SQL

  Delete All Stored Procedures declare @procName varchar(500)declare cur cursor for select [name] from sys.objects where type = 'p'open cur fetch next from cur into @procName while @@fetch_status = 0 begin if @procName <> 'DeleteAllProcedures' exec('drop procedure ' + @procName) fetch next from cur into @procName end close...

Delete All Tables in MS SQL

Delete All Tables  – MS SQL Query --Delete All Keys DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSORSET @Cursor = CURSOR FAST_FORWARD FORSELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAMEOPEN @Cursor FETCH...

Default Database roles and there permissions in MS SQL

Default Database roles and there permissions in MS SQL   Database roles: Each database has a set of fixed database roles, to which database users can be added. These fixed database roles are unique within the database. While the permissions of fixed database roles cannot be altered, new database roles can be created. Here are the fixed database roles and their associated permissions in SQL Server: Fixed...

Query Optimization in SQLite

Query Optimization in SQLite   This document provides a terse overview of how the query optimizer for SQLite works. This is not a tutorial. The reader is likely to need some prior knowledge of how database engines operate in order to fully understand this text. 1.0 WHERE clause analysis The WHERE clause on a query is broken up into "terms" where each term is separated from the others...

Default MS SQL Server roles and their permissions

Default MS SQL Server roles and their permissions   These are default role available in MS sql sever. Logins can be added to these roles to gain the associated administrative permissions of the role. Fixed server roles cannot be altered and new server roles cannot be created. Here are the fixed server roles and their associated permissions in SQL Server: Fixed server role Description sysadmin Can...

SQLite Database Architecture

SQLite Database Architecture   Block Diagram Of SQLite   This document describes the architecture of the SQLite library. The information here is useful to those who want to understand or modify the inner workings of SQLite. A block diagram showing the main components of SQLite and how they interrelate is shown at the right. The text that follows will provide a quick overview of each of...

Enable SQL encryption in ASP.NET

Database Encryption is ASP.NET   If you are using the ADO.NET SQL Server managed data provider and a SqlConnection object to connect to SQL Server, then you can enable encryption by adding the encrypt=true parameter to the connection string as shown here. <configuration>    <connectionStrings>       <add name="MyDbConn"       ...

How to restrict user to access database in MS SQL

RESTRICTED_USER in MS SQL RESTRICTED_USER allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but does not limit their number. All connections to the database are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. After the database has transitioned to the RESTRICTED_USER...

SQLite Component in V3

Different component in SQLite Version 3   Following are the some sqlite v3 components Objects:     * sqlite3     * sqlite3_int64     * sqlite3_uint64     * sqlite_int64     * sqlite_uint64     * sqlite3_file     * sqlite3_io_methods     * sqlite3_mutex...