update/replace column content

//mySQL

UPDATE [table_name]
set [column_name]=REPLACE([column_name], '[before]', '[after]')


UPDATE s_his
set s_his_date=REPLACE(s_his_date, 'Dec 7, 2011', '2011-12-07')

date calculation

//mySQL


SELECT CURDATE( )
-------------------
CURDATE( )
2011-12-29

SELECT CURDATE( ) -1
-------------------
CURDATE( ) -1
20111228

SELECT now( )
-------------------
now( )
2011-12-29 10:29:31

SELECT now( )-1
-------------------
now( )-1
20111229104426.000000

SQL Language (Speed Test)

#Server version: 5.0.51b-community-nt-log
#MySQL client version: 5.0.51a
#Used PHP extensions: mysql

SELECT
s_day.s_day_id,
s_day.s_day_yesterday,
s_day.s_day_date,
s_bwibbu.s_bwibbu_id,
s_bwibbu.s_bwibbu_name,
s_bwibbu.s_bwibbu_per,
s_bwibbu.s_bwibbu_yield,
s_bwibbu.s_bwibbu_pbr,
s_bwibbu.s_bwibbu_date,
s_cv.s_cv_id,
s_cv.s_cv_cdiv,
s_cv.s_cv_eps1q,
s_cv.s_cv_eps2q,
s_cv.s_cv_eps3q,
s_cv.s_cv_eps4q,
s_cv.s_cv_eps1y,
s_cv.s_cv_eps2y,
s_cv.s_cv_eps3y,
s_cv.s_cv_eps4y,
s_cv.s_cv_roe,
s_cv.s_cv_bvps

FROM `s_day`,`s_bwibbu`,`s_cv`
WHERE s_day.s_day_id=s_cv.s_cv_id
AND s_day.s_day_id=s_bwibbu.s_bwibbu_id

AND s_day.s_day_id=2303
AND s_bwibbu.s_bwibbu_id=2303
AND s_cv.s_cv_id=2303

AND s_day.s_day_date=s_bwibbu.s_bwibbu_date

ORDER BY s_day.s_day_date DESC
LIMIT 1

Showing rows 0 - 0 (1 total, Query took 0.1676 sec)
Showing rows 0 - 0 (1 total, Query took 0.1687 sec)
Showing rows 0 - 0 (1 total, Query took 0.1676 sec)
Showing rows 0 - 0 (1 total, Query took 0.1354 sec)
Showing rows 0 - 0 (1 total, Query took 0.1351 sec)
Showing rows 0 - 0 (1 total, Query took 0.1688 sec)
Showing rows 0 - 0 (1 total, Query took 0.1349 sec)
Showing rows 0 - 0 (1 total, Query took 0.1360 sec)
Showing rows 0 - 0 (1 total, Query took 0.1354 sec)
Showing rows 0 - 0 (1 total, Query took 0.1687 sec)

***********************************************

SELECT
s_day.s_day_id,
s_day.s_day_yesterday,
s_day.s_day_date,
s_bwibbu.s_bwibbu_id,
s_bwibbu.s_bwibbu_name,
s_bwibbu.s_bwibbu_per,
s_bwibbu.s_bwibbu_yield,
s_bwibbu.s_bwibbu_pbr,
s_bwibbu.s_bwibbu_date,
s_cv.s_cv_id,
s_cv.s_cv_cdiv,
s_cv.s_cv_eps1q,
s_cv.s_cv_eps2q,
s_cv.s_cv_eps3q,
s_cv.s_cv_eps4q,
s_cv.s_cv_eps1y,
s_cv.s_cv_eps2y,
s_cv.s_cv_eps3y,
s_cv.s_cv_eps4y,
s_cv.s_cv_roe,
s_cv.s_cv_bvps

FROM `s_day`,`s_bwibbu`,`s_cv`
WHERE s_day.s_day_id=s_cv.s_cv_id
AND s_day.s_day_id=s_bwibbu.s_bwibbu_id

AND s_day.s_day_id=2303
AND s_bwibbu.s_bwibbu_id=2303
AND s_cv.s_cv_id=2303

AND s_day.s_day_date = '100/12/23'
AND s_bwibbu_date = '100/12/23'

Showing rows 0 - 0 (1 total, Query took 0.0500 sec)
Showing rows 0 - 0 (1 total, Query took 0.0501 sec)
Showing rows 0 - 0 (1 total, Query took 0.0517 sec)
Showing rows 0 - 0 (1 total, Query took 0.0509 sec)
Showing rows 0 - 0 (1 total, Query took 0.0503 sec)
Showing rows 0 - 0 (1 total, Query took 0.0170 sec)
Showing rows 0 - 0 (1 total, Query took 0.0844 sec)
Showing rows 0 - 0 (1 total, Query took 0.0847 sec)
Showing rows 0 - 0 (1 total, Query took 0.0508 sec)
Showing rows 0 - 0 (1 total, Query took 0.0507 sec)

reconfig & restart network interfaces

#ubuntu

sudo vim.tiny /etc/network/interfaces

sudo /etc/init.d/networking restart

 * Running /etc/init.d/networking restart is deprecated because it may not enable again some interfaces
 * Reconfiguring network interfaces...

Orientation to Android Training

Orientation to Android Training

Welcome to Android Training. Here you'll find a collection of classes that aim to help you build great apps for Android, using best practices in a variety of framework topics.

Each class explains the steps required to solve a problem or implement a feature using code snippets and sample code for you to use in your apps.

What you see now is just the beginning. We plan to add many more classes, expand and refine existing classes, and build Training Courses that help you enhance your apps using objective-oriented collections of classes.

Ref>

https://developer.android.com/training/index.html

https://developer.android.com/index.html

utf8 big5

big5 >> utf-8
//PHP
$str=iconv("big5","UTF-8",$str);

utf-8 >> big5
//PHP
$str=iconv("UTF-8","big5",$str);

Error: 1010 SQLSTATE: HY000 (ER_DB_DROP_RMDIR)

/

Error: 1010 SQLSTATE: HY000 (ER_DB_DROP_RMDIR)
Message: Error dropping database (can't rmdir '%s', errno: %d)

//
C:\AppServ\MySQL\data\

shutdown

xp

使用方式: shutdown [-i | -l | -s | -r | -a] [-f] [-m \\computername] [-t xx] [-c
 "comment"] [-d up:xx:yy]

        沒有引數                顯示這個訊息 (和 -? 相同)
        -i                      顯示 GUI 介面，必須是第一個選項
        -l                      登出 (不能和 -m 選項一起使用)
        -s                      電腦關機
        -r                      關機並重新啟動電腦
        -a                      中止系統關機
        -m \\\\computername     從遠端進行關機/重新啟動/中止
        -t xx                   將關機等候時間設定成 xx 秒
        -c "comment"            關機註解 (最多 127 個字元)
        -f                      強制關閉執行中的應用程式，不顯示警告
        -d [u][p]:xx:yy         關機原因代碼
                                u 是使用者代碼
                                p 是預先計劃的關機代碼
                                xx 是主要原因代碼 (小於 256 的正整數)
                                yy 是次要原因代碼 (小於 65536 的正整數)

lamp-server

$ sudo apt-get install lamp-server^

$ sudo vim.tiny /var/www/phpinfo.php

<? php
phpinfo();

?>

$ sudo /etc/init.d/apache2 restart

//localhost/phpinfo.php

$ sudo apt-get install libapache2-mod-auth-mysql

$ sudo apt-get install libapache2-mod-auth-mysql phpmyadmin
[*]apache2

VNC

//root
$ apt-get install tightvncserver

$ sudo adduser vusr


//vncusr
$ vncserver

http://www.realvnc.com/products/download.html
Free Edition

VNC Viewer vnc-4_1_3-x86_win32
a.b.c.d:5901

login as: vusr
vusr@10.11.12.210's password: ***********
Welcome to Ubuntu 11.04 (GNU/Linux 2.6.38-8-server x86_64)

 * Documentation:  http://www.ubuntu.com/server/doc

  System information as of Mon Nov 28 22:40:11 CST 2011


  System load:  0.27              Processes:           118
  Usage of /:   2.5% of 67.38GB   Users logged in:     1
  Memory usage: 2%                IP address for eth0: 10.11.12.210
  Swap usage:   0%

  Graph this data and manage this system at https://landscape.canonical.com/
Last login: Mon Nov 28 22:18:39 2011 from 10.11.12.218
vusr@u1104s64:~$ vncserver

You will require a password to access your desktops.

Password:
Warning: password truncated to the length of 8.
Verify:
Would you like to enter a view-only password (y/n)? y
Password:
Warning: password truncated to the length of 8.
Verify:
xauth:  file /home/vusr/.Xauthority does not exist

New 'X' desktop is u1104s64:1

Creating default startup script /home/vusr/.vnc/xstartup
Starting applications specified in /home/vusr/.vnc/xstartup
Log file is /home/vusr/.vnc/u1104s64:1.log

vusr@u1104s64:~$


//terminal turn on
vncserver

//default 5901 port

//vncpasswd

adduser

//ubuntu

root@host:~$ sudo adduser abc
Adding user `abc' ...
Adding new group `abc' (1001) ...
Adding new user `abc' (1001) with group `abc' ...
Creating home directory `/home/abc' ...
Copying files from `/etc/skel' ...
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully
Changing the user information for abc
Enter the new value, or press ENTER for the default
        Full Name []:
        Room Number []:
        Work Phone []:
        Home Phone []:
        Other []:
Is the information correct? [Y/n] y
root@host:~$

ubuntu-11.04-server-amd64 GUI

//ubuntu-11.04-server-amd64

//GUI
sudo apt-get install x-window-system-core

sudo apt-get install gdm/xdm/kdm

sudo apt-get install ubuntu-desktop/kubuntu-desktop/xubuntu-desktop
sudo apt-get install gnome-core/kde-core/xfce4

//sudo reboot now

//
//sudo apt-get install firefox/gaim/xmms
//apt-get install firefox-3.5-gnome-support

//Firefox 4
sudo add-apt-repository ppa:mozillateam/firefox-stable
sudo apt-get update
sudo apt-get install firefox ubufox

//software center
wget https://launchpad.net/ubuntu/+source/software-center/2.1.5/+build/1876109/+files/software-center_2.1.5_all.deb https://launchpad.net/~guido-iodice/+archive/guiodiclucid/+files/aptdaemon_0.31+bzr403-0ubuntu1~ppa1_all.deb https://launchpad.net/~guido-iodice/+archive/guiodiclucid/+files/python-aptdaemon-gtk_0.31+bzr403-0ubuntu1~ppa1_all.deb https://launchpad.net/~guido-iodice/+archive/guiodiclucid/+files/python-aptdaemon_0.31+bzr403-0ubuntu1~ppa1_all.deb

//

TIMESTAMP Properties

Ref>
http://dev.mysql.com/doc/refman/5.1/en/timestamp.html

//

UPDATE 'table_name'
SET 'column_name'=current_time
WHERE 'column_name'=0000-00-00 00:00:00

import CSV

//phpMyAdmin - 2.10.3

>Import
>CSV

//default

Fields terminated by ;
Fields enclosed by "
Fields escaped by \
Lines terminated by auto
Column names


//
"49956";"";"";"";"";"";"";"";"2011-11-22";"31.35";"32.00";"30.05";"30.05";"1635000";"30.05";"";"";"";"";"";""
"4956";"";"";"";"";"";"";"";"2011-11-21";"34.60";"34.60";"32.30";"32.30";"1488000";"32.30";"";"";"";"";"";""

//
1234;;;;;;;;2011-11-23;32.55;32.70;31.70;31.75;11375000;31.75;;;;;;
2234;;;;;;;;2011-11-23;32.55;32.70;31.70;31.75;11375000;31.75;;;;;;
3234;;;;;;;;2011-11-23;32.55;32.70;31.70;31.75;11375000;31.75;;;;;;
4234;;;;;;;;2011-11-23;32.55;32.70;31.70;31.75;11375000;31.75;;;;;;

OpenStack

OpenStack makes its services available through Amazon EC2/S3 compatible APIs and hence the client tools written for AWS can be used with OpenStack as well.

There are 3 main service families under OpenStack

Compute Infrastructure (Nova)
Storage Infrastructure (Swift)
Imaging Service (Glance)


OpenStack Starter Guide
http://docs.openstack.org/diablo/openstack-compute/starter/content/

//This is a tutorial style beginner's guide for OpenStack™ on Ubuntu 11.10, Oneiric Ocelot. The aim is to help the reader in setting up a minimal installation of OpenStack.


//MoinMoin Wiki

1-New Features in SQL Server 2008

Exam objectives in this chapter:

■ New Feature Overview

■ Reporting Services

Introduction

>A Word About the Test

New Feature Overview

>Installation

The SQL Server installation tool is used to create a new installation, or to make changes to the existing installation.

>Compressed Backups

When using T-SQL to create the backup, you’d use:

BACKUP DATABASE [robby] TO DISK = N'C:\Backup\robby.bak' WITH

NOFORMAT, NOINIT, NAME = N'robby-Full Database Backup', SKIP,

NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

The Copy Only Backup is especially useful for taking “one off ” backups for development or testing – the advantage is it doesn’t affect transaction log backups or differential backups. Keep in mind it also cannot serve as a base for differential or transaction log backups when restoring either.

The T-SQL procedure to do a Copy Only Backup would look like:

BACKUP DATABASE [robby] TO DISK = N'C:\Backup\robby.bak' WITH COPY_ONLY,

NOFORMAT, NOINIT, NAME = N'robby-Full Database Backup', SKIP, NOREWIND,

NOUNLOAD, STATS = 10

GO

>Enhanced Configuration and Management of Audits

Auditing is available using the new Change Data Capture (CDC) feature. CDC can be used to capture insertions, updates, and deletes in an SQL table in a database and place the changes in another table.

The following SQL code demonstrates how to configure CDC for auditing of a table in a database:

--Activate CDC

EXEC sys.sp_cdc_enable_db_change_data_capture

--Enable CDC on table

EXEC sys.sp_cdc_enable_table_change_data_capture @source_schema = 'dbo',

@source_name = 'myTable', @role_name = 'cdc'

>New Table Value Parameter

Passing tables as parameters has been a long time coming. The new table type can be passed to a stored procedure. This will solve quite a few problems!

Here’s an example:

To declare a Table User Defined Type in the database:

create type MyTableType as table

(

Name varchar(150),

City varchar(20),

AddressID int

)

And here's the stored procedure that consumes it:

create procedure InsertFriends

(

@MyTable MyTableType readonly

)

as

insert

into Friends (Name, city, AddressID)

select Name, city, AddressID

from @MyTable;

--To fill create and fill the temp table:

declare @MyBestFriends_temp MyTableType

insert into @MyBestFriends_temp values ('Debbie', 'Havertown', 2)

insert into @MyBestFriends_temp values ('Chris', 'Philadelphia', 1)

insert into @MyBestFriends_temp values ('Tom', 'Garden City', 11)

insert into @MyBestFriends_temp values ('Greg', 'Lansdowne', 6)

insert into @MyBestFriends_temp values ('Steve', 'Wilmington', 6)

--And finally, to execute:

execute InsertFriends @MyBestFriends_temp

>FileStream Data Types

the database engine will store all of the data associated with the column in a disk file as opposed to the actual database. You might have used a similar home-grown scheme in earlier versions of SQL, but this integrates everything nicely into SQL Server.

must first enable it. This is accomplished via the sp_FILESTREAM_configure system stored procedure, or via the GUI in Management Studio under advanced settings.

Once FileStream is enabled, a file group must be added to the database in order for it to be able to use FileStream data types.

FileStream has the following limitations:

■ Database mirroring cannot be configured in databases with FileStream data.

■ Database snapshots are not supported for FileStream data.

■ Native encryption is not possible by SQL Server for FileStream data.

>Sparse Column Support

Sparse columns allow for the optimized storage of null columns. Sparse columns can be a good thing, but be sure to enable them only on columns that contain sparse data, or your storage requirements may go up instead of down.

To enable a column as a sparse column, use the create statement in SQL or change the properties in the column to Sparse.

The SQL to accomplish this is as follows:

CREATE TABLE dbo.Table_1

(

OID int NULL,

MyValue1 varchar(50) SPARSE NULL

) ON [PRIMARY]

GO

>Encryption Enhancements

Transparent data encryption (TDE) is available in SQL Server 2008.TDE allows you to easily encrypt the contents of your database and is designed to provide protection to the entire database. With TDE, you can encrypt the contents of your database with no changes to your application.

To enable TDE, you need to first create a master key and a certificate. Once the master key and certificate are set up, use the following to enable TDE:

ALTER DATABASE myDatabase SET ENCRYPTION ON

Once TDE is enabled, it’s designed to be transparent to the application.

>>Key Management and Encryption

Encryption requires keys to secure the database. These keys must be protected and backed up 

>High Availability 

Mirroring has been improved, Hot Add CPU has been added, and Hot Add Memory is available. 

SQL Server 2008 can also take advantage of the new failover clustering enhancements available in Windows 2008.

failover clustering is not available in the Web edition or workgroup edition.

>Performance

>>Performance Data Management

Performance data management is a new tool available in SQL Server 2008.

Performance data management allows you to collect performance-related data from your SQL Servers over time. Performance data management consists of a warehouse database (for storing the results) and the data collector, and collection is usually scheduled to run at specific times.

>>Resource Governor (similar to Query Governor)

Resource Governor is a nice new feature to help manage workload, designed to limit the resources available to a process. The way the Resource Governor works is the DBA creates a workload group and a resource pool. Workload groups are containers to hold user sessions. Workload groups are mapped to resource pools.

User sessions are mapped to workload groups based on classifier functions. The classifier functions can be by IP address, username, application name, and so on.

>>Freeze Plan

Plan freezing is meant to offer greater predictability when it comes to executing a particular query in SQL Server 2008.

basically a cached plan that is kept around and used for a particular query.

First, you need to create the data warehouse

The actual how to is found later in this book; it’s worthwhile knowing how to use this new feature.

Figure A Data Collection Report is a sample of one of the reports available in the data collection set.

the data collection can be used only on SQL Server 2008 servers. It will not work if you connect to a SQL 2005 or SQL 2000 database server.

>SQL Server 2008 Declarative Management Framework

Declarative Management Framework (DMF). This is basically a new policy-based management system for SQL Server 2008.

The DMF is very similar to Windows policy; 

you can enforce a particular naming convention for stored procedures and a different naming convention for tables.

There are three main components to the DMF: policies, conditions, and facets.

To create or apply a policy, you would right click on the policy node and proceed from there. 

>Development Improvements

>>LINQ Support

basically a mapping of the database object to programming objects. This allows for a more object-oriented approach to dealing with database objects 

>>MERGE Statement

The MERGE statement performs insert, update, or delete operations on a target table based on the results of a join to a source table.

For example, you can synchronize two tables by inserting, updating, or deleting all of the rows in one table based on differences in the other table.

Here is an example of MERGE code:

MERGE MyDatabase.Inventory AS target

USING (SELECT ProductID, SUM(OrderQty) FROM mySales.Orders AS o

JOIN mySales.mySalesOrderHeader AS soh

ON o.mySalesOrderID = soh.mySalesOrderID

AND soh.OrderDate = @OrderDate

GROUP BY ProductID) AS source (ProductID, OrderQty)

ON (target.ProductID = source.ProductID)

WHEN MATCHED AND target.Quantity - source.OrderQty <= 0

THEN DELETE

WHEN MATCHED

THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,

target.ModifiedDate = GETDATE()

OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate,

Deleted.ProductID,

Deleted.Quantity, Deleted.ModifiedDate;

>Spatial Data Type

The spatial data type is a new data type

spatial data type is used to store location-based data

do geocoding.

>Analysis Services Improvements

In Analysis Services, a better cube designer, dimension and attribute designer, and enhanced data mining structures.

>ETL/SSIS Enhancements

Many of the new features carry over to SSIS, like Change Data Capture and the MERGE statement. Another good new feature is being able to script in C#

Reporting Services

SQL Server Reporting Services. it now supports rich text format.

>No Longer Requires IIS

>Better Graphing

Reporting Services supports graphing, 

>Export to Word Support

Reporting Services now has the ability to export to Word. 

>Deprecated Features

■ BACKUP {DATABASE | LOG} WITH PASSWORD

■ BACKUP {DATABASE | LOG} WITH MEDIAPASSWORD

■ RESTORE {DATABASE | LOG} … WITH DBO_ONLY

■ RESTORE {DATABASE | LOG} WITH PASSWORD

■ RESTORE {DATABASE | LOG} WITH MEDIAPASSWORD

■ 80 compatibility level and upgrade from version 80.

■ DATABASEPROPERTY

■ WITH APPEND clause on triggers

■ Default setting of disallow results from triggers option = 0

■ sp_dboption

■ FASTFIRSTROW hint

■ sp_addremotelogin

■ sp_addserver

■ sp_dropremotelogin

■ sp_helpremotelogin

■ sp_remoteoption

■ @@remserver

■ SET REMOTE_PROC_TRANSACTIONS

■ sp_dropalias

■ SET DISABLE_DEF_CNST_CHK

■ SET ROWCOUNT for INSERT, UPDATE, and DELETE statements

■ Use of *= and =*

■ COMPUTE / COMPUTE BY

■ sys.database_principal_aliases

■ sqlmaint Utility

■ The RAISERROR (Format: RAISERROR integer string) syntax is deprecated.

these will still work in SQL Server 2008, they are not a recommended best practice.

>Discontinued Features

■ sp_addalias

■ Registered Servers API

■ DUMP statement

■ LOAD statement

■ BACKUP LOG WITH NO_LOG

■ BACKUP LOG WITH TRUNCATE_ONLY

■ BACKUP TRANSACTION

■ 60, 65, and 70 compatibility levels

■ DBCC CONCURRENCYVIOLATION

■ sp_addgroup

■ sp_changegroup

■ sp_dropgroup

■ sp_helpgroup

■ Northwind and pubs

■ Surface Area Configuration Tool

■ sp_makewebtask

■ sp_dropwebtask

■ sp_runwebtask

■ sp_enumcodepages

Ref>
ISBN 13: 978-1-59749-420-5

1-New Features in SQL Server 2008-QA

Self Test

1. You are setting up security for your new SQL Server 2008 installation.
Management is concerned about security. What approach should you take to ensure security settings are optimal?
A. Use the Surface Area Configuration Tool to secure the installation
B. Use the new Security Analysis tool to secure the installation
C. Use SQL Server Configuration Manager to secure the installation
D. Use Windows Service Manager to enable and disable the appropriate services

2. You have been tasked with setting up standards for your SQL Server 2008 installation. You need to enforce a table naming convention. What is the best way to accomplish this in your SQL Server 2008 environment?
A. Use Windows Group Policy
B. Create DDL Triggers
C. Create DML Triggers
D. Create a Declarative Management Framework policy

3. You have been asked to create a backup of your production database and restore it on a development server. Your production server is using the full recovery model. Full backups are taken Monday and Wednesday. Transaction log backups are taken every hour. Today is Friday. The backup needs to be created as quickly as possible. What’s the fastest way to get the latest database copy while minimizing impact to production?
A. Create a normal backup. Use that to restore to development.
B. Create a Copy Only Backup. Use that to restore to development.
C. Use the Wednesday backup. Restore the transaction log backups since Wednesday.
D. Copy the .mdf and log files and use SP_attach.

4. You have a SQL Server 7.0 database and would like to move it to a new SQL Server 2008 instance. The database is part of an FDA-validated system and cannot be changed at all? How can this be accomplished?
A. Restore the 7.0 database on your new server and set the compatibility mode to 7.
B. You must upgrade the database to SQL 2005 or greater.
C. Restore the 7.0 database on your new server and set the compatibility mode to 6.5.
D. Copy the .mdf and log files and use SP_attach.

5. You have an application that is being upgraded from SQL Server 2005 to SQL Server 2008. You notice that some stored procedures are not working correctly. An excerpt is as follows:
SELECT *
FROM Territories, Region
WHERE territories.regionid *= region.regionid
What should you do to resolve the issue?
A. There is no issue. The problem lies elsewhere.
B. The join syntax is incorrect. Replace with left join.
C. The select is incorrect. You need to enumerate the fields.
D. The where clause should be = not =.

6. Your disk is almost full on the transaction log drive for a database server.
How can you resolve this issue?
A. Use BACKUP LOG WITH TRUNCATE_ONLY
B. Change the mode to simple and shrink the log
C. Reinstall SQL
D. Drop the database and restore from the last backup

7. You want to enforce a standard naming convention for stored procedures.
What’s the best way to do this in SQL Server 2008?
A. Create a DDL trigger
B. Use the performance data warehouse
C. Create DML triggers
D. Use the SQL Server 2008 Declarative Management Framework

8. You want to enforce a standard naming convention for tables and stored procedures. Your company has two SQL 2008 Servers and 60 SQL 2005 Servers. You need to use the same solution on all servers. What’s the best way to do this in SQL Server 2005 and SQL Server 2008?
A. Create a DDL trigger for all servers
B. Use the performance data warehouse
C. Create DML triggers
D. Use the SQL Server 2008 Declarative Management Framework

9. You have a database table with a varchar(600) field in it. Most of the records in the table have a null value for this field. How can you save space?
A. Move the data into a second table
B. Use sparse columns
C. Install a third-party tool on the machine to compress the data
D. Use the SQL Server 2008 Declarative Management Framework

10. You have a database table with a FileStream field in it. Most of the records in the table have a null value for this field. What’s the best way to save space?
A. Move the data into a second table
B. Use sparse columns
C. Use the SQL Server 2008 Declarative Management Framework
D. None of the above

11. You need to store images in for a Web site using SQL Server 2008. How can you accomplish this?
A. Use a FileStream data type, and the images will be stored on disk
B. Use a varchar data type and store the images in that field
C. Use an int data type and store the images in that field
D. Use an nchar data type and store the images in that field

12. You are responsible for a system that is used for both online transaction processing (OLTP) and reporting. When reports run on the server, the OLTP process slows way down. How can you allow reports to be run on the server and minimize impact to the OLTP processes?
A. Use the Resource Governor
B. Use a DDL trigger
C. Use a DML trigger
D. Use processor affinity masks

13. You are creating an application to track crime in different locations throughout a large city. What data type could prove useful for storing location data (longitude and latitude)?
A. Varchar
B. int
C. Char
D. Spatial

14. You are running out of space on the drive used to store backups. All of the servers use the same network location. What can you do to save space with your backups while maintaining the same number of backups?
A. Use data compression
B. Use compressed backups
C. Use full backups
D. Use a third-party tool

15. You need to store sensitive data in your SQL Server database. The application has already been written and works fine. What’s the easiest way to do this without having to change your application?
A. Modify the stored procedures to use xp_encryptstring
B. Use transparent data encryption
C. Use a third-party tool
D. Use a trigger

16. Within your application, you need to log all changes to one table. DDL and DML changes must be logged. What’s the best approach to solve this problem?
A. Use the built-in auditing capability
B. Create a DDL trigger
C. Create a DML trigger
D. This cannot be accomplished

17. You have a server that supports Hot Add CPU. The current CPU utilization is 95 to 100 percent most of the time. The server is mission-critical and cannot be shut down. SQL Server 2008 Standard Edition is installed. What should you do?
A. Use the Hot Add CPU feature to add another CPU
B. Use the Hot Add CPU feature to add two CPUs
C. Add more memory to the server
D. Schedule an outage and add another CPU to the server

18. You are contemplating using data compression on a table. You would like to know how much space this will save. How can you determine the savings?
A. View the table properties
B. Enable compression, then check the table size
C. Use sp_estimate_data_compression_savings
D. Use sp_check_compression

19. You have a server that supports Hot Add Memory. Performance is sluggish, and you believe adding more memory will help. The server is mission-critical and cannot be shut down. SQL Server 2008 Standard Edition is installed.
What should you do?
A. Use the Hot Add CPU feature to add another CPU
B. Use the Hot Add CPU feature to add two CPUs
C. Add more memory to the server.
D. Schedule an outage and add memory to the server.

20. You have a SQL Server 2008 installation, and you want to create a highavailability solution. What are the ideal approach(es) to solve this problem?
A. Backup and restore
B. Replication
C. Mirroring
D. Clustering


Ref>
ISBN 13: 978-1-59749-420-5