DBCC stands for database consistency checker. We use these commands to check the consistency of the databases.
Example, maintenance, validation task and status checks.
DBCC CHECKALLOC - Check consistency of disk allocation.
DBCC CHECKCATALOG - Check catalog consistency
DBCC CHECKCONSTRAINTS - Check integrity of table constraints.
DBCC CHECKDB - Check allocation, and integrity of all objects.
DBCC CHECKFILEGROUP - Check all tables and indexed views in a filegroup.
DBCC CHECKIDENT - Check identity value for a table.
DBCC CHECKTABLE - Check integrity of a table or indexed view.
DBCC CLEANTABLE - Reclaim space from dropped variable-length columns.
DBCC dllname - Unload a DLL from memory.
DBCC DROPCLEANBUFFERS - Remove all clean buffers from the buffer pool.
DBCC HELP - Help for DBCC commands.
DBCC INPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC OPENTRAN - Display information about recent transactions.
DBCC OUTPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC PROCCACHE - Display information about the procedure cache
DBCC SHOW_STATISTICS - Display the current distribution statistics
DBCC SHRINKDATABASE - Shrink the size of the database data and log files.
DBCC SHRINKFILE - Shrink or empty a database data or log file.
DBCC SQLPERF - Display transaction-log space statistics. Reset wait and latch statistics.
DBCC TRACE - Enable or Disable trace flags
DBCC UPDATEUSAGE - Report and correct page and row count inaccuracies in catalog views
DBCC USEROPTIONS - Return the SET options currently active
DBCC deprecated commands
Friday, July 25, 2008
Thursday, July 24, 2008
New Feature in MySQL 6.0
1. The new Falcon transactional storage engine.
2. Support for additional Unicode character sets: utf16, utf32, and 4-byte utf8. These character sets support supplementary Unicode characters; that is, characters outside the Basic Multilingual Plane (BMP).
3. BACKUP DATABASE and RESTORE statements for backup and restore operations.
4. Optimizer enhancements for faster subqueries and joins, including batched index access of table rows for sequences of disjoint ranges by the MyISAM and InnoDB storage engines.
5.The syntax for the LOCK TABLES statement has been extended to support transactional table locks that do not commit transactions automatically. Following LOCK TABLES ... IN SHARE MODE or LOCK TABLES ... IN EXCLUSIVE MODE, you can access tables not mentioned in the LOCK TABLES statement. You can also issue LOCK TABLES statements that acquire transactional locks many times in succession, adding additional tables to the locked set, and without unlocking any tables that were locked previously. When using LOCK TABLES with IN SHARE MODE or IN EXCLUSIVE MODE, tables are not unlocked until the transaction ends.
Transactional locks acquired with LOCK TABLES are released when the transaction ends, either explicitly with COMMIT or ROLLBACK, or implicitly due to a statement that causes implicit commit or because the connection ends.
6. Support for extended comments for tables, columns, and indexes.
More details, visit here http://dev.mysql.com/doc/refman/6.0/en/mysql-nutshell.html
2. Support for additional Unicode character sets: utf16, utf32, and 4-byte utf8. These character sets support supplementary Unicode characters; that is, characters outside the Basic Multilingual Plane (BMP).
3. BACKUP DATABASE and RESTORE statements for backup and restore operations.
4. Optimizer enhancements for faster subqueries and joins, including batched index access of table rows for sequences of disjoint ranges by the MyISAM and InnoDB storage engines.
5.The syntax for the LOCK TABLES statement has been extended to support transactional table locks that do not commit transactions automatically. Following LOCK TABLES ... IN SHARE MODE or LOCK TABLES ... IN EXCLUSIVE MODE, you can access tables not mentioned in the LOCK TABLES statement. You can also issue LOCK TABLES statements that acquire transactional locks many times in succession, adding additional tables to the locked set, and without unlocking any tables that were locked previously. When using LOCK TABLES with IN SHARE MODE or IN EXCLUSIVE MODE, tables are not unlocked until the transaction ends.
Transactional locks acquired with LOCK TABLES are released when the transaction ends, either explicitly with COMMIT or ROLLBACK, or implicitly due to a statement that causes implicit commit or because the connection ends.
6. Support for extended comments for tables, columns, and indexes.
More details, visit here http://dev.mysql.com/doc/refman/6.0/en/mysql-nutshell.html
SQLyog
SQLyog GUI is the most powerful MySQL manager and admin tool, combining the features of MySQL Query Browser, Administrator, phpMyAdmin and various other MySQL Front Ends and MySQL clients in a single intuitive interface.
Wednesday, July 9, 2008
LINQ to SQL in C#
This article provides an introduction to employing LINQ to SQL within a Windows Forms application.
Article will address the incorporation of LINQ to SQL into a win forms project, how to use LINQ to SQL to select, insert, update, and delete data, and how to use LINQ to SQL to execute stored procedures.
The article shows some simple examples of LINQ to SQL; from it you can see how easy it is to query against single and related tables and to write filtered queries, execute stored procedures, perform aggregation, and how to insert, update, and delete records from the database.
DataClasses1DataContext dc = new DataClasses1DataContext();
var q =
from a in dc.GetTable()
where a.CustomerID.StartsWith("A")
orderby a.OrderDate ascending
select a;
dataGridView1.DataSource = q;
click here for full article
Article will address the incorporation of LINQ to SQL into a win forms project, how to use LINQ to SQL to select, insert, update, and delete data, and how to use LINQ to SQL to execute stored procedures.
The article shows some simple examples of LINQ to SQL; from it you can see how easy it is to query against single and related tables and to write filtered queries, execute stored procedures, perform aggregation, and how to insert, update, and delete records from the database.
DataClasses1DataContext dc = new DataClasses1DataContext();
var q =
from a in dc.GetTable
where a.CustomerID.StartsWith("A")
orderby a.OrderDate ascending
select a;
dataGridView1.DataSource = q;
click here for full article
LINQ tutorials convert a Linq Query resultset to DataTable object
This LINQ tutorials convert a Linq Query resultset to DataTable object.
The tutorials attached with the source code
full article, click here
The tutorials attached with the source code
full article, click here
LINQ Begginer Tutorials
In this tutorial, we looked at the current state of today's data access story. Then we looked at how LINQ and the new language features in C# 3.0 solve these issues by providing us with a consistent set of Standard Query Operators that we can use to query any collection that implements IEnumerable. In this installment, we only focused on in-memory collections of data in order to avoid the confusion that most people have when mixing LINQ with DLinq and XLinq, but rest assured these will be covered in the future installments of this article. Furthermore, because LINQ is just a set of methods that adhere to the naming conventions for the Standard Query Operators, anybody can implement their own LINQ based collections for accessing any other type of data.
More Details, please visit the link
More Details, please visit the link
Thursday, June 26, 2008
Relational database management system (RDBMS)
A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular commercial and open source databases currently in use are based on the relational model.
A short definition of an RDBMS may be a DBMS in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables.
A short definition of an RDBMS may be a DBMS in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables.
Monday, June 23, 2008
Not be enough virtual memory - SQL Server
For each updated database, SQL Server allocates at least one 64-KB block for use in formatting log records before they are written to disk. This allocation occurs when the first log record is generated for the database, such as during an INSERT, UPDATE, or DELETE statement. Depending on the activity and the size of the generated log records, subsequent modifications might trigger the allocation of additional 64-KB allocations. SQL Server 7.0 will allocate no more than three 54-KB blocks. In SQL Server 2000, the upper number of allocations for each database is a function of the number of processors that SQL Server is configured to use.
Solution
Use the -g startup parameter to leave additional, unreserved virtual memory available for these database allocations. The -g parameter is documented in the Readme.txt of the SQL Server 7.0 service pack, and in SQL Server 2000 Books Online. The "More Information" section in this article includes the settings that Microsoft recommends you use to determine the appropriate value for this setting.
Solution
Use the -g startup parameter to leave additional, unreserved virtual memory available for these database allocations. The -g parameter is documented in the Readme.txt of the SQL Server 7.0 service pack, and in SQL Server 2000 Books Online. The "More Information" section in this article includes the settings that Microsoft recommends you use to determine the appropriate value for this setting.
Sql Server 2005 - Twelve Tips For Optimizing Query Performance
1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level
Original Article
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level
Original Article
Tuesday, June 10, 2008
LINQ - .NET Language Integrated Query
LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework "Orcas" release, and which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it.
LINQ to SQL fully supports transactions, views, and stored procedures. It also provides an easy way to integrate data validation and business logic rules into your data model.
Developers can use LINQ with any data source. They can express efficient query behavior in their programming language of choice, optionally transform/shape data query results into whatever format they want, and then easily manipulate the results. LINQ-enabled languages can provide full type-safety and compile-time checking of query expressions, and development tools can provide full intellisense, debugging, and rich refactoring support when writing LINQ code.
LINQ supports a very rich extensibility model that facilitates the creation of very efficient domain-specific operators for data sources. The "Orcas" version of the .NET Framework ships with built-in libraries that enable LINQ support against Objects, XML, and Databases.
For More details about the LINQ :
Part 1: Introduction to LINQ to SQL
Part 2: Defining our Data Model Classes
Part 3: Querying our Database
Part 4: Updating our Database
Part 5: Binding UI using the ASP:LinqDataSource Control
Part 6: Retrieving Data Using Stored Procedures
Part 7: Updating our Database using Stored Procedures
Part 8: Executing Custom SQL Expressions
Part 9: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the
Sunday, April 27, 2008
SQL IDENTITY_INSERT
SQL IDENTITY_INSERT is allow values to be inserted into the identity column of a table.
Identity column creates a numeric sequence value for you automatic.
It only alow one table in a session can have the IDENTITY_INSERT property set to ON.
If you need to change the table, you need to set the existing table IDENTITY_INSERT back to OFF.
SET IDENTITY_INSERT TableName OFF
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.
SET IDENTITY_INSERT TableName ON
Identity column creates a numeric sequence value for you automatic.
It only alow one table in a session can have the IDENTITY_INSERT property set to ON.
If you need to change the table, you need to set the existing table IDENTITY_INSERT back to OFF.
SET IDENTITY_INSERT TableName OFF
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.
SET IDENTITY_INSERT TableName ON
Tuesday, April 15, 2008
SQL Get Rows Number
Below SQL Statement return rows number for each row in a partition.
select R1.CategoryName,
rank = (select count(distinct R2.CategoryName) from categories R2 where R1.CategoryName <= R2.CategoryName) from categories R1 Order by Rank
Below is the result after use the row number sql statement:
select R1.CategoryName,
rank = (select count(distinct R2.CategoryName) from categories R2 where R1.CategoryName <= R2.CategoryName) from categories R1 Order by Rank
Below is the result after use the row number sql statement:
Friday, March 28, 2008
DBCC DBREINDEX
When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, index fragmentation can occur and the information in the index can get scattered in the database. Fragmented data can cause SQL Server to perform unnecessary data reads, so a queries performance against a heavy fragmented table can be very poor.
Rebuilding an index is a more efficient way to reduce fragmentation in comparison with dropping and re-creating an index, this is because rebuilding an index is done by one statement.
The DBCC DBREINDEX statement cannot automatically rebuild all of the indexes on all the tables in a database it can only work on one table at a time. You can write your own script to rebuild all the indexes on all the tables in a database
TableName - Is the name of the table in your DataBase.
Index_name - Is the name of the index to rebuild. (refer to image below)
Fillfactor - Is the percentage of space on each index page to be used for storing data when the index is created or rebuilt. Default is 100.
Syntax
DBCC DBREINDEX ('TableName','Index_Name',fillfactor)
Example
DBCC DBREINDEX ('Categories')
DBCC DBREINDEX ('Categories','Categoryname',80)
- It Can improve your database perfomance.
Rebuilding an index is a more efficient way to reduce fragmentation in comparison with dropping and re-creating an index, this is because rebuilding an index is done by one statement.
The DBCC DBREINDEX statement cannot automatically rebuild all of the indexes on all the tables in a database it can only work on one table at a time. You can write your own script to rebuild all the indexes on all the tables in a database
TableName - Is the name of the table in your DataBase.
Index_name - Is the name of the index to rebuild. (refer to image below)
Fillfactor - Is the percentage of space on each index page to be used for storing data when the index is created or rebuilt. Default is 100.
Syntax
DBCC DBREINDEX ('TableName','Index_Name',fillfactor)
Example
DBCC DBREINDEX ('Categories')
DBCC DBREINDEX ('Categories','Categoryname',80)
- It Can improve your database perfomance.
Wednesday, March 19, 2008
WITH RECOMPILE - Re Compile Execution Query Plan
WITH RECOMPILE is specified SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Ideally, you should not use this option because by using this option, you lose most of the advantages you get by substituting SQL queries with the stored procedures. However, you can use this option during early development stages of the project where database structure is still evolving and stored procedures should be recompiled as a result of these changes.
Below is the simple example for using With Recompile in creating STORE PROCEDURE
CREATE PROCEDURE sp_MyTable WITH RECOMPILE
AS
Select Column1, column2 from Table
GO
Except the above method, you also can using with Recompile when u Execute the Store procedure in Query Analyzer.
Below is the example for using With Recompile in Query analyzer
EXEC sp_MyTable1 'Parameter1', 'Parameter2' WITH RECOMPILE
This tells SQL Server to throw away the existing query plan or do not cache the previous execution query plan and build another one query plan but only this once.
Below is the simple example for using With Recompile in creating STORE PROCEDURE
CREATE PROCEDURE sp_MyTable WITH RECOMPILE
AS
Select Column1, column2 from Table
GO
Except the above method, you also can using with Recompile when u Execute the Store procedure in Query Analyzer.
Below is the example for using With Recompile in Query analyzer
EXEC sp_MyTable1 'Parameter1', 'Parameter2' WITH RECOMPILE
This tells SQL Server to throw away the existing query plan or do not cache the previous execution query plan and build another one query plan but only this once.
Thursday, February 28, 2008
sp_changedbowner - SQL Change DB Owner
- Changes the owner of the current database.
After sp_changedbowner is executed, the new owner is known as the dbo user inside the database. The dbo has implied permissions to perform all activities in the database.
The owner of the master, model, or tempdb system databases cannot be changed.
To display a list of the valid login values, execute the sp_helplogins stored procedure.
Executing sp_changedbowner with only the login parameter changes database ownership to login and maps the aliases of users who were previously aliased to dbo to the new database owner.
Only members of the sysadmin fixed server role can execute sp_changedbowner.
Example
This example makes the user Emil the owner of the current database and maps
existing aliases to the old database owner to Emil.
EXEC sp_changedbowner 'Emil'
After sp_changedbowner is executed, the new owner is known as the dbo user inside the database. The dbo has implied permissions to perform all activities in the database.
The owner of the master, model, or tempdb system databases cannot be changed.
To display a list of the valid login values, execute the sp_helplogins stored procedure.
Executing sp_changedbowner with only the login parameter changes database ownership to login and maps the aliases of users who were previously aliased to dbo to the new database owner.
Only members of the sysadmin fixed server role can execute sp_changedbowner.
Example
This example makes the user Emil the owner of the current database and maps
existing aliases to the old database owner to Emil.
EXEC sp_changedbowner 'Emil'
Wednesday, February 27, 2008
Overview of DTS Package
DTS Introduction
Many organizations need to centralize data to improve corporate decision-making. However, their data may be stored in a variety of formats and in different locations. Data Transformation Services (DTS) addresses this vital business need by providing a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations supported by DTS connectivity. By using DTS tools to graphically build DTS packages or by programming a package with the DTS object model, you can create custom data movement solutions tailored to the specialized business needs of your organization.
DTS Basics
Data Transformation Services (DTS) provides a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations. You create a DTS solution as one or more packages. Each package may contain an organized set of tasks that define work to be performed, transformations on data and objects, workflow constraints that define task execution, and connections to data sources and destinations. DTS packages also provide services, such as logging package execution details, controlling transactions, and handling global variables.
DTS supplies a number of tasks that are part of the DTS object model that can be accessed graphically, through DTS Designer, or programmatically. These tasks, which can be configured individually, cover a wide variety of data copying, data transformation, and notification situations. For example:
Importing and exporting data
DTS can import data from a text file or an OLE DB data source (for example, a Microsoft Access 2000 database) into SQL Server. Alternatively, data can be exported from SQL Server to an OLE DB data destination (for example, a Microsoft Excel 2000 spreadsheet). DTS also allows high-speed data loading from text files into SQL Server tables.
Transforming data
DTS Designer includes a Transform Data task that allows you to select data from a data source connection, map the columns of data to a set of transformations, and send the transformed data to a destination connection. DTS Designer also includes a Data Driven Query task that allows you to map data to parameterized queries.
Copying database objects
With DTS, you can transfer indexes, views, logins, stored procedures, triggers, rules, defaults, constraints, and user-defined data types in addition to the data. In addition, you can generate the scripts to copy the database objects.
Sending and receiving messages to and from other users and packages
DTS includes a Send Mail task that allows you to send an e-mail if a package step succeeds or fails. DTS also includes an Execute Package task that allows one package to run another as a package step, and a Message Queue task that allows you to use Message Queuing to send and receive messages between packages.
Executing a set of Transact-SQL statements or Microsoft ActiveX scripts against a data source
The Execute SQL and ActiveX Script tasks allow you to write your own SQL statements and scripting code and execute them as a step in a package workflow.
Many organizations need to centralize data to improve corporate decision-making. However, their data may be stored in a variety of formats and in different locations. Data Transformation Services (DTS) addresses this vital business need by providing a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations supported by DTS connectivity. By using DTS tools to graphically build DTS packages or by programming a package with the DTS object model, you can create custom data movement solutions tailored to the specialized business needs of your organization.
DTS Basics
Data Transformation Services (DTS) provides a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations. You create a DTS solution as one or more packages. Each package may contain an organized set of tasks that define work to be performed, transformations on data and objects, workflow constraints that define task execution, and connections to data sources and destinations. DTS packages also provide services, such as logging package execution details, controlling transactions, and handling global variables.
DTS supplies a number of tasks that are part of the DTS object model that can be accessed graphically, through DTS Designer, or programmatically. These tasks, which can be configured individually, cover a wide variety of data copying, data transformation, and notification situations. For example:
Importing and exporting data
DTS can import data from a text file or an OLE DB data source (for example, a Microsoft Access 2000 database) into SQL Server. Alternatively, data can be exported from SQL Server to an OLE DB data destination (for example, a Microsoft Excel 2000 spreadsheet). DTS also allows high-speed data loading from text files into SQL Server tables.
Transforming data
DTS Designer includes a Transform Data task that allows you to select data from a data source connection, map the columns of data to a set of transformations, and send the transformed data to a destination connection. DTS Designer also includes a Data Driven Query task that allows you to map data to parameterized queries.
Copying database objects
With DTS, you can transfer indexes, views, logins, stored procedures, triggers, rules, defaults, constraints, and user-defined data types in addition to the data. In addition, you can generate the scripts to copy the database objects.
Sending and receiving messages to and from other users and packages
DTS includes a Send Mail task that allows you to send an e-mail if a package step succeeds or fails. DTS also includes an Execute Package task that allows one package to run another as a package step, and a Message Queue task that allows you to use Message Queuing to send and receive messages between packages.
Executing a set of Transact-SQL statements or Microsoft ActiveX scripts against a data source
The Execute SQL and ActiveX Script tasks allow you to write your own SQL statements and scripting code and execute them as a step in a package workflow.
Thursday, February 21, 2008
SQL Server 2008 New DATETIME DataTypes
The DATETIME function’s major change in SQL Server 2008 is the four DATETIME data types introduced. They are DATE, TIME, DATETIMEOFFSET and DATETIME2. IN addition to these newly introduced data types, there are new DATETIME functions all well.
TIME Datatype
DECLARE @dt as DATE
SET @dt = getdate()
SELECT @dt
23:48:04.0570000
The range for the DATE datatype is from 00:00:00.0000000 through 23:59:59.9999999..
DATETIME2 Data Type
DECLARE @dt7 datetime2(7)
SET @dt7 = Getdate()
SELECT @dt7
2007-10-28 22:11:19.7030000
for above example, (7) is the Fraction
Fraction Output
0 2007-10-28 22:11:20
1 2007-10-28 22:11:19.7
2 2007-10-28 22:11:19.70
3 2007-10-28 22:11:19.703
4 2007-10-28 22:11:19.7030
5 2007-10-28 22:11:19.70300
6 2007-10-28 22:11:19.703000
7 2007-10-28 22:11:19.7030000
Monday, February 4, 2008
Maps an existing database user to a SQL Server login using SP_CHANGE_USERS_LOGIN
Automatically mapping a user to a login, creating a new login if it is required
The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that has the password 123456 if the login Emil does not exist.
EXEC SP_Change_Users_Login 'Auto_Fix','Emil',Null,'123456'
- it is useful when u restore a existing DB to new Server. After u restore the DB, user in DB cannot link to SQL Server Login. So, you need to use the sql command instead of SQL Server GUI to update user Access Right.
AUTO_FIX
Auto_FixLinks a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. If a login with the same name does not exist, one will be created. Examine the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.
extra
Use sp_change_users_login to link a database user in the current database with a SQL Server login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing user permissions. The new login cannot be sa, and the user cannot be dbo, guest, or an INFORMATION_SCHEMA user.
sp_change_users_login cannot be executed within a user-defined transaction
The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that has the password 123456 if the login Emil does not exist.
EXEC SP_Change_Users_Login 'Auto_Fix','Emil',Null,'123456'
- it is useful when u restore a existing DB to new Server. After u restore the DB, user in DB cannot link to SQL Server Login. So, you need to use the sql command instead of SQL Server GUI to update user Access Right.
AUTO_FIX
Auto_FixLinks a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. If a login with the same name does not exist, one will be created. Examine the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.
extra
Use sp_change_users_login to link a database user in the current database with a SQL Server login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing user permissions. The new login cannot be sa, and the user cannot be dbo, guest, or an INFORMATION_SCHEMA user.
sp_change_users_login cannot be executed within a user-defined transaction
Identify your SQL Server version and edition
This article show you your current Microsoft SQL Server version number and the corresponding product or service pack (SP) level. It also describes how to identify the specific edition Microsoft SQL Server.
SELECT @@VERSION
Result - Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Result - 8.00.2039 , SP4 , Standard Edition
SELECT @@VERSION
Result - Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Result - 8.00.2039 , SP4 , Standard Edition
Saturday, September 8, 2007
SQL SET ROWCOUNT
SQL SET ROWCOUNT statement gives administrators some control over the execution of "runaway" queries issued by naive users of SQL. This statement stops the execution of a query as soon as a specified number of rows has been retrieved. In this way, system resources are not wasted, but users can see at least a partial result set. An informational message is displayed after the result set, indicating that SET ROWCOUNT is in effect.
If a fully executed query happens to return the exact number of rows specified by the ROWCOUNT value, the query-termination message is still displayed.
Example of SQL SET ROWCOUNT
SELECT * FROM EMPLOYEES
- It will return 10 rows of records.
After add SET ROWCOUNT
SET ROWCOUNT 5
SELECT * FROM EMPLOYEES
- It will return 5 rows of records instead of 10 row.
(Bacause you set the RowCount to 5)
SQL SET ROWCOUNT
If a fully executed query happens to return the exact number of rows specified by the ROWCOUNT value, the query-termination message is still displayed.
Example of SQL SET ROWCOUNT
SELECT * FROM EMPLOYEES
- It will return 10 rows of records.
After add SET ROWCOUNT
SET ROWCOUNT 5
SELECT * FROM EMPLOYEES
- It will return 5 rows of records instead of 10 row.
(Bacause you set the RowCount to 5)
SQL SET ROWCOUNT
Subscribe to:
Posts (Atom)