The answer is no. But if the question is "Can you insert data into the underlying table through view?" The answer is then yes. SQL Server will allow you to insert data into the underlying table through a view with a condition: * The insert... Read More

If you want to follow other tutorial examples included in this collection, you need to run this SQL script to create a simple table called ggl_users: USE GlobalGuideLineDatabase; GO DROP TABLE ggl_users; GO CREATE TABLE ggl_users ( id... Read More

Unicode characters are multi-byte characters. They are very hard to be entered as string literals, because it requires: * The SQL client tool to support Unicode characters. The command line tool 'sqlcmd' does not support Unicode. But ... Read More

If you have an existing table, but you don't remember what are the columns defined in the table, you can use the "sp_columns" stored procedure to get a list of all columns of the specified table. The following tutorial script shows you a good... Read More

A DML trigger is a trigger declared to handle a DML event, which occurs when an INSERT, UPDATE or DELETE statement is executed. If you want to create a DML trigger, you should use the "CREATE TRIGGER" statement in the following format: CREATE... Read More

Objects from unsigned CLR assemblies with EXTERNAL_ACCESS or UNSAFE permissions In order to execute objects from CLR assemblies with EXTERNAL_ACCESS or UNSAFE permissions, the assemby must either be signed with a key pair or the database hosting... Read More

The answer is no. But if the question is "Can you update data in the underlying table through view?" The answer is then yes. SQL Server will allow you to update data in the underlying table through a view. The tutorial exercise below is a good... Read More

The timestamp datatype does not store time values. The answer is the timestamp datatype does not store time values. The better name for this datatype is rowversion, and it is actually a database-wide unique varbinary value. When you have a... Read More

You are looking for the applicant to make some reference regarding data manipulations. The more indexes on a table, the more time it takes for the database engine to update, insert, or delete data, as the indexes all have to be maintained as the data... Read More

Unicode character string data types are used to store Unicode character strings. SQL Server 2005 supports the following Unicode character string data types: * NCHAR - Unicode character strings with a fixed length of n characters defined as NCHAR... Read More

The value NULL is a very tricky subject in the database world, so don't be surprised if several applicants trip up on this question. The value NULL means UNKNOWN; it does not mean '' (empty string). Assuming ANSI_NULLS are on in... Read More

If the WHERE clause in an UPDATE statement matches multiple rows, the SET clause will be applied to all matched rows. This rule allows you to update values on multiple rows in a single UPDATE statement. Here is a good example: SELECT * FROM... Read More

SQL Server Browser Service is installed as part of the SQL Server. But it is turned off by default to reduce the security risk. If you want start SQL Server Browser Service to allow the SQL Server to accept network connections, you need to follow... Read More

They are not really secure, that are only obfusticated. There are tools that you can use to get at the code. The code itself is just obfusticated. However, you cannot edit the code, the stored procedure or view would have to be dropped and re... Read More

ODBC, Open Database Communication, a standard API ( application program interface) developed by Microsoft for Windows applications to communicate with database management servers. If you want to access a database server through an ODBC... Read More

Assuming that the ODBC driver for SQL Server has been installed as part of the Windows system, the next step of setting up ODBC connection to SQL Server is to create a new DSN (Data Source Name) with the ODBC Data Source Administrator: * Go... Read More

Sometime, you may want to implement some business logics in a DML trigger to cancel the DML statement. For example, you may want to check the new email address format provided by the UPDATE statement. If the email address is invalid, you to... Read More

External columns represent the meta data of external data sources and output columns are used be data flow source adapters. Output columns are used by all data-flow source adapters and transformations. They are not used by destination adapters. Put... Read More

Can you delete data in a view? The answer is no. But if the question is "Can you delete data from the underlying table through view?" The answer is then yes. SQL Server will allow you to delete data from the underlying table through a view.... Read More

Any experienced database developer should be able to answer this question with ease. Some of the less-experienced developers will be able to answer it, but with a little less clarity. Weekly SQL tips in your inbox TechRepublic's free SQL ... Read More

Log and data files for your databases as well as the Quorum drive and the backup folders. Anti- virus programs can exist on the same server as SQL Server without an issue if you exclude certain items. The database data and log files, backup... Read More

If you don't want to keep a database user any more, you should delete the user by using the "DROP USER" statement. This tutorial exercise shows how to delete "Dba_User": -- Login with "sa" USE GlobalGuideLineDatabase; GO DROP USER... Read More

Continuing from the previous tutorial, on the first screen of the "Create a New Data Source to SQL Server" wizard, you should enter 3 fields: Name, Description, and Server as suggested below: Name: ggl_SQL_SERVER Description: GlobalGuideLine... Read More

CHAR and VARCHAR are both used to store code page based character strings. But they have the following main differences: * CHAR(n) stores character strings with a fixed length, n bytes, storage format. * VARCHAR(n) stores character strings with a... Read More

DECIMAL and FLOAT are both used to store numerical values. But they have the following main differences: * DECIMAL(p,s) stores values with the decimal point fixed at the position of s (scale) digits from the right. The total number of decimal... Read More

Min and Max memory for each instance If you are designing multiple instances, you will want to specify the amount of RAM that each has access to. If you do not do this, performance can vary dramatically as the servers compete for memory. Read More

The SQL Variant type can store all datatypes except varchar(max), varbinary(max), xml, text, ntext, rowversion/timestamp (thought the data of a rowversion can be stored in a binary(8), which can be stored in a sql_variant) and sql_variant (it may... Read More

The XML data type changes the representation of your XML document into an internal, optimized version that has all the content, but may not maintain the original text. The varchar(max) or nvarchar(max) represenations should be used in this case. Read More

If you want to see the table you have just created, you can use the "sys.tables" system view to get a list of all tables in the current database. The tutorial script gives you a good example: SELECT name, type_desc, create_date FROM sys.tables GO... Read More

If you have an existing table, but you don't remember what are the columns defined in the table, you can use the "sys.columns" system view to get a list of all columns of all tables in the current database. In order to a list of columns of a... Read More

The quickest way to verify a user name in a database is probably to use the SQLCMD tool. You can connect to the server, select the database, and check which user name is linked the current login name as shown below. Start a command ... Read More

The latency between transaction commits at the publisher and subscriber exceeds the warning level. This status can actually mean two different things. Either the latency between the commit of a transaction at the publisher and the same commit at the... Read More

This is the number of seconds that a serialplan cannot exceed if it is to be used. A parallel plan is used if the estimate exceeds this value. This is the threshold at which SQL Server determines whether a serial or parallel plan is to be used.... Read More

Use the WSDL=DEFAULT parameter in the create endpoint statement. When using the CREATE ENDPOINT command, you can specify the WSDL=DEFAULT parameter to generate a default WSDL response or use WSDL="spname" where spname is the name of a custom stored... Read More

It is still sent to the distributor and executed on the subscriber. A transaction that fails in the publication database is still sent to the distributor and subscriber. YUou can trap the error, but you would need to trap it on the subscriber as... Read More

A constant, or literal, is a symbol that represents a specific value of a specific data type. Constants or literals are used commonly as the default values for table columns, variables, and parameters. The format of constant or literal depends on the... Read More

A cursor is a special data type that represents a result set returned by a SELECT query statement. There are several notes about cursor you need to remember: * Cursor data type can not be used to define table columns. * Cursor data type is used... Read More

If you want to practice DML statements, like INSERT, UPDATE and DELETE statements, you should create a testing table. The tutorial exercise shows you a good example: CREATE TABLE ggl_links (id INTEGER PRIMARY KEY, url VARCHAR(80) NOT NULL, notes... Read More

When you define a view, its columns are defined through a list of expressions in the underlying SELECT statement. Their data types will be determined implicitly by the expressions. For example, if the column expression is a column name of a... Read More

SQL Mail through Extended MAPI or Database mail. SQL Server Agent Mail can be configured to use Database Mail or Extended MAPI. Read More

Both CHAR and NCHAR are fixed length data types. But they have the following main differences: * CHAR stores characters based on the code page with 1 byte per character most of the time. * NCHAR stores characters using Unicode character set with... Read More

A DDL trigger is defined to handle a DDL statement event, like create, alter and drop tables, views, indexes, etc. DDL triggers can be used to generate warning messages on database object changes. The format of creating a DDL trigger should be: ... Read More

In SQL Server, different collations may use different code pages. For example: * Albanian_CI_AI_KS_WS - Albanian, Code page 1250. * Arabic_CI_AS_KS_WS - Arabic, Code page 1256. * French_CI_AI - French, Code page 1252. * Korean_Wansung_BIN -... Read More

Returns the smallest integer greater than or equal to the value passed in. CEILING() returns the smallest integer that is great than or equal to the value passed in. Read More

It allows the grantee the equivalent of ownership in that they have all permissions and can grant them to others. The Control permission is equivalent to assigning ownership of the securables. All available permissions are granted to the principal,... Read More

If you want to stop a WHILE loop early, you can use the BREAK statement in the loop statement block. The tutorial exercise below shows you how to use a BREAK statement to stop a WHILE loop early: -- Counting number of days in 2000 DECLARE ... Read More

A server login connects an account to the server. A database user the link from the server login to a defined database . You need to create a server login using CREATE LOGIN before you can then link it to a database using a database user with CREATE... Read More

The CLR environment needs to be enabled. By default on SQL Server 2005, the CLR runtime environment, which is what executes the .NET assemblies, is not enabled. You need to execute the following code to enable it: Read More

Handles Volume Shadow Service Copy functions. The SQL Writer service was introduced to handle access to the data files in SQL Server. It allows backup programs, like VSS, to function while SQL Server is still running. Read More

Another way to get a list of columns from a table is to use the "sp_help" stored procedure. "sp_help" returns more than just a list of columns. It returns: the table information, the column information, the identity column, the row GUID column, the... Read More

To test a DML trigger defined on a table, you just need to execute several INSERT, UPDATE and DELETE statements on that table as shown in this tutorial example: USE GlobalGuideLineDatabase; GO INSERT INTO ggl_users (name) VALUES ('GGL... Read More

This agent reads the subscriber logs and moves changes back to the publisher. This agent is used when the queued update model is chosen with transactional replication. It moves changes from the subscribers back to the publishers. Read More

Add, string concatenation, unary plus The three functions are Add, String Concatenation, and Unary Plus. Read More

If you want to make changes to an existing trigger, you could use the "ALTER TRIGGER" statements to refine the trigger again. The tutorial exercise below shows you how to modify the trigger defined in a previous tutorial: USE... Read More

Log Reader agent reads the publisher log for transactions to send to the distributor. This agent is tasked with reading the transaction log in transactional replication and moving those transactions that need to be replicated to the distributor. Read More

If you don't want to use a trigger any more, you should delete it from the database by using the "DROP TRIGGER" statement as shown in tutorial example: USE GlobalGuideLineDatabase; GO DROP TRIGGER new_user; GO SELECT * FROM sys... Read More

The trigger, dml_message, provided in previous tutorials was defined to handle all 3 types of DML statements, INSERT, UPDATE, and DELETE. If you do not want the trigger to handle all 3 types of DML statements, you can list only 1 or 2 of the... Read More

Booleans are formatted as True/False and there are no other built in options, but you could build an expression using IIF that would let you do other formatting From the format dialog there are no extra formatting options for Booleans, Report... Read More

This statement is used to setup new tasks for Service Broker queues. This statement is ue to create a new Service Broker service that is a set of tasks that are run on messages. Read More

Query the system catalog view: sys.conversation_endpoints The system catalog view sys.conversation_endpoints will show you which Service Broker endpoints, and therefore ports, are open on your server. Read More

A collation is a set of rules defining a character set and its sorting rules. SQL Server support a large number of built-in collations. For example: * Albanian_CI_AI_KS_WS - Albanian, case-insensitive (CI), accent-insensitive (AI), kanatype... Read More

If you do not want to use the default collation provided by the SQL Server, you can use the "COLLATE collation_name" clause to specify a different collation to be used at different levels: * Database Level - Used in CREATE DATABASE or ALTER... Read More

It supports BLOB object data. The Raw File Destination connection does not use a connection manager, supports NULL data, and only has one input. It also does not support BLOB data or have an error output. Read More

If you want to know how many views you have created in a database, you use the system view called sys.views to get a list of views defined in the current database. The tutorial exercise shows you how many views in database GlobalGuideLineDatabase: ... Read More

If you don't need a specific view any more, you can use the DROP VIEW statement to delete it from the database. The following tutorial exercise shows you how to delete the view, ggl_links_view: USE GlobalGuideLineDatabase; GO SELECT *... Read More

BEGIN ... END" statement structure is used to group multiple statements into a single statement block, which can be used in other statement structures as a single statement. For example, a statement block can be used in an "IF ... ELSE ..." statement... Read More

If you want get the definition of an existing view back from the SQL Server, you can use the system view called sys.sql_modules, which stores definitions of views and procedures. The sys.sql_modules holds view definitions identifiable by the... Read More

If you want to practice you DBA skills with the scaled-down version of the sample database AdventureWorksLT provided by Microsoft, you should follow this tutorial to download and install it first: 1. Go to the SQL Server 2005 Samples and Sample... Read More

Unlimited, no different from the other SQL Server 2000 editions. The answer is actually an unlimited number of connections, though this in a practical sense is limited by memory on the machine (The actual limitation is 32k connecitons). Instead... Read More

If your table has a primary key column, and you are trying to insert a new row with duplicate key value on the primary key column, you will get an error. The reason is simple - Primary key column does not allow duplicate values. The following... Read More

A primary key is the field(s) in a table that uniquely defines the row in the table; the values in the primary key are always unique. A foreign key is a constraint that establishes a relationship between two tables. This relationship typically... Read More

NET Framework Version 2.0 is required by many Microsoft applications like SQL Server 2005. If you want download and install .NET Framework Version 2.0, you should follow this tutorial: 1. Go to the Microsoft .NET Framework Version 2.0... Read More

If you want to change the name of an existing database user, you can use the "ALTER USER" statement as shown in the tutorial exercise below: -- Login with "sa" USE GlobalGuideLineDatabase; GO ALTER USER ggl_User WITH NAME = Dba_User; GO ... Read More

If you want to list all triggers defined in the current database, you can use the catalog view, sys.triggers, as shown in the following tutorial example: USE GlobalGuideLineDatabase; GO CREATE TRIGGER new_user ON ggl_users AFTER INSERT AS ... Read More

The DCM tracks changes. The differential backup reads the extents from this structure. A differential backup uses the Differential Change Map to determine which extents have changed and need to be include in the backup. This greatly speeds the... Read More

Continue from the previous tutorial. After clicking Next on the first screen of the "Create a New Data Source to SQL Server" wizard, you should see the second screen asking you to select SQL Server login type, login name and password. Select the... Read More

Blocking Transformation The Sort component is a blocking transformation meaning that it needs all rows in order to perform its function. Read More

If you want to know how an existing table was created, you can use SQL Server Management Studio to automatically generate a "CREATE TABLE" script The following tutorial shows you how to do this: 1. Run SQL Server Management Studio and ... Read More

Continue from the previous tutorial. After clicking Next on the SQL login information screen, ODBC manager will try to connect to the SQL Server with the login information you provided. After a period of waiting time, you may get error... Read More

Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "SELECT ... INTO" statement. The tutorial script below gives you a good example: INSERT INTO... Read More

By default, SQL Server 2005 Express Edition is installed only one connection protocol enabled: * Shared Memory - SQL Server connection protocol for applications that are running on the same machine where the SQL Server is running. For... Read More

In the directory stored in the system variable TEMP. Profiler uses the location specified for the TEMP system variable. Read More

 GUID that identifies the database on which Service Broker is running. Each database has a Service Broker identifier. This is a GUID in the service_broker_GUID column that identifies the databases on which Service Broker is running. It ensure... Read More

The row being fetched is missing. This means that the row that was being fetched from the cursor is missing. Read More

Make a new copy of the production database each month and then copy that to the SAN. Attach it as a new Scalable Shared Database each month to the reporting servers. A Scalable Shared Database does not support database snapshots, so you would have... Read More

If you want get the definition of an existing trigger back from the SQL Server, you can use the catalog view called sys.sql_modules, which stores definitions of views, stored procedures, and triggers. The sys.sql_modules holds trigger... Read More

Service Broker does not consider the collation, using byte-by-byte matching for names. Neither collation is used. Service Broker operates in a collation independent method that removes collation information from the messages. Read More

If you have an existing table with existing data rows, and want to add a new column to that table, you can use the "ALTER TABLE ... ADD" statement. The tutorial script below shows you a good example: ALTER TABLE tip ADD author VARCHAR(40) GO ... Read More

If want to stop the execution of an existing trigger temporarily, you can use the "DISABLE TRIGGER" statement to disable it. The disabled trigger will be kept in the database. If you want to resume the execution of a disabled trigger, you can use... Read More

TEXT_IN_ROW_DATA The three types of allocation units are: IN_ROW_DATA, LOB_DATA, and ROW_OVERFLOW_DATA. Each heap or index has IN_ROW_DATA which holds part of the data. LOB_DATA is used for large object data types and ROW_OVERFLOW_DATA is used for... Read More

A reporting database server A scalable shared database is a feature that allows you to setup read-only database on a separate server for reporting purposes. This database provides an identical view of your data from another server. Read More

If what are the DML events an existing trigger is handling, you can use the catalog view, sys.trigger_events. You need to join sys.trigger_events and sys.triggers to get a better list as shown in this tutorial example: USE GlobalGuideLineDatabase ... Read More

Add a field to the model in Report Builder using the built in formula/function support While Report Builder cannot be used to build or maintain models, it does allow you to add a virtual field that exists only within that report - to the end user it... Read More

Users can add one or more images, but they will appear in the header or footer of the report, they cannot be added to the detail row. Images that are stored as row data in the database can be rendered at the detail level. Multiple images can be... Read More

Run Profiler Profiler will work as long as you have permissions to profile the server and is the the best solution because it requires no change to the Report Server itself. There is a way to log all report SQL to a log file, but that option was... Read More

Table, Matrix, Chart Report Builder can build a report formatted as a table, chart, or matrix (cross tab), but only ONE can be used in any given report. Read More

Date and time data types are used to store an instances of calendar dates and times. SQL Server 2005 supports the following date and time data types: * DATETIME - Date and time values stored in 8 bytes with 4 bytes for the date and 4 bytes for... Read More

When a DML event occurs, SQL Server will prepare a temporary table called "INSERTED", which contains the new record of the affected row, which is: * A copy of the inserted row for an INSERT statement. * A copy of the updated row for an UPDATE... Read More

True. Subreports can be used for a master-detail relationship, or the subreport can be a separate item, but in either case RS will query to get the data for the report once for each detail row. If end users are going to only occasionally look at the... Read More

Once the file is grown, zeros are written to the new space, causing a delay. When a database file grows, unless instant file initialization is turned on, the server must allocate disk space and then write 0s into all that space. This zero-ing out... Read More

The current filter and the number of rows that matched that filter By default, the current filter definition and the number of rows that matched the filter are added to the end of the report. They can be removed and added back as needed. Read More

Character string data types are used to store code page based character strings. SQL Server 2005 supports the following character string data types: * CHAR - Code page based character strings with a fixed length of n bytes defined as CHAR(n).... Read More

When a DML event occurs, SQL Server will prepare a temporary table called "DELETED", which contains the old record of the affected row, which is: * A copy of the deleted row for a DELETE statement. * A copy of the row to be updated for an... Read More

Triple DES When you create a Database Master Key, it is encrypted using the password you supply with the Triple DES algorithm. Read More

Remove a reference to a cursor. This statement is used to remove cursor references. If this is the last reference being removed, the cursor's resources are released. Read More

Have users save the RDL files locally and check into source control using a source control utility, or write code to script them out each night and check them into source control Users can do faux source control by just saving copies of their RDL... Read More

Binary string data types are used to store binary strings. SQL Server 2005 supports the following binary string data types: * BINARY - Binary strings with a fixed length of n bytes defined as BINARY(n). The maximum length is 8,000 bytes. *... Read More

If there is only one row affected by a DML statement, we know that the DML trigger will be executed once. But how many times the DML trigger will be executed if the DML statement resulted multiple affected rows? The answer is still one. In the... Read More

ReportBuilder Report Builder is bundled as part of Reporting Services 2005 and is targeted at end users needing to build ad hoc reports. BI Design Studio could be used, but typically requires more time and experience to use than the average... Read More

It means the sets in this file are accent sensitive. The diacritics_sensitive determines if the terms in the expansion and replacement sets are accent sensitive. A value of 0 means accent insensitive and a value of 1 means accent sensitive. Read More

Build and deploy a model Report Builder requires a model and that model cannot be defined using Report Builder. The advantage of using models is that users do not need to understand SQL statements to build a report (but they will still benefit from... Read More

One click deployment launched from a menu on the Report Manager home page One click deployment is fast and easy, users click the menu on the Report Manager page. It is not a web application, but rather a .Net Winform application. Read More

Expansion sets are synonyms for the search term and returned as results if they appear along with the search term. The expansion set is the group of values that are synonyms and can be substituted for the search term. For example, an expansion... Read More

Can you roll back the DDL statement in a trigger? The answer is yes. Since the DDL statement that fires the trigger and the statements defined inside the trigger are all executed as a single statement batch, you can add a ROLLBACK statement in the... Read More

In the Windows Application Log Event messages are logged in the Windows Application log. Read More

Can you create a logon trigger in SQL Server 2005 Express Edition? The answer is no. LOGON is not a supported event type in Express Edition. The script below shows you the error message when you try to create a logon trigger: CREATE TRIGGER... Read More

A report model provides familiar business names for database tables and fields. A Report model provides business names and terms for database fields and tables. It allows for predefined relationships between tables as well as grouping items together... Read More

Character string literals are used everywhere in Transact- SQL statements. You need to learn some important rules on writing character string literals: * Character string literals must be enclosed in single quotes as 'Welcome to... Read More

Yes, but only for simple subscriptions. Notification Services can be called from unmanaged code through a COM interface for simple subscriptions only. Condition based subscriptions are not supported through COM Interop. Read More

Edit the nsservice.exe.config file in the C:Program FilesMicrosoft SQL Server90NotificationServices .n.nnnin folder. Event logging and most Notification Services configuration require editing an XML file. In this case, the NSservice.exe.config file... Read More

If you want to change the definition of an existing index, you can use the "DROP INDEX" statement to drop the index first. Then use the "CREATE INDEX" statement to create it again with the new definition. But you can also combine those two... Read More

A view is a database object that represents the data in one or more tables in the same structure as a separate table. Here are some basic rules about views: * Tables store real data. * Views do not store real data. * Views must have underlying... Read More

These are defined for each contract. Each application that sets up queues and contracts inside Service Broker must define the message types that are valid for the contract. Read More

The default collation of a database comes from the server if you are not using the COLLATE clause in the CREATE DATABASE statement. If you are not using the COLLATE clause for character string column, it will use the default collation from the... Read More

If you want to a view on an existing table, you can use the CREATE VIEW statement in a simple syntax: CREATE VIEW view_name AS SELECT ... The tutorial exercise below shows you how to create a view to represent sub set of data stored in... Read More

When the length of the input string does not match the storage size of the fixed length string data type CHAR(n). SQL Server will: * If the input string of CHAR(n) has less than n bytes, it will be padded with space characters to become n bytes.... Read More

A dialog conversation is a conversation between services. A dialog conversation is a conversation between services. A conversation includes messages being passed back and forth as part of a contract. Read More

f only date value is provided in a data and time literal, the SQL Server will pad the time value with a zero, or '00:00:00.000', representing the midnight time of the day. The tutorial exercise below gives you some good examples: -- &... Read More

If you want to know how an existing view was created, you can use SQL Server Management Studio to automatically generate a "CREATE VIEW" script The following tutorial shows you how to do this: 1. Run SQL Server Management Studio and connect... Read More

If only time value is provided in a data and time literal, the SQL Server will pad the date value with a zero, representing the base date, January 1, 1900. The tutorial exercise below gives you some good examples: -- 'hh:mi:ss.mmm'... Read More

Fast Parse The fast parse mode that is set on columns for a flat file source connection can import a limited set of data types extremely quickly. Read More

If you want to skip the remaining statements in a loop block, you can use the CONTINUE statement. The tutorial exercise below shows you how to use a CONTINUE statement to skip the remaining statements and continue the next iteration: --... Read More

Can You Create a View with Data from Multiple Tables? The answer is yes. A view can be created with a SELECT statement to join data from multiple tables. It is a common practice to normalize data into multiple tables. Then using a view to de... Read More

Analysis Services Of these, only Analysis services is instance-aware. Read More

An acronym that describes concurrency support in a database The answer is an acronym that describes concurrency support in a database. ACID is an acronym for the following four terms: * Atomicity - Every operation within a transaction is treated as... Read More

In order to help you to learn SQL Server, Microsoft provides several free sample scripts and sample databases. * SqlServerSamples. msi - 25,469 KB: Sample scripts. * AdventureWorksDB.msi - 28,053 KB: Sample OLTP database: AdventureWorks, case... Read More

png Images stored originally in formats other than jpg are rendered in png format. Read More

This utility will report the differences in data between two tables. This utility will report the data differences between two tables. They must be alike in structure and give back the T-SQL to bring the data in the tables to be the same for most... Read More

Can You Create a View with Data from Another View? The answer is yes. A view can be used as a table to build other views. The tutorial exercise below shows you how to create a view using data from another view: USE AdventureWorksLT; GO ... Read More

You can monitor the Assembly Load event in a trace. While you could scan trace results for the names of functions and procedures using the assembly, it is possible that you may not know all the places the assembly is being called from. There is a... Read More

When you enter data and time literals, you may get out-of-range errors due to two common mistakes: * The date value is a valid calendar date, but it is not in the range covered by DATETIME data type: from January 1, 1753, to December 31, 9999. *... Read More

After installed the sample database AdventureWorksLT, you need to attach it to your SQL server to make it available by follow this tutorial: EXEC sp_attach_db @dbname=N'AdventureWorksLT', @filename1=N'C:Program FilesMicrosoft ... Read More

Assuming that you have a table which is used by a view, and you try to delete that table. SQL Server will let you delete the table without any trouble. But that view will become invalid. The tutorial exercise below shows you what happens to the... Read More

If you are entering an INT data type literal with representing an integer value too big for INT data type to store, the SQL Server will give you an arithmetic overflow error. The same error will happen on BIGINT, INT, SMALLINT, and TINYINT data... Read More

There are 12 user tables defined in AdventureWorksLT: USE AdventureWorksLT GO SELECT s.name, t.name, t.type_desc FROM sys.tables t, sys.schemas s WHERE t.schema_id = s.schema_id ORDER BY s.name GO s.name t.name t.type_desc dbo... Read More

Sometimes you want the data in a view to be sorted and try to use the ORDER BY clause in the SELECT statement to define the view. But SQL Server will not allow you to use ORDER BY to define a view without the TOP clause. The tutorial exercise... Read More

An operation where the row in the heap or clustered index is found from the bookmark in the index. A bookmark ID is stored in an index and points back to the actual row in the heap or clustered index of the table. The lookup operation occurs when an... Read More

Exact numeric data types defined with NUMERIC(p,s) has two limits defined by two parameters: p (precision) and s (scale): * Maximum number of digits of the integer part (digits before the decimal point) is defined as p-s. If this limit is passed, ... Read More

If you have an existing view, and want to change the underlying SELECT statement, you can use the "ALTER VIEW ..." statement to redefine the view. The tutorial exercise below shows you how modify an existing view: USE GlobalGuideLineDatabase; GO ... Read More

The service is installed once for each instances on the server. An instance aware service is one that is installed for each instance on the server. An instance-unaware service is only installed once , no matter how many instances. Integration... Read More

If you have an existing column in a table and you want to change the column name, you can use the "sp_rename ... 'COLUMN'" stored procedure. "sp_rename" allows you to change names of COLUMN, DATABASE, INDEX, USERDATATYPE, and OBJECT. The... Read More

AES (256 bit) The longer the key, the better the encryption, so choose longer keys for more encryption. However there is a larger performance penalty for longer keys. DES is a relatively old and weaker algorithm than AES. Read More

If you are using SQL Server Management Studio, you can rename almost any data objects through the Object Explorer window. The tutorial example below shows you how to rename a column: 1. Run SQL Server Management Studio and connect to SQL... Read More

By definition, FLOAT(n) should store the mantissa of the floating number in n bits. For example, FLOAT(16) should have a precision one-byte less than FLOAT(24). However, SQL Server 2005 only supports two precisions for floating numbers: * Single... Read More

SQL Server Integration Services You must install Integration Services in order to properly create and execute maintance plans since these plans are a subset of SSIS packages. Read More

If you have an existing table and you want to change the table name, you can use the "sp_rename ... 'OBJECT'" stored procedure. "sp_rename" allows you to change names of COLUMN, DATABASE, INDEX, USERDATATYPE, and OBJECT (including tables).... Read More

All communication points with SQL Server using network protocols use an endpoint. SQL Server creates defaults for each protocol that you enable. Read More

If you want to delete an existing table and its data rows, you can use the "DROP TABLE" statement as shown in the tutorial script below: SELECT * FROM tipBackup GO id subject description create_date 1 Learn SQL Visit www.globalguideline.com... Read More

If you enter a floating number that is too big or too small for the FLOAT data type, SQL Server2005 will behave as: * FLOAT(24) Underflow: If a floating number is too small for FLOAT(24), it will be stored as 0 without any warning. * FLOAT... Read More

DML (Data Manipulation Language) statements are statements to change data values in database tables. The are 3 primary DML statements: * INSERT - Inserting new rows into database tables. For example "INSERT INTO ggl_links VALUES (101, 'www... Read More

The computer account of the Windows installation. The network service account connects to network resources as the computer account for the Windows installation. Read More

By default, column names in a view are provided by the underlying SELECT statement. But sometimes, the underlying SELECT statement can not provide names for output columns that specified as expressions with functions and operations. In this case,... Read More

To insert a new row into a table, you can use the INSERT INTO statement with values specified for all columns as in the following syntax: INSERT INTO table_name VALUES (list_of_values_of_all columns) Note that the list of values of all... Read More

Encrypt data with a symmetric key and secure all keys with asymmetric keys. For performance reasons, you should encrypt data using a symmetric key and secure that key with an asymmetric key. Read More

If a column is defined with a default value in a table, you can use the key word DEFAULT in the INSERT statement to take the default value for that column. The following tutorial exercise gives a good example: INSERT INTO ggl_links VALUES (102, ... Read More

A numeric expression is a combination of identifiers, values, and operators that SQL Server 2005 can evaluate to obtain a numeric value. A simple expression could be a constant, a function, a column name, a variable, or a subquery without any... Read More

If you don't want to specify values for columns that have default values, or you want to specify values to columns in an order different than how they are defined, you can provide a column list in the INSERT statement. If a column is omitted in... Read More

If you want to insert multiple rows with a single INSERT statement, you can use a subquery instead of the VALUES clause. Rows returned from the subquery will be inserted the target table. The following tutorial exercise gives you a good example: ... Read More

By default, views are not bound to the schema of the underlying tables. This means that SQL Server will allow you to change underlying table's schema any time. For example, you can drop the underlying table while keep the view. Of course,... Read More

If you want to update some values in one row or multiple rows in a table, you can use the UPDATE statement. The tutorial script below shows a good example: SELECT * FROM ggl_links WHERE id = 101 GO id url notes counts created 101 www... Read More

There are 4 ways to convert a numeric expression from one data type to another data type: * Implicit conversion by arithmetic operations - When arithmetic operations are performed on expressions of different data types, implicit data type... Read More

It's very beneficial for a potential database developer to know the types of triggers available, and how to implement them. A trigger is a specialized type of stored procedure that is bound to a table or view in SQL Server 2000. In SQL... Read More

To find out if we can add data into AdventureWorksLT or not, you can try to add an address record into the "SalesLT.Address" in AdventureWorksLT: USE AdventureWorksLT GO INSERT SalesLT.Address (AddressLine1, City, StateProvince, CountryRegion... Read More

If you need to search and sort data in a view with a large number of row, you may want to create an index on the view to speed up your search process. The tutorial exercise below shows you how to create a unique clustered index on a view. DROP... Read More

If a row matches the WHERE clause in a UPDATE statement, existing values in this row can be used in expressions to provide new values in the SET clause. Existing values are represented by column names in the expressions. The tutorial exercise below... Read More

An assignment operation is used to assign an expression to a variable, a column, or a parameter. If the data type of the expression does not match the data type of the receiving variable, column, or parameter, SQL Server will perform an implicit data... Read More

An index is a secondary database object associated with a table to improve the retrieval performance of rows from that table. An index can be defined for a single column or multiple columns of a given table. If an index is defined on a single... Read More

This question can be answered a couple of different ways, but only one answer is a "good" one. The answer you want to hear is a Check constraint, which is defined on a database table that limits the values entered into that column. These constraints... Read More