I.T. Discussion Community!
-Collapse +Expand
DBA
Search DBA Group:

Advanced
-Collapse +Expand DBA Store
PRESTWOODSTORE

Prestwood eMagazine

October Edition
Subscribe now! It's Free!
Enter your email:

   ► KBDBA Knowledge Base  Print This    All Groups  

DBA Flashcards Library

These FlashCards are contributed by you (our online community members). They are organized by our knowledge base topics. Specifically, by the DBA sub-topics.

Contribute a Flashcard

19 DBA, Databases, & Data FlashCards

Group: DBA, Databases, & Data


Topic: ANSI SQL Scripting

Drop Index
Drop an index with the DROP INDEX statement.

The general syntax is (works on DB2, Oracle, etc):

DROP INDEX index_name

Microsoft Jet Engine / Microsoft Access syntax:

DROP INDEX index_name ON table_name

MS SQL syntax:

DROP INDEX table_name.index_name

MySQL syntax:

ALTER TABLE table_name DROP INDEX index_name
Posted By Mike Prestwood, Post #100629, KB Topic: ANSI SQL Scripting
Not In SQL Select Query

The following selects all the records in Table1 where IDField is not in Table2.

SELECT Table1.*
FROM Table1
LEFT JOIN Table2 ON Table1.IDField = Table2.IDField
WHERE Table2.IDField is null
Posted By Mike Prestwood, Post #101797, KB Topic: ANSI SQL Scripting
Q&A: Reverse sort order in a select
Question: Is there a way to reverse order a select statement?

Answer:

Yes. Use the DESC keyword in your order by clause on each column you want ordered descending. Use ASC for ascending.

select * from Customer 
Order by Age Desc, City Asc, State Asc
Posted By Mike Prestwood, Post #100011, KB Topic: ANSI SQL Scripting



Topic: DBA & Data

Definition

ANSI

Definition: ANSI - An acronym for American National Standards Institute. The ANSI set consists of 8-bit codes that represent 256 standard characters, letters, numbers, and symbols. The ANSI set is used by Windows applications.
Posted By Mike Prestwood, Post #100179, KB Topic: DBA & Data
Definition

ASCII

Definition: ASCII is an acronym for American Standard Code for Information Interchange. The ASCII set consists of 7-bit codes that represent 128 standard characters, including letters, numbers, and symbols. The first 128 characters in the ASCII set, the extended ASCII set, and the ANSI set are the same.
Posted By Mike Prestwood, Post #100182, KB Topic: DBA & Data
Definition

BDE

Definition:

BDE is an acronym for Borland Database Engine (previously referred to as IDAPI and before that it was ODAPI). Back in 1993, Phillipe Kahn battled Microsoft in many ways including the orginal ODAPI versus ODBC. ODAPI grew up into IDAPI and finally is now named BDE.

Posted By Mike Prestwood, Post #100183, KB Topic: DBA & Data
Tip: Learn the Three Normal Forms in One Sentence
The three normal forms can be summed up in the following phrase: All the fields of a table should relate to the key, the whole key, and nothing but the key.
Posted By Mike Prestwood, Post #100597, KB Topic: DBA & Data
Definition

Master Table

Definition: A master table in a multitable relationship is the primary table. For every record in the master table, there can be many records in the detail table. If you are only dealing with one table, then it is the master table. A detail table in a multitable relationship is the table whose records are subordinate to those of the master table. A detail table is also called a slave table, a child table, or a many table.
Posted By Mike Prestwood, Post #100621, KB Topic: DBA & Data
Tip: Naming Fields: Choose a Context Name
When naming fields/columns in a table, use ONE name for the data in the field. For example, although states are called provinces and other names in other coutries, use one or the other in the database but not both. Use CompanyState, avoid CompanyStateProvince. Use labels in your website or program to switch context.
Posted By Mike Prestwood, Post #101296, KB Topic: DBA & Data
Definition

Optimistic Locking

Definition: A locking mechanism that allows other users to edit a record that is currently being edited. Essentially, last in wins or edits are discarded usually with an error.
Posted By Mike Prestwood, Post #100802, KB Topic: DBA & Data
Definition

Pessimistic Locking

Definition: A locking mechanism that prevents other users from entering edit mode on a record that is currently locked.
Posted By Mike Prestwood, Post #100803, KB Topic: DBA & Data
Definition

Standard Delimited Format (SDF)

Definition: SDF is an acronym for standard delimited format. An SDF is a text file formatted in a particular style. Each field is enclosed in quotation marks and separated by a comma. Each line ends with a carriage return and a linefeed.
Posted By Mike Prestwood, Post #100184, KB Topic: DBA & Data
Definition

Tab-delimited format (TDF)

Definition: In a Tab-delimited format (TDF) file, each field is delimited by a tab character.  Each record is delimited with a carriage return and line feed. TDF has the advantages of being immune to commas and quotation marks within the data itself.
Posted By Wes Peterson, Post #100546, KB Topic: DBA & Data



Topic: Microsoft SQL Server

Q&A: Char vs NChar
Question: What is the differences between Char, NChar, VarChar, and NVarChar?

Answer:

A Char field is a text field of a specific length. For example, a Char(50) field takes up 50 characters of storage in most databases even if you only store 1 character in it., or even none. 

A VarChar field is a text field of variable length. For example, a VarChar(50) field can be up to 50 characters but if less is stored, the length of the field is somewhat less than 50. If you only store 1 character in a VarChar, then generally only 1 character of space is taken up in storage.

The "N" in NChar and NVarChar stands for National character which means you can store unicode text. NChar and NVarChar take up twice as much storage space.

Posted By Mike Prestwood, Post #101165, KB Topic: Microsoft SQL Server
Easy SQL Server Backup Script

Learn how to make an easy SQL Server Script that will automatically back up all your databases in a simple way.

-- Back Up All Databases
-- by Bryan Valencia

--create temp table
declare @temp table(commands varchar(500), completed bit)

--load it with backup commands
insert into @temp (commands, completed)
(select
    'BACKUP DATABASE ['+name+
    '] TO  DISK = N''J:\Backups\'+name+
    '.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'''+name+
    '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10',
    0
from
    master.sys.databases
where
    owner_sid <> 0x01 and state_desc='ONLINE'
)

--variable for the current command
declare @thisCommand varchar(500);

--loop through the table
while (select count(1) from @temp where completed=0)>0
begin
    --find the first row that has not already been executed
    select top 1 @thisCommand = commands from @temp where completed=0

    --show the command in the "mesage" output window.
    print @thisCommand

    --execute the command
    EXEC (@thisCommand);

    --flag this row as completed.
    update @temp set completed=1 where commands=@thisCommand
end

--show the user the rows that have been found.
select * from @temp

Posted By Bryan Valencia, Post #102439, KB Topic: Microsoft SQL Server
Q&A: Edit SQL Views?
Question: Are views in Microsoft SQL Server editable?

Answer:

Yes and no. Yes, there is nothing in MS SQL Server preventing a client from writing to the underlying tables involved in an view. Therefore, it is left up to the tool accessing the view. Many tools allow you to edit views in SQL Server including ASP Classic, ASP.Net, VB, Access, etc. Some tools, like SQL Server Management Studio allow you to edit tables, but not views.
Posted By Mike Prestwood, Post #100799, KB Topic: Microsoft SQL Server
Move Table to Another Schema

In MS-SQL, to move an object such as a table or view from one schema to another, use alter schema.

alter schema [ToSchema] transfer FromSchema.[Object] 

 

For example, the following moves the Orders table from the user1 schema t the dbo schema.

alter schema [dbo] transfer user1.[Orders] 
Posted By Mike Prestwood, Post #102065, KB Topic: Microsoft SQL Server



Topic: MS SQL 2005

Q&A: MS-SQL Binary Large Objects
Question: How do you store BLOBs (Binary Large Objects) in Microsoft SQL Server?

Answer:

BLOBs (Binary Large Objects) are collections of data that can be stored as a single entity in a database. In Microsoft SQL Server, BLOBs are stored using the text, ntext, and image data types.

  • The text data type stores a variable length of non-unicode data up to 231 - 1 characters.
  • The ntext data type stores a variable length of unicode data up to 230 - 1 characters. The storage size, in bytes, is twice the amount of characters.
  • The image data type is used to store image files smaller than 231 - 1 bytes.
Posted By Stephen Berry, Post #101297, KB Topic: MS SQL 2005
MSSQL Update Trigger Example

This tutorial shows how you would create a trigger in Microsoft SQL Server 2005/2008 that will date/timestamp a column named last_updated everytime any data in the row is updated.

This example assumes a primary key that includes 3 fields.

CREATE TRIGGER MyTableUpdate
ON dbo.MyTable
FOR update
AS
UPDATE
MyTable
SET last_updated = GetDate()
From MyTable Inner Join Inserted On
MyTable.KeyField1 = Inserted.KeyField1
and MyTable.KeyField2 = Inserted.KeyField2
and MyTable.KeyField3 = Inserted.KeyField3
Posted By Bryan Valencia, Post #100987, KB Topic: MS SQL 2005

Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech