Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Interview Prep - SQL > Tables and Schemas > Flashcards

Tables and Schemas Flashcards

1
Q

How many databases does a SQL Server table belong to?

A

Just 1

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is meant by base tables?

A

Based tables hold the core data and are permanent in the sense that if SQL Server is shut down and restarted the based tables contents and definitions would remain.

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What are two ways you can create a table?

A
  1. CREATE TABLE statement
  2. SELECT INTO statement

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is a schema?

A

A container that holds tables, views, procedures and other database objects.

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What are two benefits of schemas?

A
  1. They allow you to group together objects in a way that makes sense for you and your organization
  2. You can set permissions at the schema level

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How many schemas can a particular table belong to?

A
  • Only one. You may have copies of the same table in multiple schemas but you couldn’t update table1 in schema ABC and expect the changes to flow to table1 schema xyz.
  • Schema ABC is part of table1’s name and makes it unique to that schema.

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

If you don’t supply a database schema name, what is the default?

A

The database user name’s default schema

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Can 2 objects within the same schema have the same name?
For example,
View=Test1
Table=Test1

A

No. Objects within a schema must have unique names, even if they are different types of objects.

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How many owners does a schema have?

A
  • Just one authorized database user.
  • The schema owner can grant permissions to other users regarding objects in that schema

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Should you use ROWVERSION OR TIMESTAMP?

A

ROWVERSION

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How can you specify whether a column allows nulls?

A
  • By putting NULL or NOT NULL after the columns data type. Null meaning Null’s are allowed.
  • Example: categoryname NVARCHAR(15) NOT NULL

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What two things can you specify when using the identity property?

A
  • Seed=the value to begin with
  • Increment=the amount to increase each new sequence number by
  • Example: categoryid INT IDENTITY(1,1) NOT NULL

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What does it mean to make a computed column persisted?

A

SQL Server will store the computed values with the table’s data and not compute them on the fly.

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What type of functions can’t be used as part of the computed column expression if the column is persisted?

A
  • Functions that are not deterministic
  • Example:
    GETDATE()
    CURRENT_TIMESTAMP

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What does table compression do?

A

It compresses data in a table, in addition to the indexes, to get more efficient storage.

SQL Server 70-461 08-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What are the two types of table compression?

A
  1. Row
  2. Page

SQL Server 70-461 08-01

17
Q

How does row-level compression work?

A

SQL Server applies a more compact storage format to each row of a table.

SQL Server 70-461 08-01

18
Q

How does page-level compression work?

A

It includes row-level compression plus additional compression algorithms that can be performed at the page level.

SQL Server 70-461 08-01

19
Q

What are 5 variations on tables?

A
  1. Temporary Tables
  2. Table Variables
  3. Views
  4. Indexed Views
  5. Derived Tables and Table Expressions

SQL Server 70-461 08-01

20
Q

Can you have a schema within another schema?

A

No

SQL Server 70-461 08-01

21
Q

What are the 4 built in schemas that can’t be dropped?

A
  1. dbo
  2. guest
  3. INFORMATION_SCHEMA
  4. sys

SQL Server 70-461 08-01

22
Q

What is the general form of the statement to create a schema and assign an owner?

A

CREATE SCHEMA schema_name AUTHORIZATION user_name

SQL Server 70-461 08-01

23
Q

What is the general form of a statement to move a table from one schema to another?

A 
ALTER SCHEMA schema_name TRANSFER schema_name.table_name
  • ALTER SCHEMA schema_name is the schema you are moving to
  • schema_name.table_name is the object to move
  • This will work assuming the object with the same does not already exist in the schema you’re transferring to.

SQL Server 70-461 08-01

24
Q

How can you indicate a specific value to be used in place of a null? (In create table statement)

A

Add the DEFAULT clause right after NOT NULL

Example:
description NVARCHAR(200) NOT NULL DEFAULT(‘ ‘)

SQL Server 70-461 08-01

25
Q

How does the identity property work?

A

It is allowed in only one column of a table. It automatically generates a sequence of numbers.

SQL Server 70-461 08-01

26
Q

Standard form when assigning identity property

A

IDENTITY(seed, increment)

SQL Server 70-461 08-01

27
Q

What column is it common to use the identity property on?

A

The column that is the primary key

SQL Server 70-461 08-01

28
Q

What is helpful to know about computed columns when using the CREATE TABLE statement?

A

You can make a computed column at the time you create the table.

Example: initialcost AS unitprice*qty

SQL Server 70-461 08-01

29
Q

What is the general format of a statement to do table compression?

A

After the last paren surrounding the column definitions place
- WITH(DATA_COMPRESSION=ROW)
- WITH(DATA_COMPRESSION=PAGE)

SQL Server 70-461 08-01

30
Q

What is the general form of the statemet to alter a table to set its compression?

A
  • ALTER TABLE table_name
    REBUILD WITH(DATA_COMPRESSION-PAGE)
  • ALTER TABLE table_name
    REBUILD WITH(DATA_COMPRESSION-ROW)

SQL Server 70-461 08-01

31
Q

What 4 things can you do with the ALTER TABLE command?

A
  1. Add or remove a column, including a computed column
  2. Change the data type of a column
  3. Change a columns nullability
  4. Add or remove a constraint

SQL Server 70-461 08-01

32
Q

What 5 kinds of constraints can you add and remove with ALTER TABLE?

A
  1. Primary key constraint
  2. Unique constraint
  3. Foreign Key constraint
  4. Check constraint
  5. Defualt constraint

SQL Server 70-461 08-01

33
Q

What 2 things can you not do with ALTER TABLE?

A
  1. Change a column name
  2. Add or remove an identity property

SQL Server 70-461 08-01

34
Q

How is a database schema different than a table schema?

A
  • Database schema is a container to hold objects
  • Table schema is the definition of the table to include CREATE TABLE statement and all column definitions

SQL Server 70-461 08-01

35
Q

Can a FK be created on a computed column?

A

Yes

SQL Server 70-461 08-02

36
Q

Why might you want to partition a table?

A

If it is very large you may want to partition it into multiple tables in one database or one instance of SQL Server. Or, you may want to have them across multiple databases accross multiple servers.

SQL Server 70-461 09-01

Interview Prep - SQL (26 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions