- By David LeBlanc
T-SQL programming interface
What is the value in storing data if you cannot access it? SQL Server provides a rich programming language that allows you to write simple and complex queries against the underlying storage structures. Using T-SQL, you can write data manipulation queries that enable you to modify and access the data on demand. You can create objects such as views, stored procedures, triggers, and user-defined functions that act as a means of surfacing that data. Applications written in programming languages such as Visual Basic and C# .NET can send T-SQL queries from applications to the Database Engine. The Database Engine will then resolve the queries and send the results back to the client.
In addition, you can write data definition queries to create and modify objects that act as mechanisms for surfacing the data. T-SQL also allows you to manage server configurations and security seamlessly. T-SQL is a set-based language, meaning that it performs optimally when interacting with data in sets as opposed to manipulating strings or iterating over rows of data. While T-SQL is capable of these cursor-based operations, these types of operations are less efficient than a properly designed set-based approach. If you find you are using T-SQL to perform cursor-based operations, consider leveraging a common language runtime (CLR) language. Using your favorite compiler (Visual Studio, for example), you can extend the functionality of T-SQL.
SQL Server 2012 introduces several new T-SQL programming enhancements, including a simpler form of paging, windowing functions, and error handling. A THROW statement is introduced that provides a way to elegantly handle errors by raising exceptions. You can now create a FileTable that builds on the FileStream technology introduced in SQL Server 2008. Coupling the FileTable with FullTextSearch allows you to run complicated queries against massive amounts of text data (such as the complete text of this book). SQL Server 2012 also introduces several new conversion, string, logical, data, and time functions.