Search This Blog
MySQL : Restore Database From the Command Prompt
MySQL : Backup Database From the Command Prompt
MySQL Function : mysql_affected_rows
Description
int mysql_affected_rows ([ resource $link_identifier ] )
Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link_identifier .
Example #
= mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');
/* this should return the correct numbers of deleted records */
mysql_query('DELETE FROM mytable WHERE id <>);
printf("Records deleted: %d\n", mysql_affected_rows());
/* with a where clause that is never true, it should return 0 */
mysql_query('DELETE FROM mytable WHERE 0');
printf("Records deleted: %d\n", mysql_affected_rows());
?>
The above example will output something similar to:
Records deleted: 10
Records deleted: 0
MySQL : Have the new Query Analyzer graphs helped you solve development problems internally?
In addition, our support team also runs the Query Analyzer on internal servers, and they actually found some problem queries. In some occasions they solve performance issues that they didn't know the root cause; in other situations they see the performance drop and have actually been able to tune some queries that they didn't even know were problematic.
MySQL : How do the Query Analyzer graphs benefit customers?
MySQL : What type of metrics can be correlated with the Query Analyzer?
SQL Server 2008 Top New Features :Extended Events
The extended events infrastructure provides an in-depth troubleshooting tool that enables administrators to address difficult-to-solve problems more efficiently. Administrators can investigate excessive CPU usage, deadlocks, and application time outs as well as many other issues. Extended events data can be correlated with Windows events data to obtain a more complete picture that will aid in problem resolution.
Provides detailed information about low level events in SQL Server
Reduces the time required to troubleshoot complex problems
Provides access to event data that has previously been difficult to obtain
SQL Server 2008 Top New Features :Backup Compression
Save storage space
Compressed backups can be stored on tape or on disk
Simple configuration using SQL Server Management Studio
Default state of all backups on a server to be compressed can be configured
SQL Server 2008 Top New Features :Partition Aligned Indexed Views
Indexed Views let SQL Server persist the results of a view, instead of having to dynamically combine the results from the individual queries in the view definition. Indexed Views can now be created to follow the partitioning scheme of the table that they reference. Indexed views that are aligned in this manner do not need to be dropped before a partition is switched out of the partitioned table, as was the case with SQL Server 2005 indexed views.
- Indexed views improve performance with very large partitioned tables, such as fact tables in data warehouses.
SQL Server 2008 Top New Features :Upgrade Advisor
Helps streamline the upgrade process by identifying upgrade issues in advance
Can be used to analyze both local and remote systems
Upgrade Advisor reports provide links to technical information that assist in addressing upgrade problems
SQL Server 2008 Top New Features :Server Group Management
Server Group management enables T-SQL queries to be issued against multiple servers from a single Central Management Server, which simplifies administration. Stream results of multi-server queries into a single result set or into multiple result sets enables the option of evaluating policies against a server group.
Management is centralized, so servers do not need to be configured individually
Provides a simpler administration model for policy evaluation
SQL Server 2008 Top New Features : Streamlined Installation
Easier to install SQL Server
Setup now advises of configuration problems such as installation pre-requisites, which helps streamline the installation process
SQL Server 2008 Top New Features: Hot-Add CPUs and Hot-Add Memory
Hot-add CPUs, a feature available with the 64-bit edition SQL Server Enterprise, allows CPUs to be dynamically added to servers as needed, without the need to shut down the server or limit client connections. Hot-add memory enables memory to be added in the same way.
- Dynamically add memory and processors to servers without incurring downtime
SQL Server 2008 Top New Features : Data Auditing
Data Auditing provides a simple way to track and log events relating to your databases and servers. You can audit logons, password changes, data access and modification, and many other events. Tracking these events helps maintain security and can also provide valuable troubleshooting information. The results of audits can be saved to file or to the Windows Security or Application logs for later analysis or archiving.
Enables compliance with security regulations
Simple configuration using SQL Server Management Studio
Minimal impact on performance because audit data is stored outside of SQL Server database files
External Key Management / Extensible Key Management
Helps organizations comply with data privacy regulations
Reduces administration requirements when there are multiple keys and certificates to manage
Standard interface supports third party hardware security modules
SQL Server 2008 Top New Features : Transparent Data Encryption
Transparent Data Encryption enables data to be stored securely by encrypting the database files. If the disks that contain database files become compromised, data in those files is protected because that data can only be de-encrypted by an authorized agent. SQL Server performs the encryption and de-encryption directly, so the process is entirely transparent to connecting applications. Applications can continue to read and write data to and from the database as they normally would. Backup copies of encrypted database files are also automatically encrypted.
Implements strong encryption keys and certificates to secure data
Applications do not need to be modified to support Transparent Data Encryption
Enables compliance with data privacy regulations
Does not increase the size of the database
SQL Server 2008 Top New Features :Resource Governor
The Resource Governor enables administrators to control and allocate CPU and memory resources to high priority applications. This enables predictable performance to be maintained and helps avoid performance from being negatively affected by resource-intense applications or processes
Prioritize applications, users, and computers competing for the same resources
Prevent runaway queries that hold resources for extended periods of time
Limitations are not enforced when there is no competition for resources
SQL Server 2008 Top New Features :Data Compression
Data compression reduces the amount of storage space needed to store tables and indexes, which enables more efficient storage of data. Data Compression does not require changes be made to applications in order to be enabled.
Save disk storage
Enable compression option for individual tables or indexes
Configuration is easy using the Data Compression wizard
Applications do not need to be reconfigured as SQL Server handles compression and decompression of data
Compression can improve disk I/O and memory utilization
SQL Server 2008 Top New Features : Performance Data Collection (Data Collector)
Setup wizard makes configuration simple
Data collection is automated by using set of SQL Server Agent jobs and SQL Server Integration Services packages
Management is centralized so data collection can be easily configured and results can be viewed in one place
Performance impact is minimal because the data collected can be cached and uploaded to the data warehouse later
SQL Server 2008 Top New Features : Policy-Based Management
Management is centralized, thereby reducing the need to configure each server separately
Administration is simplified, reducing the effort required to maintain standardization and compliance, even in complex environments
Configuration is straightforward and can be done entirely within SQL Server Management Studio
Out-of-the-box predefined policies make it easy to get started
Backwards compatibility supports managing instances of SQL Server 2008, SQL Server 2005, and SQL Server 2000
When a query is sent to the database and an index is notbeing used, what type of execution is taking place?
What are the pros and cons of using triggers?
A trigger is one or more statements of SQL that are being executed in event of data modification in a table to which the trigger belongs. Triggers enhance the security, efficiency, and standardization of databases.
Triggers can be beneficial when used:
– to check or modify values before they are actually updated or inserted in the database. This is useful if you need to transform data from the way the user sees it to some internal database format.
– to run other non-database operations coded in user-defined functions
– to update data in other tables. This is useful for maintaining relationships between data or in keeping audit trail information.
– To check against other data in the table or in other tables. This is useful to ensure data integrity when referential integrity constraints aren’t appropriate, or when table check constraints limit checking to the current table only.
What are the advantages of views?
- Hide data complexity.
- Simplify commands for the user.
- Present the data in a different perspective from that of the base table.
- Store complex queries.
What is an Oracle sequence?
What is an Oracle view?
What is Oracle table?
Can a tablespace hold objects from different schemes?
Yes.
What are Schema Objects?
Explain the relationship among database, tablespace and data file.
What is SYSTEM tablespace and when is it created?
created when the database is created. The SYSTEM tablespace always contains the data
dictionary tables for the entire database.
What is a tablespace?
A tablespace is used to grouped related logical structures together.
What are the components of logical database structure of Oracle database?
What are the components of physical database structure of Oracle database?
Introduction to Full-Text Search -In Microsoft SQL Server 2005
In Microsoft SQL Server 2005, full-text search delivers enterprise search functionality. Significant enhancements in the areas of performance, manageability, and functionality deliver exceptional search capabilities for applications of any size.
The performance benefit of using full-text search can be best realized when querying against a large amount of unstructured text data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.
You can build full-text indexes on columns that contain char, varchar and nvarchar data. Full-text indexes can also be built on columns that contain formatted binary data, such as Microsoft Word documents, stored in a varbinary(max) or image column. You cannot use the LIKE predicate to query formatted binary data.
To create a full-text index on a table, the table must have a single, unique not null column. For example, consider a full-text index for the Document table in Adventure Works in which the DocumentID column is the primary key column. A full-text index indicates that the word "instructions" is found at word number 24 and word number 44 in the DocumentSummary column for the row associated with a DocumentID of 3. This index structure supports an efficient search for all items containing indexed words and advanced search operations, such as phrase searches and proximity searches. For more information, see Document Table (AdventureWorks).
When processing a full-text query, the search engine returns to SQL Server the key values of the rows that match the search criteria. If you want to use a full-text query to find the documents that include the word "instructions", the DocumentID values of 3, 4, 6, 7 and 8 are obtained from the full-text index. SQL Server then uses these keys to return the matching rows.
Using SESSION_USER to return the user name of the current session
The following example declares a variable as nchar, assigns the current value of SESSION_USER to that variable, and then prints the variable with a text description.
DECLARE @session_usr nchar(30);
SET @session_usr = SESSION_USER;
SELECT 'This session''s current user is: '+ @session_usr;
GO
ISDATE() TSQL Return Value
| Column value (varchar) | ISDATE return value |
|---|---|
| NULL | 0 |
| Abc | 0 |
| 100, -100, 100 a, or 100.00 | 0 |
| .01 | 0 |
| -100.1234e-123 | 0 |
| .231e90 | 0 |
| $100.12345, - $100.12345, or $-1000.123 | 0 |
| as100 or 1a00 | 0 |
| 1995-10-1, 1/20/95, 1995-10-1 12:00pm, Feb 7 1995 11:00pm, 1995-10-1, or 1/23/95 | 1 |
| 13/43/3425 or 1995-10-1a | 0 |
| $1000, $100, or $100 a | 0 |
HOST_ID() -TSQL Example
CREATE TABLE Orders
(OrderID int PRIMARY KEY,
CustomerID nchar(5) REFERENCES Customers(CustomerID),
TerminalID char(8) NOT NULL DEFAULT HOST_ID(),
OrderDate datetime NOT NULL,
ShipDate datetime NULL,
ShipperID int NULL REFERENCES Shippers(ShipperID));
GO
HOST_NAME() T-SQL Example
Examples
The following example creates a table that uses HOST_NAME() in a DEFAULT definition to record the workstation name of computers that insert rows into a table recording orders.
CREATE TABLE Orders
(OrderID int PRIMARY KEY,
CustomerID nchar(5) REFERENCES Customers(CustomerID),
Workstation nchar(30) NOT NULL DEFAULT HOST_NAME(),
OrderDate datetime NOT NULL,
ShipDate datetime NULL,
ShipperID int NULL REFERENCES Shippers(ShipperID));
GO
Wildcard character - TSQL
| 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. |
| [^] | 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. |
Example:t-SQL Using generic syntax for finding gaps in identity values
-- The illustrative example starts here.
SET IDENTITY_INSERT tablename ON
DECLARE @minidentval column_type
DECLARE @maxidentval column_type
DECLARE @nextidentval column_type
SELECT @minidentval = MIN($IDENTITY), @maxidentval = MAX($IDENTITY)
FROM tablename
IF @minidentval = IDENT_SEED('tablename')
SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
FROM tablename t1
WHERE $IDENTITY BETWEEN IDENT_SEED('tablename') AND
@maxidentval AND
NOT EXISTS (SELECT * FROM tablename t2
WHERE t2.$IDENTITY = t1.$IDENTITY +
IDENT_INCR('tablename'))
ELSE
SELECT @nextidentval = IDENT_SEED('tablename')
SET IDENTITY_INSERT tablename OFF
-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column
-- called id_num, which is an increasing identification number, and the
-- second column called company_name.
-- This is the end of the illustration example.
-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF OBJECT_ID ('dbo.img', 'U') IS NOT NULL
DROP TABLE img
GO
CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname)
INSERT img(company_name) VALUES ('New Moon Books')
INSERT img(company_name) VALUES ('Lucerne Publishing')
-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON
DECLARE @minidentval smallint
DECLARE @nextidentval smallint
SELECT @minidentval = MIN($IDENTITY) FROM img
IF @minidentval = IDENT_SEED('img')
SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
FROM img t1
WHERE $IDENTITY BETWEEN IDENT_SEED('img') AND 32766 AND
NOT EXISTS (SELECT * FROM img t2
WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img'))
ELSE
SELECT @nextidentval = IDENT_SEED('img')
SET IDENTITY_INSERT img OFF
Examples :Using the IDENTITY property with CREATE TABLE
USE AdventureWorks
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL
DROP TABLE new_employees
GO
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)
INSERT new_employees
(fname, minit, lname)
VALUES
('Karin', 'F', 'Josephs')
INSERT new_employees
(fname, minit, lname)
VALUES
('Pirkko', 'O', 'Koskitalo')
Examples :Listing the advanced configuration options
GO
EXEC sp_configure 'show advanced option', '1';
Example : Using nested cursors to produce report output
DECLARE @vendor_id int, @vendor_name nvarchar(50),
@message varchar(80), @product nvarchar(50)
PRINT '-------- Vendor Products Report --------'
DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From Vendor: ' +
@vendor_name
PRINT @message
-- Declare an inner cursor based
-- on vendor_id from the outer cursor.
DECLARE product_cursor CURSOR FOR
SELECT v.Name
FROM Purchasing.ProductVendor pv, Production.Product v
WHERE pv.ProductID = v.ProductID AND
pv.VendorID = @vendor_id -- Variable value from the outer cursor
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product
IF @@FETCH_STATUS <> 0
PRINT ' <
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = ' ' + @product
PRINT @message
FETCH NEXT FROM product_cursor INTO @product
END
CLOSE product_cursor
DEALLOCATE product_cursor
-- Get the next vendor.
FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor
Examples :Using simple cursor and syntax
DECLARE vend_cursor CURSOR
FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor
System stored procedures Description
Returns a list of cursors currently visible on the connection and their attributes.
sp_describe_cursor
Describes the attributes of a cursor, such as whether it is a forward-only or scrolling cursor.
sp_describe_cursor_columns
Describes the attributes of the columns in the cursor result set.
sp_describe_cursor_tables
Describes the base tables accessed by the cursor.