Monday, February 22, 2010

On Google technologies

This is record of some interesting technologies by Google.



Google BigQuery 
Note: Currently, BigQuery is available by invitation only and users must be added to the service before they can interact with the BigQuery API, the browser tool, or the command-line tool. If you are interested in signing up for BigQuery, please fill out this form.


Google BigQuery Service allows you to run SQL-like queries against very large datasets, with potentially billions of rows. This can be your own data, or data that someone else has shared for you. BigQuery works best for interactive analysis of very large datasets, typically using a small number of very large, append-only tables. For more traditional relational database scenarios, you might consider using Google Cloud SQL instead. You can use BigQuery through the bigquery browser tool, the bq command-line tool, or by making direct calls to the REST API. Learn more.



Google Cloud SQL 
Google Cloud SQL is web service that allows you to create, configure, and use relational databases with your App Engine applications. It is a fully-managed service that maintains, manages, and administers your databases, allowing you to focus on your applications and services.


By offering the capabilities of a MySQL database, the service enables you to easily move your data, applications, and services into and out of the cloud. This allows for high data portability and helps in faster time-to-market because you can quickly leverage your existing database (using JDBC and/or DB-API) in your App Engine application. 


To ensure that your critical applications and services are always running, Google Cloud SQL replicates data to multiple geographic regions to provide high data availability.


The service is currently in limited preview. We will publish pricing soon





Why Google+ will become Google's only productOr: How Google+ will crush everyone with 'featurization'
By Mike Elgan

"Computerworld - I finally understand Google's überstrategy for dominating the future of online everything.
Here it is: Coalesce all of its best products into a single super product that marginalizes smaller rivals to the point of irrelevance and clobbers Facebook with total superiority.
That single super product is Google+.
Why features beat products
The most important distinction in Silicon Valley is this: Is it a product or a feature?"


.................
"And that's how Google plans to dominate the future of the Internet. Many of the product categories for online services will be marginalized as Google makes these technologies mere features of Google+."





Google Fusion Tables
http://www.google.com/fusiontables/public/tour/index.html



Stumbled upon this less publicised technology called Fusion Tables. I was looking for some online database where I can store numbers that can be accessed and used by a web page. Basically, I was looking for something to replace MySQL or similar databases. I'm trying to avoid DB administration like having to create DB, Tables, fields or logging into a DB to update data etc. I was looking for a simple table interface with the power of a DB. And it looks like Google Fusion Tables is just that and more.


In particular, Fusion Tables can visualise the data and integrate with maps and so on.
https://sites.google.com/site/fusiontablestalks/stories




Here is an extract from Google:
"How is Fusion Tables different from spreadsheets? The goal of Fusion Tables, as with other database systems, is to manage larger amounts of data than spreadsheets typically do. This size difference leads to a focus on a different set of functionalities.


For example, Fusion Tables focuses more on bulk operations on the data (filtering, aggregation, merges). These operations are typically not necessary for smaller collections of data stored in spreadsheets.


In contrast, spreadsheets preserve complete flexibility in managing data -- you can put any value you want in any cell and work carefully to format your spreadsheet to make it look nice. Fusion Tables requires that the data be tabular with column headers and types for each column.


Sometimes there are more effective ways to look at your data than rows in a table. For example, when your data contains geographical locations, viewing the data on a map or as an intensity map (where each country is colored according to some attributes) may reveal patterns in the data that are otherwise very hard to see. Fusion Tables offers several visualizations that will help you explore your data more effectively. You will see them in the "Visualize" menu.


Finally, Fusion Tables allows you to combine data owned by different parties to make a new table. The visualizations and queries on that table can incorporate any or all of the data seamlessly."









Google Plus or Google+ 
https://plus.google.com/


The Facebook challenger has finally arrived! This is quite an exclusive club at the moment so it is by invitation only that you can get a Google+ access.


So what is it? It is Facebook and much more. The Google+ site will explain much more. 



But as a quick summary:
  • Google+ Circles - like Facebook friends, but you can control which group of friends can see which content you publish.
  • Google+ Streams - like Facebook wall, where you can publish your comments, pictures, videos
  • Google+ Hangout - like online group chat. You can invite people from different Circles.
  • Google+ Sparks - this will collect content from the web including articles and videos that you like and let you read or watch whenever you want.
Also here is a YouTube clip on Google+ 


10 ways Google+ can boost your brand
http://www.smh.com.au/small-business/smallbiz-tech/10-ways-google-can-boost-your-brand-20110831-1jkz4.html


Links to 63 Google+ Google Plus resources


 

Google TV
http://www.google.com/tv/




Getting Started With Google Voice

The basics: Google Voice gives you a local number with tons of rich features that becomes the one number you’ll use. You configure Google Voice with all your other phones — smart or dumb cell phones and landlines, at home or work – and, based on who’s calling, have Google Voice route the call directly to voicemail or any of your phones. If you don’t know where you’ll be — say, work, home, or mobile — Google Voice can ring all your numbers; you pick up the one that’s handiest.





Five Really Handy Google Command Line Tricks
"With the right commands, you can turn your favorite command-line text editor into a distraction-free Google Docs app, add new events to Google Calendar, upload images to Picasa or video to YouTube, backup your Google data, and more. Here's how it works.

On Friday, Google released GoogleCL, a saucy command line program that interacts with Google services from any *nix-friendly command-line prompt (on Windows, Mac, or Linux). We spent the weekend playing around with it, and now we're sharing a few of the coolest ways we've been putting it to use." ............





Tips on using Google Attachments


The link above contain tips on how to use some neat features in  Google Gmail regarding Attachments. In summary the tips are:
1) Drag and Drop attachments when writing email using Gmail. Currently works with Google Chrome and Mozilla Firefox browser.
2) Multiple selection of attachments is possible
3) Forget to attach a file to your email? If the contents of the email has any words like "attachment" or "attached", then Gmail will pop-up a prompt to remind you about adding the attachment.
4) Attachments can be downloaded or viewed online, eg in PDF format.
5) Find attachments in old email. In the "Search Mail" text box, type:
   has: attachment.pdf  or filename:file.txt










Google offers encrypted search with 'https' site








Google promises private browsing via SSL page.
Google has launched an encrypted version of its web site which it said will offer users more control over their online activities. The 'https' URL has not yet gone live in the UK, but is expected to do so shortly.


Evan Roseman, a software engineer at Google, said in a blog post that https://www.google.com/ will let web users surf in a "secure channel".







Google leaps language barrier with translator phone

"GOOGLE is developing software for the first phone capable of translating foreign languages almost instantly — like the Babel Fish in The Hitchhiker’s Guide to the Galaxy.
By building on existing technologies in voice recognition and automatic translation, Google hopes to have a basic system ready within a couple of years. If it works, it could eventually transform communication among speakers of the world’s 6,000-plus languages." ..............


Google Web Search
Tips on using Google Search


Here's a summary of the 10 tips:









  1. Use the “site:” operator to limit searches to a particular site. 
  2. Use Google as a spelling aid. 
  3. Use Google as a calculator. 
  4. Find out what time it is anywhere in the world. 
  5. Get quick currency conversions. 
  6. Use the OR operator. 
  7. Exclude specific terms with the – operator. 
  8. Search for specific document types. 
  9. Search within numerical ranges using the .. operator. 
  10. Area code lookup.

Another 9 tips from the same source above:
http://webworkerdaily.com/2010/05/03/9-more-simple-google-search-tricks/


  1. Use quote marks to search for exact phrases. 
  2. Use the wildcard operator to fill in the blanks.
  3. Track a package. 
  4. Track a flight. 
  5. Get a stock quote. 
  6. Get quick conversions of pretty much anything. 
  7. Find out what movies are showing in your city. 
  8. Use Google’s cache to retrieve the previous version of a page. 
  9. Get the weather forecast.

Various things  you can do via Google Search

Here is a summary of the features:









• Cached Links
View a snapshot of each page as it looked when we indexed it.
• Calculator
Use Google to evaluate mathematical expressions.
• Currency Conversion
Easily perform any currency conversion.
• Definitions
Use Google to get glossary definitions gathered from various online sources.
• File Types
Search for non-HTML file formats including PDF documents and others.
• I'm Feeling Lucky
Bypass our results and go to the first web page returned for your query.
• Images
See relevant images in your regular web search results.
• News Headlines
Enhances your search results with the latest related news stories.
• PhoneBook
Look up U.S. street address and phone number information.
• Product Search
To find a product for sale online, use Google Product Search.
• Q&A - New!
Use Google to get quick answers to straightforward questions.
• Search By Number
Use Google to access package tracking information, US patents, and a variety of online databases.
• Similar Pages
Display pages that are related to a particular result.
• Site Search
Restrict your search to a specific site.
• Spell Checker
Offers alternative spelling for queries.
• Stock Quotes
Use Google to get stock and mutual fund information.
• Street Maps
Use Google to find U.S. street maps.
• Travel Information
Check the status of an airline flight in the U.S. or view airport delays and weather conditions.
• Weather - New!
Check the current weather conditions and forecast for any location in the U.S.
• Web Page Translation 
Provides English speakers access to a variety of non-English web pages.
• Who Links To You?
Find pages that point to a specific URL.


GoogleSharing
GoogleSharing is a special kind of anonymizing proxy service, designed for a very specific threat. It ultimately aims to provide a level of anonymity that will prevent Google from tracking your searches, movements, and what websites you visit. GoogleSharing is not a full proxy service designed to anonymize all your traffic, but rather something designed exclusively for your communication with Google. Our system is totally transparent, with no special "alternative" websites to visit. Your normal work flow should be exactly the same.











Announcing Google TV: TV meets web. Web meets TV.

5/20/2010 10:00:00 AM
"If there’s one entertainment device that people know and love, it’s the television. In fact, 4 billion people across the world watch TV and the average American spends five hours per day in front of one*. Recently, however, an increasing amount of our entertainment experience is coming from our phones and computers. One reason is that these devices have something that the TV lacks: the web. With the web, finding and accessing interesting content is fast and often as easy as a search. But the web still lacks many of the great features and the high-quality viewing experience that the TV offers. ....................."


http://googleblog.blogspot.com/2010/05/announcing-google-tv-tv-meets-web-web.html



On Google Wave

This is record of some interesting articles on Google Wave

On 24 Nov 2011 - Google send out this email to  Wave users:
"Dear Wavers,
More than a year ago, we announced
 
 that Google Wave would no longer be developed as a separate product. At the time, we committed to maintaining the site at least through to the end of 2010. Today, we are sharing the specific dates for ending this maintenance period and shutting down Wave. As of January 31, 2012, all waves will be read-only, and the Wave service will be turned off on April 30, 2012. You will be able to continue exporting individual waves using the existing PDF export feature until the Google Wave service is turned off. We encourage you to export any important data before April 30, 2012.
If you would like to continue using Wave, there are a number of open source projects, including Apache Wave
 
. There is also an open source project called Walkaround that includes an experimental feature that lets you import all your Waves from Google. This feature will also work until the Wave service is turned off on April 30, 2012.
For more details, please see our help center.
Yours sincerely,
The Wave Team
"

The Complete Guide to Google Wave


http://completewaveguide.com/
http://completewaveguide.com/guide/The_Complete_Guide_to_Google_Wave
"Google Wave is a new web-based collaboration tool that's notoriously difficult to understand. This guide will help. Here you'll learn the ins and outs of how to use Google Wave to get things done with your group. This entire book is available to read for free online, with an electronic and upcoming print version available for purchase."......





Google Wave in Action: Real-World Use Case Studies

"A week ago we asked readers to tell us how they're using Google Wave in their daily lives, and despite a bit of "ha! no one's using Wave!" snarking on the Twitter, we got lots of interesting responses.
Unsurprisingly, most Wavers use it as a real-time wiki, but some take advantage of features unique to Wave, like inline and private replies, public tags, and gadgets. I featured the most unique use cases I got in a brand new chapter just added to The Complete Guide to Google Wave. The following is the text of the just-published Chapter 10, which describes ways in which a few people who don't work for Google are using Wave to get things done—with screenshots."........

Thursday, February 18, 2010

NotesMSSQL - Notes on Microsoft SQL Server



NotesMSSQL
===========

General / FAQ
Starting the server
Login to server
Stopping the server
Converting CSV to MSSQL
Visual Studio Database Project
Views and Stored Procedures
Transact SQL
Transact SQL Data Types vs DB Datatypes
Transact SQL functions
Checking if table, stored procedures, synonyms, etc EXISTS
sys.object constants
Configuring Permissions
Deleting tables, DB, etc
Timing
Bulk Insert Selected Column
How to use Merge in SQLserver 2008
Installing SQL Server 2008 with Visual Studio 2008
How to use SQL Server 2008 Projects in Visual Studio 2008
Link to other servers
Using SQL Server Import and Export Wizard (SSIS)
Backup and Restore Database with MS SQL Server Management Studio
Difference Between nVarChar vs VarChar and nChar vs Char
Unicode
Using Variables / Parameters in Stored Procedures
Dynamic SQL
Partitioned Tables
Using SQL Server Management Studio
SQL Server Indexes



General / FAQ
===============
Where is the actual data stored?
    :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Reference in MSDN:
T-Sql reference
    Enterprise Servers and Development - SQL Server - SQL Server 2005 Documentation - SQL Server 2005 Books Online - SQL Server Language Reference
    MSDN Library - Servers and Enterprise Development - SQL Server - SQL Server 2008 - Product Documentation - SQL Server 2008 Book Online - Database Engine - Technical Reference - Transact-SQL Reference
Sql Server Project
    Development Tools and Languages - Visual Studio 2005 - Visual Studio - .Net Framework Programming in Visual ... - Accessing Data - Creating SQL Server 2005 Objects in ...
Run a Data Generation Plan to generate data
http://msdn.microsoft.com/en-us/library/dd193262.aspx
    Development Tools and Languages - Visual Studio 2005 - Visual Studio Team System - Team Edition for Database Professionals - Generating Data with Data Generator - Data Generation Plans
    Development Tools and Languages - Visual Studio 2008 - Visual Studio Team System - Database Edition - Managing Changes to Datatabse and D.. - Verifying Existing Database code - Generate Test Data for Databases by ....
Database Tuning
    Enterprise Servers and Development - SQL Server - SQL Server 2005 Documentation - SQL Server 2005 Tutorials - SQL Server Tools Tutorials - Database Engine Tuning Advisor Tutorial
Export / Import Data - SQL Server Integration Services (SSIS)
    Enterprise Servers and Development - SQL Server - SQL server 2005 Documentation - SQL Server 2005 Books Online - SQL Server Overview - SQL Server Integration Services (SSIS)  
Export / Import Data - SQL Server Import and Export Wizard
    Enterprise Servers and Development - SQL Server - SQL server 2005 Documentation - SQL Server 2005 Books Online - SQL Server Integration Services (SSIS) - Designing and Creating Integration Services Packages - Creating Packagaes using the SQL server Import and Export Wizard.




Starting the server
====================

- net start server; or
- osql /Usa -P



Login to server
================
- use SQL Server Enterprise Manager
- use SQL Query Analyser
osql /U [login_id] /P [password] /S [servername]

Stopping the server
====================
SQL Server Enterprise Manager
SQL Server Service Manager
SHUTDOWN statement
net stop mssqlserver
Control Panel
CTRL+C


Converting CSV to MSSQL
=========================
1. Assume the CSV data file is available and it has first row as headings for the table.
2. Open the CSV file with MS Access 2007. Open up the table of data.
3. In MS Access, click Database Tools - SQL Server. This opens up an upsizing wizard.
4. In the wizard, select Create New DB - click Next.
5. In the wizard, choose DB server, Use Trusted Connection, enter a name for the new SQL DB, click Next.
6. In the wizard, select the Tables to be exported - click Next.
7. In the wizard, select the following: Indexes, Validation Rules, Defaults,
   Table Relationships,  Use DRI, click Next.
7. In the wizard, select No Application Changes - click Next. Click Finish. The DB gets converted into MSSQL.

Note that the Table Structure and Data would be migrated to MSSQL in the above procedure.
BUT, for very large datasets, when there are incompatibilities with the data types, sometimes the
migration will fail to migrate data but the structure will have been created.

The additional steps below is for the situation where the Table Structure has been migrated
successfully to MSSQL, but not the data. Assume that the original data is still in the CSV file. The
do the following.

1. Create a new Visual Studio Project: Add New Project - Database Projects - Microsoft SQL Server
    - SQL-CLR - C# SQL Server Project.
2. In the project created above, in the Solutions Explorer, go to its folder called Test Scripts,
   and create a new Test Script.
3. The test script eg. foo.sql can be any kind of script and can be run interactively.
4. To create a script to copy data from the CSV file to SQL DB, write the following script and execute:

BULK
INSERT

FROM 'file.cxv'
WITH
(
FIRSTROW = 2,     -- starts importing data in row 2
--LASTROW = 40,   -- imports until row 40
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

5. If there are any import errors, then consider modifying the data type of the table.
Example if a column is type int, then in the data file, the data may be !#NUM. One way to deal with
this is to change the column data type to nvarchar.


Visual Studio Database Project
================================
1. In Solution Explorer of VS.Net, do Add New Project.
2. Choose Microsoft Database Project - SQL Server 2005 Wizard
   Give this DB project name: DB_A
3. Select:
   Organize my project by Schema / Object type
   Default schema: dbo
   Include Schema name in the file name
   Enable SQL CLR
4. Select:
   select everything except "Numeric round abort" - this is the default
   Database Collation: SQL_Latin1_General_CP1_CS_AS
5. Import existing schema - this is where you choose your existing database.
   Import the DB called: DB_B
   Also select: Script the column collation .....
6. Select:
   Build output path: .\sql\
   IMPORTANT - Target Database name: DB_B  
      this should be the name of the actual DB that is being pointed to.
   Block incremental deployment ....
   Click Finish.

Views and Stored Procedures
=================
Stored Procedures belong to the database itself. VS.Net allow the creation of stored
procedures through the IDE via the Database project.

1. From the Solution Explorer, go to the DB project, navigate the tree:
   Schema Objects - Schemas - dbo - Stored Procedures.
2. Click Add New Item - Programmability - Procedure.
3. A stored procedure template is created and ready to be edited.

A view is a stored SELECT statement, and a stored procedure is one or more Transact-SQL
statements that execute as a batch. Views are queried like tables and do not accept parameters.

Stored procedures are more complex than views. Stored procedures can have both input
and output parameters and can contain statements to control the flow of the code,
such as IF and WHILE statements. It is good programming practice to use stored procedures for
all repetitive actions in the database.

---View-
CREATE VIEW vw_Names
   AS
   SELECT ProductName, Price FROM Products;
GO
-- Testing the View
SELECT * FROM vw_Names;
GO

--- Stored Proc
CREATE PROCEDURE pr_Names @VarPrice money
   AS
   BEGIN
      -- The print statement returns text to the user
      PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));
      -- A second statement starts here
      SELECT ProductName, Price FROM vw_Names
            WHERE Price < @varPrice;
   END
GO
--- Testing Stored Proc
EXECUTE pr_Names 10.00;
GO


To run Stored Procedures for Views from MS VS.Net, just highlight the SQL command and
right click Execute SQL!



Transact SQL
============
Ref: MSDN - Enterprise and Servers Development - SQL Server - SQL Server 2005 Documentation -
     SQL Server Books Online - SQL Server Database Engine - Designing and Creating Databases

SET NOCOUNT ON        instructs SQL Server not to count the rows in the result set

NOLOCK                is a SQL hint to not issue a shared lock, not honor exclusive locks, and
                      maybe permit a dirty read. In the delete procedure, the row count is left
                      on (by default) and the number of rows deleted is returned

IF blahCondition
   blah
   blah
ELSE
   blah
END;                 Semicolon need at the end

------ Example
USE master;
GO

--Delete the TestData database if it exists.
IF EXISTS(SELECT * from sys.databases WHERE name='TestData')
BEGIN
    DROP DATABASE TestData;
END

--Create a new database called TestData.
CREATE DATABASE TestData;

USE TestData
GO

CREATE TABLE dbo.Products
   (ProductID int PRIMARY KEY NOT NULL,
    ProductName varchar(25) NOT NULL,
    Price money NULL,
    ProductDescription text NULL)
GO



-- Standard syntax
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
    VALUES (1, 'Clamp', 12.48, 'Workbench clamp')
GO

-- Changing the order of the columns
INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)
    VALUES ('Screwdriver', 50, 3.17, 'Flat head')
GO

UPDATE dbo.Products
    SET ProductName = 'Flat Head Screwdriver'
    WHERE ProductID = 50
GO

-- Returns only two of the records in the table
SELECT ProductID, ProductName, Price, ProductDescription
    FROM dbo.Products
    WHERE ProductID < 60
GO

-- Returns ProductName and the Price including a 7% tax
-- Provides the name CustomerPays for the calculated column
SELECT ProductName, Price * 1.07 AS CustomerPays
    FROM dbo.Products
GO


Passing Arguments into Stored Procedures
- In the example below, schm and Tsource arguments have default values assigned.
-------
Create PROCEDURE [dbo].[testPERD_XPOS]  (
     @tblName sysname,
     @schm sysname = 'dbo',
     @Tsource char(50) = NULL



Transact SQL Data Types vs DB Datatypes
=========================================
datetime - DT_DBTIMESTAMP
money - DT_CY



Transact SQL functions
=========================
-- To get current database name
Select db_name()

-- To check compatibility level DB
exec sp_dbcmptlevel 'RatingsHistoryDB'

Result: 100 means version 10.0
Result: 90 means version 9.0


-- QUOTENAME(, '')
If quote character is omitted, then square brackets is used.
Example:
    SELECT QUOTENAME('abc[]def')
    result is: [abc[]]def]

-- RTRIM
Returns a character string after truncating all trailing blanks

-- EXEC
To execute a stored procedure from a Query window (not from within Stored Procedure).
   exec @arg1=blah, @arg2=bleh

-- To find rows of all tables
SELECT     [TableName] = so.name,     [RowCount] = MAX(si.rows)
FROM     sysobjects so,     sysindexes si
WHERE     so.xtype = 'U'     AND     si.id = OBJECT_ID(so.name)
GROUP BY     so.name
ORDER BY     2 DESC

-- To find size (in bytes) of a table
exec sp_spaceused


-- To reduce size of database
Using SQL Server Management Studio, right click on DB - Tasks - Shrink - Files | Database.




Checking if table, stored procedures, synonyms, etc EXISTS
==============================================================

To check that a table exists:  
   IF OBJECT_ID ('AdventureWorks.dbo.AWBuildVersion','U') IS NOT NULL
      Print 'Table Exists'
   ELSE
      Print 'Table Does Not Exists'

Note the type of object 'U' represents a Table. For more object type codes, see section on: "sys.object constants"



sys.object constants
======================
       principal_id  -- int -- ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership.
Is NULL if there is no alternate individual owner.
Is NULL if the object type is one of the following:
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
TA = Assembly (CLR-integration) trigger
TR = SQL trigger
UQ = UNIQUE constraint


     type -- char(2) -- Object type:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

    type_desc -- nvarchar(60) -- Description of the object type:
AGGREGATE_FUNCTION
CHECK_CONSTRAINT
DEFAULT_CONSTRAINT
FOREIGN_KEY_CONSTRAINT
SQL_SCALAR_FUNCTION
CLR_SCALAR_FUNCTION
CLR_TABLE_VALUED_FUNCTION
SQL_INLINE_TABLE_VALUED_FUNCTION
INTERNAL_TABLE
SQL_STORED_PROCEDURE
CLR_STORED_PROCEDURE
PLAN_GUIDE
PRIMARY_KEY_CONSTRAINT
RULE
REPLICATION_FILTER_PROCEDURE
SYSTEM_TABLE
SYNONYM
SERVICE_QUEUE
CLR_TRIGGER
SQL_TABLE_VALUED_FUNCTION
SQL_TRIGGER
TABLE_TYPE
USER_TABLE
UNIQUE_CONSTRAINT
VIEW
EXTENDED_STORED_PROCEDURE




Configuring Permissions
=========================

-- give permission to access instance of SQL DB
CREATE LOGIN [computer_name\Mary]
    FROM WINDOWS
    WITH DEFAULT_DATABASE = [TestData];
GO

-- give permission to access TestData DB
USE [TestData];
GO
CREATE USER [Mary] FOR LOGIN [computer_name\Mary];
GO

-- give permission to access Stored Proc
GRANT EXECUTE ON pr_Names TO Mary;
GO


Deleting tables, DB, etc
==========================
USE TestData;
GO

--Use the REVOKE statement to remove execute permission for Mary on the stored procedure:
REVOKE EXECUTE ON pr_Names FROM Mary;
GO

-- Use the DROP statement to remove permission for Mary to access the TestData database:
DROP USER Mary;
GO

--
Use the DROP statement to remove permission for Mary to access this instance of SQL Server 2005:
DROP LOGIN [\Mary];
GO

-- Use the DROP statement to remove the store procedure pr_Names:
DROP PROC pr_Names;
GO

-- Use the DROP statement to remove the view vw_Names:
DROP View vw_Names;
GO

-- Use the DELETE statement to remove all rows from the Products table:
DELETE FROM Products;
GO

-- Use the DROP statement to remove the Products table:
DROP Table Products;
GO

-- You cannot remove the TestData database while you are in the database; therefore, first switch context to another database, and then use the DROP statement to remove the TestData database:
USE MASTER;
GO
DROP DATABASE TestData;
GO



Timing
========


Bulk Insert Selected Column
============================
--CREATE VIEW testView
--AS
--SELECT exposure_class
--FROM ASB_IPRE_CommProperty
BULK INSERT testView
FROM 'H:\workCBA\myVS2005\StressTest\TestRiskRatedPFConsole\Data\testjunk.csv'
WITH
(
--FIRSTROW = 2,
----LASTROW = 10,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO


How to use Merge in SQLserver 2008
====================================
This is a description of how to use the SQL Server 2008 feature - "Merge ... Using".
The operation here involves 2 Tables.
Tsrc = Source table where the information is downloaded from.
Trgt = Local table, typically our working version of the table.

The real life situation / scenario is that from time to time, Tsrc will be changed. At different times, we need to update our local table

Trgt such that it reflects the source table (Tsrc) but also require to keep the records of old data.


The process involve a merge between the local and the source tables and the results are kept in the local table.
- The source table is unchanged.
- The local table has extra columns RUN_I and Clatest.
- Column RUN_I keeps track of the runs each time we perform a download and merge from the source database.
- Column Clatest keeps track of whether the record is the latest record or has been modified.


Define the two tables as follows:

CREATE TABLE [dbo].[Tsrc](
[C1] [int] NOT NULL,
[C2] [nchar](3) NULL,
[C3] [date] NOT NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[Trgt](
[RUN_I] [int] NOT NULL,  -- a run id
[C1] [int] NOT NULL,
[C2] [nchar](3) NULL,
[C3] [date] NOT NULL,
[Clatest] [int] NOT NULL      --  is 0 if record is old, is 1 if record is the new/latest.
) ON [PRIMARY]



The Merge process actually occurs in a temporary table-space facilitated by a VIEW. The view can be defined as follows:


CREATE VIEW [dbo].[VTemp]
AS
SELECT     RUN_I, C1, C2, C3, Clatest
FROM         dbo.Trgt
WHERE     (Clatest = 1)


There is also a temporary table called UpdateRecords which are created from the Merge process. This table contain the meta-data about

which records have changed from the Merge process.


The description below is based on the complete Insert Into .. Select .. Merge operations.
The results from the merge operation, which return multiple records, are inserted into the local table Trgt.

Overall Description:
1. Get desired data from source table. Lines 5-13
2. Merge results from step 1 with local table. Lines 4-26. In this process there are three conditions and one post processing.
3. Within the merge operation, the 3 conditions are: i) when there is a match between source and local table conditions; ii) when record

is in source but not in local iii) when record is in local but not in source. Within each of these conditions, there are operations that

perform a change to the local table.
4. At the end of the merge operation, as a post process, meta-data can be outputted as a temporary table (eg UpdateRecords table).
5. From the temporary table of the Merge operation, select the records which have been replaced. Lines 2-28.
6. From step 6, insert the results back into the local table Trgt. Line 1.


Detailed Description: From the innnermost loop ......
Lines 8-10:  Downloads records from Source table, with relevant conditions.
Lines 5-13:  The downloaded source data is aliased as src(C1,C2,C3), to be used as the source of the Merge operation
Line  14:    Specify conditions for merge.
Lines 15-17: Columns C1, C3 are info to identify records between source and local tables. When identified, then if the C2 info is

different between source and local tables, or either one is null, then update C2 info and Run_I identifier.
Lines 18-21: When records in source but not in local, then put data from source to local.
Lines 22-24: When records in local but not in source, then mark the record as obsolete by setting Clatest = 0.
Line  4:     This merging process does not merged with the local table, but rather it merges with the sub-table of the local table, where

the entries are the most current ones, ie Clatest = 1.
Line  26:    Outputs the merge process including special value $action which has values 'UPDATE', 'INSERT', etc. And also Deleted and

Inserted values.
Lines 04-26: The complete merge process. At the end of this, both view and its real data in the local table Trgt, would have been

changed. It also outputs meta data including inserted information and deleted information.
Eg. Deleted.RUN_I AS prevRunId, Deleted.C1, Deleted.C2 AS prevCQC, Deleted.C3, 0 AS prevClatest, Inserted.Clatest AS newClatest

Lines 27:    The results from the merge process, acting like a temporary table for the Select command.
Lines 02-28: Selection of META results from the merge process to extract information of updated records. Note in the actual merge process

of lines 04-26, for the case when a row is being updated, the Trgt table and view will hold the updated record only, the previous record

is not kept. Example
    Before merge: {RUN_I, C1, C2, C3, Clatest} = {23,  1, 2, 3,  1}   -> old
    After merge:  {RUN_I, C1, C2, C3, Clatest} = {24,  1, 5, 3,  1}   -> new
There are no extra rows added to keep track of the old record. However at the end of the merge process, there are Meta-fields available

which keep track of the old record as: Deleted.RUN_I, Deleted.C1, Deleted.C2, Deleted.C3, Deleted.Clatest. In this example:
    {Deleted.RUN_I, Deleted.C1, Deleted.C2, Deleted.C3, Deleted.Clatest} = {23,  1, 2, 3,  1}
In Line 26, these Meta-fields are selective extracted into the temporary table called UpdateRecords. Also a new field called prevClatest

is created with values 0 to indicate old results.
Finally, in line 2, the select statement chooses {prevRunId, C1, prevCQC, C3, prevClatest}, in the example this is:
        {23, 1, 2, 3, 0}
And ultimately in Line 1, the results from Line 2 which represent replaced records, are added back into the local table Trgt.

Lines 01-28: Add records to local table Trgt, those records selected by Lines 02-28 which are in fact old records which have been updated

and use prevClatest into the Clatest column.





01 INSERT INTO Trgt
02 SELECT prevRunId, C1, prevCQC, C3, prevClatest
03 FROM (
04 MERGE VTemp AS trgt
05     USING (SELECT crr.C1, crr.C2, crr.C3
06 FROM Tsrc AS crr
07 INNER JOIN (
08 SELECT DISTINCT C1
09 FROM Tsrc
10 WHERE C1>2 AND C2 in ('A','A0')
11 ) as c
12 ON c.C1 = crr.C1
13 ) AS src (C1, C2, C3)
14 ON trgt.C1 = src.C1 AND trgt.C3 = src.C3
15 WHEN MATCHED AND ((trgt.C2 != src.C2) OR (ISNULL(trgt.C2,'') != ISNULL(src.C2,'')))
16 -- record exists for given client and rating date, but rating value has changed so update record
17 THEN UPDATE SET C2 = src.C2, RUN_I = @RUN_I
18 WHEN NOT MATCHED BY TARGET
19 -- records in source that are not in target, insert new records in target
20 THEN INSERT (RUN_I, C1, C2, C3, Clatest)
21 VALUES (@RUN_I, src.C1, src.C2, src.C3, 1)
22 WHEN NOT MATCHED BY SOURCE
23 -- records in target that are no longer in source, expire target records
24 THEN UPDATE SET Clatest = 0
25 -- record exists for given client and rating date, but rating value has changed so insert the previous record with old rating
26 OUTPUT $action, Deleted.RUN_I AS prevRunId, Deleted.C1, Deleted.C2 AS prevCQC, Deleted.C3, 0 AS prevClatest,

Inserted.Clatest AS newClatest)
27 AS UpdatedRecords (Action, prevRunId, C1, prevCQC, C3, prevClatest, newClatest)
28 WHERE Action = 'UPDATE' AND newClatest = 1



Installing SQL Server 2008 with Visual Studio 2008
====================================================
Visual Studio 2008 Team Suite
Visual Studio 2008 Team Suite SP1
SQL Server 2008
Microsoft® Visual Studio Team System 2008 Database Edition GDR R2
(http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en#filelist)
Visual Studio 2008 Team Explorer



Showstoppers during installation:

Message: Setup could not verify the integrity of the file Update.inf. Make sure the Cryptographic service is running on this computer.
Solution: First solution works in: http://support.microsoft.com/kb/822798

Message: The following error has occurred: Error 1316.A network error occurred while attempting to read from the file
............\SSCERuntime-enu.msi
Solution: see http://support.microsoft.com/kb/970876


Message: The following error has occurred: Upgrade Failed due to the following Error.The error code is :-2147467259.Message:Unspecified error
Solution:
If Visual Studio is installed to a non-default location on X64 machine and X64 BIDS are installed, the SQL Server setup fails.
This failure does not apply when setup runs on X86 machines or on X64 machines when the BIDS are installed in WOW mode (X86 on X64).
Solution:
See https://connect.microsoft.com/SQLServer/feedback/details/363017/sql-server-2008-rtm-upgrade-failed-due-to-the-following-error-the-error-code-is-2147467259-message-unspecified-error




How to use SQL Server 2008 Projects in Visual Studio 2008
===========================================================
Prerequisites: see section "Installing SQL Server 2008 with Visual Studio 2008"

There can be various reasons why we need to create a SQL Server 2008 project as a Visual Studio 2008 project. The reason in this case is so that we can create an image of the DB to deploy to other systems. Hence the initial step would be to create a DB itself in the DB server. Then we create the following Visual Studio - Sql Server project. Having this Visual Studio - Sql Server project enables us to link to the actual DB and deploy any changes to the real DB or to a new DB.

This section explains the process to create the Visual Studio - Sql Server project.
1. In Visual Studio: File - New - Project.
2. In the Project dialog: Database Projects - SQL Server 2008 - SQL Server 2008 Wizard.
3. In the New Project Wizard,
i) Type of project: select "A database project to manage changes to a user-defined database.
ii) In the SQL Script File section, select "By type of object".

To Link the VS project to the actual Database:
1. Once the vS SQL Server project has been created, go to Solution Explorer and right click on the DB project.
2. Select "Import database objects and settings"
3. In the Import Database Wizard, choose the connection to the real DB and fill in any other options.
4. Press Start.

Now that the VS SQL Server project DB has been created, and has also linked to the actual real DB, we can build this VS DB project and re-create the DB in other DB servers. To do this, go to the VS SQL Server project and Build, then Deploy.



Link to other servers
======================
- List all databases in the server:
     exec sp_databases
- List the servers being connected to:
     select * from sys.servers
- List all tables in the database
     exec sp_tables
- To connect to a remote DB which is also MSSQL server
     exec sp_addlinkedserver @server='', @srvproduct='SQL Server'
Note that 'SQL Server' is a special fixed string - do not modify.

Ref:
http://blogs.techrepublic.com.com/datacenter/?p=133

If @srvproduct is not 'SQL Server', then @provider may be necessary. @provider may have the following values:
SQL Server - SQLNCLI
Oracle     - MSDAORA
Oracle, version 8 and later  - OraOLEDB.Oracle
Access/Jet and Excel         - Microsoft.Jet.OLEDB.4.0
ODBC data source - MSDASQL
IBM DB2 Database - DB2OLEDB

If the remote server cannot be accessed, then perhaps permissions need to be configured,
---- see master.dbo.sp_addlinkedsrvlogin

Linked Server - can be done from Management Studio, by:
- Server Objects - Linked Servers, right click to Add - New Linked Server


Using SQL Server to Import to data file
==========================================
This is for the transfer of large tables across databases.
1. Go to Start - Programs - Microsoft SQL Server 200x - Import and Export Data (32bit)
2. Follow instructions in the wizard to Select source.
3. In the "Choose a Destination" dialog, choose "Flat File Destination" in the "Destination" drop down list.
Then select the file name to save the data. Also check "Column names in the first data row".
4. In the "Specify Table Copy or Query" dialog, choose "Copy data from one or more tables or viewa".
5. In the "Configure Flat File Destination" dialog, choose the table to be copied. By clicking the "Edit Mappings" button, the columns of the table can be modified.
6. In the "Save and Run Package", select "Run Immediately" and "Save SSIS Package" and "File System".
7. In the "Save SSIS Package", select the destination to where the package is to be saved.
8. The table will be saved in two files: .dtsx is the SSIS package file, .txt or whatever name is given in Step 3 is the raw data file. 

WARNING - When importing to flat file , MSSQL - SSIS transforms the original data types and usually saves them into strings. When exporting these data back into the DB, they are still strings, but will be able to populate columns which have the original data type.

Using SQL Server to Export from data file
==========================================
This is for the transfer of large tables across databases.
1. Go to Start - Programs - Microsoft SQL Server 200x - Import and Export Data (32bit)
2. In the "Choose Data Source" dialog, choose "Flat File Source" in the "Source" drop down list.
Then select the file name. Also check "Column names in the first data row".
3. In the "Choose a Destination" dialog, selec the local DB server. Then also select the DB from the Database drop down list.
4. In the "Select Source Tables and Views", select the table needed.
5. Review the data mapping in the dialog.
6. In the "Save and Run Package", select "Run Immediately".

7. In the "Save SSIS Package", select the destination to where the package is to be saved.


Transfer or Copy Database using Backup and Restore with MS SQL Server Management Studio
=================================================================
This function allows the transfer of an entire Database (DB) at one go by using the backup and restoration facility of MSSMS.
1. Open up MS SQL Server Management Studio (MSSMS).
2. In the Object Explorer, navigate to the specific DB to be backed up. Right click on it, then select Tasks - Back Up...
3. In the Backup DB dialog in the General tab, ensure the following settings:
      Source - Database: name of database is correct
        - Backup type: Full
- Backup Component: Database
      Destination - Disk
               the backp file path will be displayed in the text box.
4. In the Backup DB dialog in the Options tab, there are various options that control the backup such as:
      Overwrite / Append backup file
 Reliability: verifying backup, continue on error
 Compression
5. Click OK to begin backup. The backup file is usually .bak
6. To copy over the backed up DB to a new server, open up MSSMS and connect to the new server.
7. In the Object Explorer, under the DB server, right click on the "Databases" and select "Restore Database".
8. In the Restore DB dialog, in the General tab, fill in the following:
      To Database: type in the name of the new DB
 Source To Restore: From Device, then click on the button to choose the backup DB file.
 In the list of DB to restore, put a tick in the DB to be restored.
9. In the Restore DB dialog, in the Options tab, fill in the following:
      Restore Options: choose from various options as needed.
 In the table "Restore the DB files as", under the "Restore As" column, manually edit the path to where the DB files should be created.
 Recovery State options: choose from various options as needed.
10. Click OK to begin the Transfer / Recovery of DB.



Difference Between nVarChar vs VarChar and nChar vs Char
===========================================================
The var or char with the letter n in front means Unicode character is allowed. N stands for 'National'.
Note the nchar, nvarchar takes at least twice as much storage than the non-n version.

Unicode
========
Unicode allows data to be stored in characters beyond ASCII characters. It allows letters from other languages.
1. Unicode data type are: nChar, nVarchar
2. Unicode string, eg.
      set @tmp = N'Select * from blah'


Using Variables / Parameters in Stored Procedures
===================================================
- Using database name as a variable
    DECLARE @Database VARCHAR(10)
    SET @Database = 'TWO'
    EXEC('USE  ' + @DATABASE)

- Using sp_executesql example:
use blah
go
declare @RECCNT int
declare @ORDID varchar(10)
declare @CMD Nvarchar(100)
set @ORDID = 10436
SET @CMD = 'SELECT @RECORDCNT=count(*) from [Orders]' + ' where OrderId < @ORDERID'
print @CMD
exec sp_executesql @CMD,
                   N'@RECORDCNT int out, @ORDERID int',
                   @RECCNT out,
                   @ORDID
print 'The number of records that have an OrderId' +  ' greater than ' + @ORDID + ' is ' + cast(@RECCNT as char(5))

In the example above, @ORDID -> @ORDERID as input, then @RECORDCNT -> @RECCNT as output.


Dynamic SQL
=============
Ref: "The Curse and Blessings of Dynamic SQL" http://www.sommarskog.se/dynamic_sql.html

- Safest way of using Dynamic SQL is through Stored Procedures in T-SQL, rather than Stored Procedures in C#, .Net, and rather than sending SQL statements to DB server.
- use sp_executesql rather than EXEC() in Stored Procedures
- Use dbo to prefix table names
- Use a @debug parameter in SP for easy debugging, eg.
       CREATE PROCEDURE blah @debug bit = 0 , @tblname sysname
  AS
     blah
 DECLARE @sql nvarchar(max)
 SET @sql = 'select * from dbo.' + QUOTENAME(@tblname)
 IF @debug = 1 PRINT @sql
- When table name is variable, eg @tableName, use QUOTENAME(@tableName).
  Note that QUOTENAME can only be used with sp_executesql, but not in EXEC(). The equivalent of using EXEC() is:
          EXEC('Select * from ' + @tblname)
- When passing variable tablename eg @tblname in the previous example, use the "sysname" data type.
- When using DB or Linked Servers, use SYNONYM
           CREATE SYNONYM otherDB FOR other.DB.table


Partitioned Tables
======================
??? See Books Online


Using SQL Server Management Studio (SSMS)
==========================================
The following applies to SSMS 2008

Tools in SMSS:
i) Activity Monitor - this allows you to monitor the performance of SQL Server. To Open the Activity Monitor:
   - In object explorer, right click on the server name of the DB, and choose Activity Monitor
   - In the Toolbar, click the icon that looks like a graph.


SQL Server Indexes
====================
Indexes are extra sets of information pointing to specified column data in a table. As its name suggests, it indexes a set of column data to provide something like pointers or addresses to the data. There are various types of indexes for different purposes that can be created.

Example: Select ProdID, ProdName, UnitPrice FROM Prod WHERE UnitPrice > 12.5

Non-Unique Index - In the example above, the non-unique index will sort the Unit Price, and produce an internal index that point the sorted UnitPrice to the original row position in the table.
       Eg. CREATE INDEX Idx_Price ON Prod (UnitPrice)

Unique Index - the column to be indexed need to be unique.
             - a Primary Key is automatically a unique index.  
       Eg. CREATE UNIQUE INDEX Idx_Price ON Prod (UnitPrice)

Clustered Index - instead of keeping an index to the indexed column, a clustered index RE-SORTS ALL columns based on the chosen                     column to be indexed
                - having a primary key automatically makes the table into a clustered index.
- if there is no primary key, then the table should be made into a clustered index based on a certain column.
- a table can have only one clustered index.
- Every table should have a clustered indexe for performance reason.
- a clustered index can be unique or non-unique.
       Eg. CREATE CLUSTERED INDEX Idx_Price ON Prod (UnitPrice)

Composite Index - where multiple columns are used as the index
                - can be clustered or non-clustered
- if a primary key is compose of two columns, then those two columns are also Composite Indexes.
       Eg. CREATE CLUSTERED INDEX Idx_Price_ProdName ON Prod (UnitPrice, ProdName)
      
When to use Indexes
- when Searching for records in queries with WHERE conditions, with SELECT, UPDATE or DELETE statements.
- when Sorting records, eg with ORDER BY keyword
- when Grouping records, eg with GROUP BY keyword
- when Covering queries use Composite Index. In the example below, apart from using Price as the index, the ProdName can also be used together as the index.
       Eg Select ProdName, Price FROM Prod ORDER BY Price
- when Matching complex search, useful for searches like below
       Eg SELECT * FROM Order WHERE OrderID = 1 AND ProdID = 2

When not to use Indexes
- when DB space is limited, because indexes take up extra spaces
- when queries modify data, eg with INSERT, UPDATE, DELETE statements; because the indexes need to be modified too which reduces performance.

Other Guidelines to use Indexes
- make indexes from columns with short data types, eg int, rather than long characters.
- columns where values are mostly distinct or unique.
- delete indexes which are not needed

-- to rename an index
     EXEC sp_rename 'Prod.IX_UnitPrice', 'IX_Price'

-- to delete an index
     DROP INDEX .

-- to see a list of all indexes created on a table
     EXEC sp_helpindex

-- to see the space used by Indexes
     EXEC sp_spaceused