DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Integrating PostgreSQL Databases with ANF: Join this workshop to learn how to create a PostgreSQL server using Instaclustr’s managed service

Mobile Database Essentials: Assess data needs, storage requirements, and more when leveraging databases for cloud and edge applications.

Monitoring and Observability for LLMs: Datadog and Google Cloud discuss how to achieve optimal AI model performance.

Automated Testing: The latest on architecture, TDD, and the benefits of AI and low-code tools.

Related

  • New ORM Framework for Kotlin
  • Angular Component Tree With Tables in the Leaves and a Flexible JPA Criteria Backend
  • Externalize Microservice Configuration With Spring Cloud Config
  • Develop XR With Oracle Cloud, Database on HoloLens, Ep 2: Property Graphs, Data Visualization, and Metaverse

Trending

  • Agile Estimation: Techniques and Tips for Success
  • Build a Digital Collectibles Portal Using Flow and Cadence (Part 1)
  • API Design
  • Freedom to Code on Low-Code Platforms
  1. DZone
  2. Data Engineering
  3. Databases
  4. Database Fundamentals #5: Database Properties

Database Fundamentals #5: Database Properties

Part of creating and maintaining the database is setting the correct database properties, such as collation, recovery model, compatibility level, and containment type.

Grant Fritchey user avatar by
Grant Fritchey
·
Jul. 19, 17 · Opinion
Like (2)
Save
Tweet
Share
3.64K Views

Join the DZone community and get the full member experience.

Join For Free

Don’t let the ease of creating databases lull you into a false sense of security. They actually can be very complicated. You can modify and individualize their behavior within your server so that different databases behave in radically different ways. The best way to see all the different manipulations you can make is by opening the New Database window by right-clicking on the Databases folder within the Object Explorer window, assuming you’re already connected to the server.

Don’t bother typing anything into the first page. Click on the Options tab on the left side of the window. You’ll see a screen that should look very similar to this:

Don’t worry. We won’t be talking about all the possible settings on this page. Instead, we’ll focus on a few that are extremely important and we’ll learn about a few that are going to be covered later in other blog posts.

Making changes to database options through the GUI is reasonably straightforward. You’re going to use the drop-down lists provided or type values into the text boxes provided. Making changes to database properties through TSQL is generally done one of two ways. Some of the properties can be set when creating the database. Most properties have to be modified after creating the database use ALTER DATABASE commands. I’ll show an example of each.

The most important settings — and the ones you’re most likely going to use to change the behavior of the database when you create it — are located right on top. That makes things easy because it means you won’t have to worry about scrolling through that long list of options.

Collation

One of the most important options on the database is the Collation. Most of the time though, this is set to a single value for all databases on all your servers. Collation controls how SQL Server will deal with character information. Changing the collation is something you do for international systems so that you see sorting and comparisons of character values in ways that are appropriate for the international language and character set that you’re dealing with. This is why most systems will have a single value for all databases. But if you have international languages to support, you may have to change your collation.

Collation is a little different than other options when creating a database using TSQL. It has a slightly different syntax:

CREATE DATABASE MyColDB
 ON  PRIMARY
( NAME = N'MyColDB', FILENAME = N'C:\DATA\MyCol.mdf', 
SIZE = 4MB , FILEGROWTH = 1MB )
LOG ON
( NAME = N'MyColDB_log', FILENAME = N'C:\DATA\MyColDB_log.ldf'
, SIZE = 1GB , FILEGROWTH = 10%)
 COLLATE Albanian_100_CI_AI_WS_SC

You can see that the COLLATE command is not a part of the WITH clause I mentioned earlier. The exact values for getting COLLATE working correctly have to be looked up individually because there are hundreds of them. Remember, Books Online is a reference you should be constantly using.

The key point here is that, for most people, most of the time, collation is something that they will set once when they install the server and then never change again. But it’s important to know that it’s a setting you can control if you need it.

Recovery Model

The recovery model option is something that you will manipulate constantly as you create databases. A full overview of what the different recovery models are and why you would choose each one will be covered in detail in a blog post later when we talk about database backups. Just to introduce the concept, if you set Recovery to Full, you will need to set up backups for your log. If you set recovery to simple then the logs will clean up on their own. There’s a lot more to the topic than just that, but that’s the simple part. To create a database using simple recover here’s a script:

CREATE DATABASE MySimpleDB
ON  PRIMARY
( NAME = N'MySimpleDB', FILENAME = N'C:\DATA\MySimpleDB.mdf',
SIZE = 3MB , FILEGROWTH = 1MB )
LOG ON
( NAME = N'MySimpleDB_log', FILENAME = N'C:\DATA\MySimpleDB_log.ldf', 
SIZE = 1MB , FILEGROWTH = 10%);
 
ALTER DATABASE MySimpleDB
SET RECOVERY SIMPLE;

This script will create the database and then modify the database through the ALTER command to make it into Simple Recovery.

Compatibility Level

Some databases and their code may not be designed for running on the latest version of SQL Server. SQL Server has the capability to support databases from previous versions by changing the capability level. Only alter this if you absolutely are required to. It will change the behavior of the database and could remove or change functionality.

Containment Type

A contained database is one where all the functionality that defines the database and its objects are present within that database alone. It means that there are no dependencies on the SQL Server instance that is hosting the database. You would modify this option for databases that you are developing yourself that are likely to be part of an application that you are going to distribute or sell. You may receive contained databases from third party vendors. Because of the way that database containment works, you won’t generally be switching between a non-contained and a contained database. Drilling down into the details of database containment are beyond the scope of this post.

Other Options

The remaining database options are split up into different categories in the list below the initial set of options that I’ve defined. Some of these options will be addressed as we come to the pertinent blog posts in this series and others will not be. In general, until you need one of these to be something other than the default values, you should leave the default values in place.

Conclusion

Part of creating and maintaining the database is setting these properties. You’ve seen how to do that with SSMS and with T-SQL. Remember that even though you may be most comfortable with the GUI in SSMS, the best way to expand and learn to manage SQL Server properly is to use T-SQL because it leads to automation of your processes.

Database Property (programming)

Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • New ORM Framework for Kotlin
  • Angular Component Tree With Tables in the Leaves and a Flexible JPA Criteria Backend
  • Externalize Microservice Configuration With Spring Cloud Config
  • Develop XR With Oracle Cloud, Database on HoloLens, Ep 2: Property Graphs, Data Visualization, and Metaverse

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: