Data Types used in SQL with Range: Introduction to SQL

+Pinal Dave  : As a database developer, you need to regularly retrieve data for various purposes, such as creating reports and manipulating data. You can retrieve data from the database server by using SQL queries.

In this article we will let you know how to retrieve selected data from database tables by executing the SQL queries. It will also explain to use functions to customize the result set as well as summarize and grouping data.

Retrieving Data

At times, the database developers might need to retrieve complete or selected data from a table. Depending on the requirements, you might need to extract only selected columns or rows from a table. Consider the example of an organization that stores the employee data in SQL Server database. At times, the users might need to extract only selected information such as name, data of birth and address details of all the employees. At other times, the users might need to retrieve all the details of the employees in the Sales and Marketing department.

Identifying Data types

Data type specifies the type of data that an object can contain, such as character data or integer data. You can associate a data type with each column, local variable, expression, or parameter defined in the database.

You need to specify the data type according to the data to be stored. For example, you can specify character as the data type to store the employee name, date time as the data type to store the hire date of employees. SQL Server 2005 supports the following data types.

Int:  ranges from (-) 2^31 to 2^31-1 and used to store Integer data (whole numbers)

Decimal: ranges from (-)10^38 +1 through 10^38-1 and used to store Numeric data types with a fixed precision and scale

Numeric: ranges from (-)10^38+1 through 10^38-1 and used to store  Numeric data types with a fixed precision and scale

Float: ranges from (-)1.79E+308to-2.23E-308,0 and 2.23E-308 to 1.79E+308 and used to store Floating precision data

Money: ranges from (-)922,203,685,477.5808 to 922,337,203,685,477.5807 and used to store monetary data

Datetime: ranges from January 1,1753, through December 31, 9999 and used to store Date and time data

Char(n): n characters, where n can be 1 to 8000 and used to store Fixed length character data

Varchar(n): n characters, where n can be 1 to 8000 and used to store Variable length character data

Text: Maximum length of 2^31-1(2,147,483,647) characters and used to store character string

Bit: 0 or 1 and used to store  Integer data with 0 or 1

Image: maximum length of 2^31-1(2,147,483,647) bytes and used to store  Variable length binary data to store images

Sql_variant: Maximum length of 8016 bytes Different data types except text, ntext, image, timestamp, and
sql_variant

Timestamp: maximum storage size of 8 bytes Unique number in a database that is updated every time a row that contains timestamp is inserted or updated

Uniqueidentifier: Is a 16-byte GUID A column or local variable of the uniqueidentifier data type can be initialized by either using the NEWID function or converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, where each x is a hexadecimal digit in the range 0-9 or a-f. For example, an unique identifier value is 6F9619FF-8B86-D011-B42D-00C04FC964FF

Table: Result set to be processed later

Xml: xml instances and xml type variables Store and return xml values

In the next article, we will discuss about how to fire a query to retrieve data from the database in sql server management studio.

 

SQL Server Tools with SQL Server Management Studio: Introduction to SQL

SQL Server provides various tools that help improve the efficiency of database developers. The SQL Server Management Studio is one such tool that helps in creating and maintaining database objects. The SQL Server Business Intelligence Development Studio is another tool that helps in creating and implementing BI solutions. The server also provides tools, such as the Database Engine Tuning Advisor and the SQL Server Configuration Manger that help the database administrator in configuring the server and optimizing the performance.

SQL Server Management Studio

The SQL Server Management Studio is a powerful tool associated with SQL Server, it provides a simple and integrated environment for developing and managing the SQL Server database objects. The following figure shows the SQL Server Management Studio starting interface.

Here’s the list contains main components of the SQL Server Management Studio.

Object Explorer: An Object Explorer provides the ability to register, browse, and manage servers. Using Object Explorer, you can also create, browse, and manage server components. The explorer allows you to configure:

• Security: Used to create log on Ids and users and to assign permissions.
• Notification Services: Used to generate and send notifications to users.
• Replication: Used to create and manage publishers and subscribers.
• SQL Server Agent: Used to automate administrative tasks by creating and managing jobs, alerts, and operators.
• Management Services: Used to configure Distributed Transaction Coordinator, Full-Text search, Database Mail service, or SQL Server logs.
• Server Objects: Used to create and manage backups, endpoints, and triggers.

Registered Servers: A registered server is a list that displays all the servers registered with the management studio. It also helps record connection information for each registered server including the authentication type, default database, network protocol characteristics, encryption, and time-out parameters.

Solution Explorer: The Solution Explorer provides on organized view of your projects and files. In this explorer, you can right-click on a project of file to manage or set their properties.

Query Editor: The Query Editor provides the ability to execute queries written in T-SQL. It can be invoked by selecting the New Query option from the File menu or the New Query button from the Standard toolbar.

Template Explorer: The Template Explorer provides a set of templates of SQL queries to perform standard database operations. You can use these queries to reduce the time spent in creating queries.

Dynamic Help: The Dynamic Help is available from the Help menu of the SQL Server Management Studio. This tool automatically displays links to relevant information while users work in the management studio environment.

SQL Server Business Intelligence Development Studio

The Business Intelligence Development Studio is a tool that provides an environment to develop business intelligence solutions. These solutions are based on the data that was generated in the organization and helps in business forecasting and making strategic decisions and future plans.

It helps building the following types of solutions:

• Data Integration: The integration services allow you to build solutions that integrate data from various data sources and store them in a common data warehouse.
• Data Analysis: The analysis services help analyse the data stored in the data warehouse.
• Data Reporting: The reporting services allow you to build reports in different formats that are based on the data warehouse.

Database Engine Tuning Advisor

The Database Engine Tuning Advisor helps database administrators to analyse and tune the performance of the server. To analyse the performance of the server, the administrator can execute a set of T-SQL statements against a database. After analysing the performance of these statements, the tool provides recommendations to add, remove, or modify database objects, such as indexes or indexed views to improve performance. These recommended help in executing the given T-SQL statements in the minimum possible time.

SQL Server Configuration Manager

The SQL Server Configuration Manager helps the database administrators to manage the services associated with the SQL Server. By default, in SQL Server, some services such as full-text search, SQL Server Agent, and integration services are not enabled. Administrators can start, pause, resume, or stop these services by using this tool.

In addition, the tool allows you to manage the network connectivity configuration from the SQL Server client computers. It allows you to specify the protocols through which the client computers can connect to the server.

Read Also: Feature of SQL Server and Data Types used in SQL

Windows 8 app development : Example of media element

In app development, windows 8 sdk mediaElement control support  audio and video format files. A short of code make media element or you can say media player for your apps.

<MediaElement Source="5th.mp4" HorizontalAlignment="Left" Height="438" VerticalAlignment="Top" Width="680"/>

In the preceding code define the attributes of MediaElement, the source attribute means which file you want to play in it. Height and width define the structure of mediaElement.

Example of MediaElement control in app development

<Page

x:Class="App1.MainPage"

xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

xmlns:local="using:App1"

xmlns:d="http://schemas.microsoft.com/expression/blend/2008"

xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

mc:Ignorable="d">

<Grid Background="{StaticResource ApplicationPageBackgroundThemeBrush}">

<MediaElement Source="5th.mp4" HorizontalAlignment="Left" Height="438" VerticalAlignment="Top" Width="680"/>

</Grid>

</Page>

Output of the program is

Using IsFullWindow property you can see your favorite videos in full window mode .It contains Boolean value for rendering videos in full or specified height and width.

Moving Records from a DataGridView to Another in C#: WinForms

Drag-n-Drop operation is an important action in the winforms application like our file explorer. This article will let you perform moving items from one datagridview to another, code is in c#. Follow the article and you will easily be able to do this task.

There are series of events that need to be handled to complete this task, discussed in moving items from one CheckedListBox to another. To perform this in datagridview we will use the pre-defined class Student with its properties like name, age and address.

• Add two DataGridView control to the form (defaultDGV & newDGV) and place them in simple form as shown.
  
  

• Create two list of type Student class named stuList and newList.
  List<Student> stuList = new List<Student>();
  List<Student> newList = new List<Student>();
• In the constructor add some records in the first list and bind that to first datagridview.
  public Form1()
  {
  InitializeComponent();
  stuList.Add(new Student() { Name = "Jacob", Age = 29, City = "London" });
  stuList.Add(new Student() { Name = "Zulia", Age = 31, City = "London" });
  stuList.Add(new Student() { Name = "Brandon", Age = 35, City = "London" });
  defaultDGV.DataSource = stuList;
  }
• Generate MouseDown event of the defaultDGV datagridview and write following code:
  if (defaultDGV.SelectedRows.Count == 1)
  {
  if (e.Button == MouseButtons.Left)
  {
  DataGridView.HitTestInfo info = defaultDGV.HitTest(e.X, e.Y);
  if (info.RowIndex >= 0)
  {
  string name = defaultDGV.Rows[info.RowIndex].Cells["Name"].Value.ToString();
  defaultDGV.DoDragDrop(name, DragDropEffects.Move);
  }
  }
  }

In above code, we have to write code only when any of the row is selected and left mouse button is pressed. HitTestInfo class is used to get information of the specific row at given coordinates. After that get your desired cell value if the row index is greater than zero. DoDragDrop() method will set the value to be dragged.

• Generate DragEnter and DragDrop events of another datagridview i.e. newDGV and make them same as written with following c# code.
  private void newDGV_DragEnter(object sender, DragEventArgs e)
  {
  e.Effect = DragDropEffects.Move;
  }
  
  private void newDGV_DragDrop(object sender, DragEventArgs e)
  {
  if (e.Data.GetDataPresent(typeof(string)))
  {
  string str = (string)e.Data.GetData(typeof(string));
  var record = stuList.FirstOrDefault(a => a.Name.Equals(str));
  stuList.Remove(record);
  newList.Add(record);
  defaultDGV.DataSource = newDGV.DataSource = null;
  defaultDGV.DataSource = stuList;
  newDGV.DataSource = newList;
  }
  }

You better known about the events used above. At the last, in DragDrop event remove the selected record from stuList and add that in newList. And then bind both lists to respective datagridview.

Run the project, defaultDGV have three rows added above, perform your drag-n-drop operation, it will successfully move the record.

Sql Server Features with About SQL: Introduction to SQL

The components of SQL Server help to improve the database management and developer productivity. These benefits are provided by the following features:

• Built-in support for XML data: Allow you to store and manage XML data in variables or columns of the XML data type. The XML data is stored in a structured format that can be used across different platforms and applications.
• CLR integration: Allows you to implement programming logic in any language supported.
• Scalability: Allow portioning of database table to help in parallel processing of queries. This makes the database scalable and improves the performance of queries.
• Service-oriented architecture: Provides a distributed, asynchronous application Framework for large-scale applications. This allows the database clients to send requests to the database server even if the server is not available to process the request immediately.
• Support for Web services: Allows you to provide direct access to the data from the Web services by implementing the HTTP endpoints.
• High level of security: implements high security by enforcing policies for log on passwords. Administrators can also manage permissions granted to different users to access the database objects.
• High availability: ensures that the database server is available to all users at all times. This reduces the downtime of the server. High availability in SQL Server is implemented with the help of database mirroring, failover clustering, and database snapshots.
• Support for data migration and analysis: provides tools to migrate data from disparate data sources to a common database. In addition, it allows building the data warehouse on this that can support BI applications for data analysis and decision-making

SQL

Database developer need to manage the database to store, access, and modify data. SQL is the core language used to perform these operations on the data. SQL, pronounced as “sequel”, is a language that is used to manage data in an RDBMS. This language was developed by IBM in the 1970s and follows the international Organization for Standardization (ISO) and American National Standards Institute (ANSI) standards.

Most database systems have created customized versions of the SQL language. For example, transact-SQL (T-SQL) is a scripting language used on the SQL Server for programming. Alternatively, PL-SQL is used for programming in Oracle. T-SQL confirms to the ANSI SQL-92 standard published by ANSI and ISO in the year 1992.

Here're the categorization of SQL statements:

• Data Definition Language (DDL): is used by to define the database, data types, structures, and constraints on the data. Some of the DDL commands are create, alter and drop etc.
• Data manipulation language (DML): is used to manipulate the data in the database objects. Some of the DML commands are insert, update and delete.
• Data Control Language (DCL): is used to control the data access in the database Some of the DCL commands are grant and revoke.
• Data Query Language (DQL): is used to query data from the database objects. Select is the DQL command that is used to select data from the database in different ways and formats.

SQL is not a case-sensitive statement. Therefore, you can write the commands is any case, lowercase or uppercase. For example, you can use the SELECT statement in lowercase as 'select' or in title case as 'Select'.

SQL Server Integration with .NET Framework

Common Language Runtime and Features, .NET Framework: Introduction to Sql Server

Earlier article was about .Net Framework and its components. In this article we will discuss about the remaining component i.e. Common Language Runtime, the important among all.

The CLR, Common Language Runtime, is one of the most essential components of the .NET Framework. It provides an environment for the application to run. The CLR or the runtime provides functionalities, such as exception handling, security, debugging, and versioning support to the applications.

Here're some of the features provided by the CLR:

• Automatic memory management: allocates and de-allocates memory to the application as and when required.
• Standard type system: provides a set of common data types in the form of Common Type System (CTS). This means that the size of integer and long variables is the same across all programming languages.
• Language Inter-operability: provides the ability of an application to interact with another application written in a different programming language. This also helps maximize code reuse.
• Platform independence: allows execution of a code from any platform that supports the .NET CLR.
• Security management: applies restrictions on the code to access the resources of a computer.

The CLR can host a variety of languages and offers a common set of tools across these languages, ensuring inter-operability between the codes. The code developed with a language compiler that targets the CLR is called a managed code.

Alternatively, the code that is developed without considering the conventions and requirements of the common language runtime is called unmanaged code. Unmanaged code executes in the common language runtime environment with minimal services. For example, unmanaged code may run with limited debugging and without the garbage collection process.

The components of SQL Server and the .NET Framework provide various features to the database server. These features help the developers to manage data in an efficient way.

Read Also: SQL Server Features and SQL Intro

SQL Server Integration with .NET Framework: Introduction to SQL

The .NET Framework is a collection of services and classes and exists as a layer between the .NET applications and the underlying operating system. SQL Server uses various services provided by the .NET Framework. For example, the notification services component is based on the .NET Framework and uses its services to generate and send notification messages.

SQL Server is integrated with the .NET Framework, as shown in the following figure.

Advantage of the .NET integration is that you can create managed database objects. Managed database objects are created by using any .NET language and can be embedded within the SQL Server. For example, developers can create an object that retrieves data from a Web server and saves it in a database table. This provides the software developer with a flexibility of writing codes in a language the developer is most comfortable with. Therefore, as a database developer, it is important to understand the .NET Framework.

The .NET Framework

The .NET Framework is an environment used to build, deploy, and run business applications. These applications can be built in various programming languages supported by the .NET Framework.

The .NET Framework is designed to make significant improvements in code reuse, code specialization, resource management, Multilanguage development, security, deployment, and administration. Therefore, it helps bridge the gap of interoperability between applications.

The .NET Framework consists of the following components:

• Development tools and languages
• Base Class Library
• Common Language Runtime (CLR)

Development Tools and Languages

The development tools and languages are used to create the interface for the Windows forms, Web forms, and console applications. The development tools include Visual Studio 2005 and Visual C# Developer. The languages that can be used are Visual Basic .NET, C#, or J#. These components are based on the .NET Framework base classes.

Base Class Library

The .NET Framework consists of a class library that acts as a base for any .NET language, such as Visual Basic, .NET, and C#. This class library is built on the object-oriented nature of the runtime. It provides classes that can be used in the code to accomplish a range of common programming tasks, such as string management, data collection, database connectivity, and file access.

CLR provides an environment for the application to run, discussed in next article.