Uniqueidentifier vs. IDENTITY By Sean Baird on 12 September 2000 | 21 Comments | Tags: Identity Kim writes: "with an identity column you can SELECT @@identity after inserting a row. How do you get the value of a uniqueidentifier column after an insert? thanks!" Uniqueidentifiers are new to SQL 7.0, and behave a little differently than our friend the IDENTITY column. First off, for those of you not familiar with the uniqueidentifier datatype, here's the lowdown:Uniqueidentifiers are also referred to as GUIDs. DECLARE @GUID uniqueidentifierSET @GUID = NEWID()INSERT Item VALUES (@GUID,'Yak Hoof') So, to answer Kim's question: Sorry, there isn't a way to get the value of a uniqueidentifier column after an insert. The major advantage of using GUIDs is that they are unique across all space and time. The main disadvantage to using GUIDs as key values is that they are BIG. Not only that, but they're just plain hard to read. -SQLGuru
DbUnit - Database Testing Database Testing Richard Dallaway notes about database unit testing inspired me the realization of the DbUnit framework. I think this is a very good text about this subject. The original text is much longer and I strongly suggest you to read it as well. Unit testing database These are my notes on how I've gone about unit testing database functionality. The problem is this: you have a SQL database, some stored procedures, and a layer of code sitting between your application and the database. Why bother? I'm guessing some, if not a lot, of database development goes like this: set up database, write code to access database, run code, do a SELECT to see if the records showed up in the database. The problem with visual inspection is this: you don't do it often, and you don't check everything every time. Automated tests — painless tests that run often and test lots — reduce the chances of your data is going missing. You need [multiple] databases The production database.
AppendText Method public static StreamWriter AppendText( string path ) Parameters path Type: System.String The path to the file to append to. Return Value Type: System.IO.StreamWriterA stream writer that appends UTF-8 encoded text to the specified file or to a new file. The path parameter is not case-sensitive. The following example appends text to a file. .NET Framework Supported in: 4.5.1, 4.5, 4, 3.5, 3.0, 2.0, 1.1, 1.0 .NET Framework Client Profile Supported in: 4, 3.5 SP1 .NET for Windows Phone apps Supported in: Windows Phone 8.1, Windows Phone 8, Silverlight 8.1 Windows Phone 8.1, Windows Phone 8, Windows 8.1, Windows Server 2012 R2, Windows 8, Windows Server 2012, Windows 7, Windows Vista SP2, Windows Server 2008 (Server Core Role not supported), Windows Server 2008 R2 (Server Core Role supported with SP1 or later; Itanium not supported)
CodeProject: Improving the performance of queries using SQL Server Part 2. Free source code and programming help Introduction In the previous article: Improving the performance of queries using SQL Server: Part 1, we discussed using SQL Profiler to identify queries that need looking at, to see if performance can be improved. We learned how to set the criteria that allow us to focus on the queries that are most likely to give us performance gains if we fix them. In this article, we are going to look at how to take the information we gleaned from the SQL Profiler and use it to enhance our queries so that they are more performant. Starting out Before we dive into looking at indexes and execution plans, we need to consider whether or not a query is well written or not. This query is a prime example of a badly written query that needs a serious kick up the backside before we even consider the plan: As we can see, the big problem with this query is that it is just badly written. Right, the first thing that we need to do then is to rewrite the query so that it cuts out the unnecessary work that it is doing.
asp.net - Entity Framework v2 doesn't support sql 2000 Factoring a database interface class: the "DBIGenerator" class into action I firmly believe that dynamically building DB interfaces will be best understood by example. Bearing that concept in mind, let's build an example that implements the "DBIGenerator" class and quickly creates a "User" DB interface class, which behaves as a centralized mechanism to access rows in a sample "users" table. Here's the corresponding code: // include MySQLConnector wrapping classrequire_once 'mysqlclass.php';// include the "DBIGenerator" classrequire_once 'dbigeneratorclass.php';// instantiate a new MySQLConnector object and connect to MySQL$db=&new MySQLConnector(array('host'=>'host','user'=>'user' ,'password'=>'password','database'=>'databasename'));// set class properties-accessors and modifiers$options=array('id','firstname','lastname','email');// instantiate a DBIGenerator object$gn=&new DBIGenerator('User','DBICLASSES/',$options);// create User class$gn->create();// get $user objectif(! The above example demonstrates how simple it is to create a DB interface class. Summary
Caché - World's fastest high performance object database Unified Multi-Model Architecture Within Caché, data can be modeled and stored as tables, objects, or multidimensional arrays (hierarchies). Different models can seamlessly access data – without the need for performance-killing mapping between models. Built-in support for dynamic data objects (such as XML and JSON) enables easy interoperability and rapid development of Web applications. High-Performance Queries SQL is the lingua franca for querying Caché data across all data models. Distributed Systems InterSystems Enterprise Cache Protocol allows a network of many servers to behave as a single data store, dramatically enhancing the scalability and performance of distributed applications. High Availability through Database Mirroring Caché Database Mirrors do not require large investments in hardware, support, operating system licenses, or storage. Security Model Secure data-at-rest and data-in-motion while minimizing the burden on application performance. Application Development
10 Essential SQL Tips for Developers | Nettuts+ SQL is yet another essential language for developers wishing to create data-driven websites. However, many developers are unfamiliar with various aspects of SQL; so in this article, we'll analyze ten essential tips. 1. Use The Right Language Web developers often have a plethora of languages at their disposal. Let's review the following code. When you are writing code, make sure that it works efficiently. Too many developers are satisfied with code that performs adequately on 100 rows of data, with little thought ahead to when the database will have 10,000 rows. 2. Databases store valuable information. In case you aren't convinced about the seriousness of database security, these two articles should help drive the point home: "The FBI and Virginia State Police are searching for hackers who demanded that the state pay them a $10 million ransom by Thursday for the return of millions of personal pharmaceutical records they say they stole from the state's prescription drug database." 3. 4. 5. 6.
Best approach to remove time part of datetime in SQL Server Identifying and non-identifying relationships An identifying relationship means that the child table cannot be uniquely identified without the parent. For example, you have this situation in the intersection table used to resolve a many-to-many relationship where the intersecting table's Primary Key is a composite of the left and right (parents) table's Primary Keys. Example... Account (AccountID, AccountNum, AccountTypeID) PersonAccount (AccountID, PersonID, Balance) Person(PersonID, Name) The Account to PersonAccount relationship and the Person to PersonAccount relationship are identifying because the child row (PersonAccount) cannot exist without having been defined in the parent (Account or Person). A non-identifying relationship is one where the child can be identified independently of the parent ( Account - AccountType) Example... The relationship between Account and AccountType is non-identifying because each AccountType can be identified without having to exist in the parent table. Resources