Purpose : A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object [...]
Continue reading about [SQL Server Developer Tips] CTE (Common Table Expression)
SQL Server 2005 – Relational Database Engine
Database Engine is the primary component of SQL Server 2005, It is the Online Transaction Processing (OLTP) engine for SQL Server. It is responsible for the efficient storage, retrieval, and manipulation of relational and Extensible Markup Language (XML) formatted data. It is highly optimized for transaction processing
[...]
Following are the difference between SET and SELECT while using for DML
1. SET can assign only one variable at a time… SELECT can assign multiple variable in one line
2. If assigning from Query, SET can assign only scalar values
3. If query returns multiple values / rows, SET raises an error… Select simply assigns one row [...]
Continue reading about [SQL Tips] SET vs SELECT in Sql Server
SQL Server provides a method of retrieving data from more than one table using joins. A join can be defined as an operation that includes the retrieval of data from more than one table at a time.
A join is implemented using the SELECT statement, in which the SELECT list contains the name of the columns [...]
If we change SQL Server machine’s computer name, the new name doesn’t take to effect immediately to SQL server. This article will solve this problem.
Continue reading about Issue on Renaming SQL Server machine name
The following table compares the maximum sizes and numbers of various objects defined in SQL Server 7.0, SQL Server 2000 and SQL Server 2005 databases or referenced in Transact-SQL statements. The table does not include Microsoft SQL Server 2000 Windows CE Edition and Microsoft SQL Server 2005 Windows CE Edition. The table has the technical [...]
Continue reading about SQL Server – Maximum Capacity Specifications Comparison
Microsoft says that SQL Server Integration Services (SSIS) “is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing.” A simpler way to think of SSIS is that it’s the solution for automating SQL Server. SSIS provides a way to build packages made up of tasks [...]
Continue reading about Steps to Create and Deploy SSIS Package as a SQLAgent Job
There may be situations where you need to delete all the tables in a database, or clean all rows in all the tables or truncate all the tables in database. If the database contains only few tables then it is not a problem, Otherwise it would be a major task to clean the database.
Luckily Microsoft [...]
Continue reading about Delete All tables in a SQL Server Database
Overview of Consistency Errors
A DBCC CHECKDB statement may report consistency errors in your database when all the following conditions are true:
SQL Server loads data into a column that is a sql_variant data type.
The data originates in another column that is a sql_variant data type and at some point the data is sent on-the-wire [...]
Continue reading about Consistency Errors in MS SQL Server 2000
What are Builtin\Administrators?
SQL Server creates a default NT Group login called Builtin\Administrators during installations.This login facilitates the following
All local NT administrators on the server to have default sysadmin privileges
Automatic access to system tables
Automatic access to user tables
How security is compromised?
SQL Server security is compromised as follows
Vulnerable to changes by [...]
