luni, 27 iunie 2016

The Trie: A Neglected Data Structure

From the very first days in our lives as programmers, we’ve all dealt with data structures: Arrays, linked lists, trees, sets, stacks and queues are our everyday companions, and the experienced programmer knows when and why to use them. In this article we’ll see how an oft-neglected data structure, the trie, really shines in application domains with specific features, like word games.

Word Games

For starters, let’s consider a simple word puzzle: find all the valid words in a 4x4 letter board, connecting adjacent letters horizontally, vertically, or diagonally. For example, in the following board, we see the letters ‘W’, ‘A’, ‘I’, and ‘T’ connecting to form the word “WAIT”.
simple word puzzle
The naive solution to finding all valids words would be to explore the board starting from the upper-left corner and then moving depth-first to longer sequences, starting again from the second letter in the first row, and so on. In a 4x4 board, allowing vertical, horizontal and diagonal moves, there are 12029640 sequences, ranging in length from one to sixteen characters.
Now, our goal is to find the best data structure to implement this valid-word checker, i.e., our vocabulary. A few points to keep in mind:
  • We only need a single copy of each word, i.e., our vocabulary is a set, from a logical point of view.
  • We need to answer the following questions for any given word:
    • Does the current character sequence comprise a valid word?
    • Are there longer words that begin with this sequence? If not, we can abandon our depth-first exploration, as going deeper will not yield any valid words.
To illustrate the second point, consider the following board: There’s no point in exploring subsequent moves, since there are no words in the dictionary that start with “ASF”.
nothing starts with asf
We’d love our data structure to answer these questions as quickly as possible. ~O(1) access time (for checking a sequence) would be ideal!
We can define the Vocabulary interface like this (see here for the GitHub repo):
public interface Vocabulary {
    boolean add(String word);
    boolean isPrefix(String prefix);
    boolean contains(String word);
}

Candidate Data Structures

Implementing the contains() method requires a backing data structure that lets you find elements efficiently, while the isPrefix() method requires us to find the “next greater element”, i.e. we need to keep the vocabulary sorted in some way.
We can easily exclude hash-based sets from our list of candidates: while such a structure would give us constant-time checking for contains(), it would perform quite poorly on isPrefix(), in the worst case requiring that we scan the whole set.
For quite the opposite reason, we can also exclude sorted linked-lists, as they require scanning the list at least up to the first element that is greater than or equal to the searched word or prefix.
Two valid options are using a sorted array-backed list or a binary tree.
On the sorted array-backed list we can use binary search to find the current sequence if present or the next greater element at a cost of O(log2(n)), where n is the number of words in the dictionary.
We can implement an array-backed vocabulary that always maintains ordering of like this, using standard java.util.ArrayList and java.util.Collections.binarySeach:
public class ListVocabulary implements Vocabulary {
    private List<String> words = new ArrayList<String>();

    /**
     * Constructor that adds all the words and then sorts the underlying list
     */
    public ListVocabulary(Collection<String> words) {
        this.words.addAll(words);
        Collections.sort(this.words);
    }

    public boolean add(String word) {
        int pos = Collections.binarySearch(words, word);
        // pos > 0 means the word is already in the list. Insert only
        // if it's not there yet
        if (pos < 0) {
            words.add(-(pos+1), word);
            return true;
        }
        return false;
    }

    public boolean isPrefix(String prefix) {
        int pos = Collections.binarySearch(words, prefix) ;
        if (pos >= 0) {
            // The prefix is a word. Check the following word, because we are looking 
            // for words that are longer than the prefix
            if (pos +1 < words.size()) {
                String nextWord = words.get(pos+1);
                return nextWord.startsWith(prefix);
            }
            return false;
        }
        pos = -(pos+1);
        // The prefix is not a word. Check where it would be inserted and get the next word.
        // If it starts with prefix, return true.
        if (pos == words.size()) {
            return false;
        }
        String nextWord = words.get(pos);
        return nextWord.startsWith(prefix);
    }

    public boolean contains(String word) {
        int pos = Collections.binarySearch(words, word);
        return pos >= 0;
    }
}
If we decided to use a binary tree, the implementation could be even shorter and more elegant (again, here’s a link to the code):
public class TreeVocabulary extends TreeSet<String> implements Vocabulary {

    public TreeVocabulary(Collection<String> c) {
        super(c);
    }

    public boolean isPrefix(String prefix) {
        String nextWord = ceiling(prefix);
        if (nextWord == null) {
            return false;
        }
        if (nextWord.equals(prefix)) {
            Set<String> tail = tailSet(nextWord, false);
            if (tail.isEmpty()) {
                return false;
            }
            nextWord = tail.iterator().next();
        }
        return nextWord.startsWith(prefix);
    }

    /**
     * There is a mismatch between the parameter types of vocabulary and TreeSet, so
     * force call to the upper-class method
     */
    public boolean contains(String word) {
        return super.contains(word);
    }
}
In both cases, we can expect O(log n) performance for each access method (contains() and isPrefix()). As for space requirements, both the array-backed implementation and the tree-backed implementation requireO(n+M) where n is the number of words in the dictionary and M is the bytesize of the dictionary, i.e. the sum of the length of the strings in the dictionary.

Enter, the Trie

Logarithmic performance and linear memory isn’t bad. But there are a few more characteristics of our application domain that can lead us to better performance:
  • We can safely assume that all words are lowercase.
  • We accept only a-z letters—no punctuation, no hyphens, no accents, etc.
  • The dictionary contains many inflected forms: plurals, conjugated verbs, composite words (e.g., house –> housekeeper). Therefore, many words share the same stem.
  • Words have a limited length. For example, if we are working on a 4x4 board, all words longer than 16 chars can be discarded.
This is where the trie (pronounced “try”) comes in. But what exactly is a trie? Tries are neglected data structures, found in books but rarely in standard libraries.
For motivation, let’s first consider Computer Science’s poster child: the binary tree. Now, when we analyze the performance of a binary tree and say operation x is O(log(n)), we’re constantly talking log base 2. But what if, instead of a binary tree, we used a ternary tree, where every node has three children (or, a fan-out of three). Then, we’d be talking log base 3. (That’s a performance improvement, albeit only by a constant factor.) Essentially, our trees would become wider but shorter, and we could perform fewer lookups as we don’t need to descend quite so deep.
Taking things a step further, what if we had a tree with fan-out equal to the number of possible values of our datatype?
This is the motivation behind the trie. And as you may have guessed, a trie is indeed a tree!
But, contrary to most binary-trees that you’d use for sorting strings, those that would store entire words in their nodes, each node of a trie holds a single character (and not even that, as we’ll see soon) and has a maximum fan-out equal to the length of the alphabet. In our case, the length of the alphabet is 26; therefore the nodes of the trie have a maximum fan-out of 26. And, while a balanced binary tree has log2(n) depth, the maximum depth of the trie is equal to the maximum length of a word! (Again, wider but shorter.)
Within a trie, words with the same stem (prefix) share the memory area that corresponds to the stem.
To visualize the difference, let’s consider a small dictionary made of five words. Assume that the Greek letters indicate pointers, and note that in the trie, red characters indicate nodes holding valid words.
visualizing the trie

The Implementation

As we know, in the tree the pointers to the children elements are usually implemented with a left and right variable, because the maximum fan-out is fixed at two.
In a trie indexing an alphabet of 26 letters, each node has 26 possible children and, therefore, 26 possible pointers. Each node thus features an array of 26 (pointers to) sub-trees, where each value could either be null (if there is no such child) or another node.
How, then, do we look-up a word in a trie? Here is the method that, given a String s, will identify the node that corresponds to the last letter of the word, if it exists in the tree:
public LowercaseTrieVocabulary getNode(String s) {
 LowercaseTrieVocabulary node = this;
 for (int i = 0; i < s.length(); i++) {
  int index = LOWERCASE.getIndex(s.charAt(i));
  LowercaseTrieVocabulary child = node.children[index];
  if (child == null) {
   // There is no such word
   return null;
  }
  node = child;
 }
 return node;
}
The LOWERCASE.getIndex(s.charAt(i)) method simply returns the position of the ith character in the alphabet. On the returned node, a Boolean property node indicates that the node corresponds to the last letter of a word, i.e. a letter marked in red in the previous example. Since each node keeps a counter of the number of children, if this counter is positive then there are longer words in the dictionary that have the current string as a prefix. Note: the node does not really need to keep a reference to the character that it corresponds to, because it’s implicit in its position in the trie.

Analyzing Performance

What makes the trie really perform well in these situations is that the cost of looking up a word or prefix is fixed and dependent only on the number of characters in the word and not on the size of the vocabulary.
In our specific domain, since we have strings that are at most 16 characters, exactly 16 steps are necessary to find a word that is in the vocabulary, while any negative answer, i.e. the word or prefix is not in the trie, can be obtained in at most 16 steps as well! Considering that we have previously ignored the length of the string when calculating running time complexity for both the array-backed sorted list and the tree, which is hidden in the string comparisons, we can as well ignore it here and safely state that lookup is done in O(1) time.
Considering space requirements (and remembering that we have indicated with M the bytesize of the dictionary), the trie could have M nodes in the worst case, if no two strings shared a prefix. But since we have observed that there is high degree of redundancy in the dictionary, there is a lot of compression to be done. The English dictionary that is used in the example code is 935,017 bytes and requires 250,264 nodes, with a compression ratio of about 73%.
However, despite the compression, a trie will usually require more memory than a tree or array. This is because, for each node, at least 26 x sizeof(pointer) bytes are necessary, plus some overhead for the object and additional attributes. On a 64-bit machine, each node requires more than 200 bytes, whereas a string character requires a single byte, or two if we consider UTF strings.

Performance Tests

So, what about performance? The vocabulary implementations were tested in two different situations: checking for 20,000,000 random strings and finding all the words in 15,000 boards randomly generated from the same word list.
Four data structures were analyzed: an array-backed sorted list, a binary tree, the trie described above, and a trie using arrays of bytes corresponding to the alphabet-index of the characters themselves (a minor and easily implemented performance optimization). Here are the results, in ms:
performance results
The average number of moves made to solve the board is 2,188. For each move, a word lookup and a prefix lookup are done, i.e., for checking all the boards, more than 32M word lookups and 32M prefix lookups were performed. Note: these could be done in a single step, I kept them separated for clarity in the exposition. Compacting them in a single step would cut the time for solving the boards almost in half, and would probably favour the trie even more.
As can be seen above, the word lookup perform better with the trie even when using strings, and is even faster when using alphabet indexes, with the latter performing more than twice as fast as a standard binary tree. The difference in solving the boards is even more evident, with the fast trie-alphabet-index solution being more than four times as fast as the list and the tree.

Conclusions

The trie is a very specialized data structure that requires much more memory than trees and lists. However, when specific domain characteristics apply, like a limited alphabet and high redundancy in the first part of the strings, it can be very effective in addressing performance optimization.

References

An extensive explanation of tries and alphabets can be found in chapter 5 of Robert Sedgewick’s book “Algorithms, 4th edition”. The companion website at Princeton has the code for an implementation of Alphabet and TrieST that is more extensive than my example.
Description of the trie and implementations for various languages can also be found on Wikipedia.
This article was written by Anna Chiara Bellini, a Toptal Java developer.

duminică, 1 februarie 2015

Choosing a license for your code on Github

I added licensing information to all my open source code on Github and Sourceforge after a request from someone who wanted to use parts of it.
Turns out if someone wants to use your code those licences that you keep seeing everywhere are pretty important. Even if it is just a small, apparently unimportant bit of code.
The person in question also provided me with a link that explains the differences between the various licences.
After some consideration decided to go for the MIT license.
Why MIT?
Because and I quote
It lets people do anything they want with your code as long as they provide attribution back to you and don’t hold you liable
. Sounds good to me at this moment in time.
Found Choosealicense.com to be a very interesting website.

luni, 11 martie 2013

Contribuited to my second open source project

I was looking for an application that could migrate issues from one of my Bitbucket repositories to one of my Github repositories.
I couldn't find anything already written but I found something close Git2BitIssues by tekstop. His application basically did the opposite of what I wanted. Moved things from Git to Bit not from Bit to Git. I built on it and achieved the desired result. You can check out the code if you need something similar done.
This has been my first experience using collaborative coding on GitHub and I was amazed by how smoothly everything went. The idea behind improving on someone else's code is that you first fork what he did. Basically you get a copy of his repository on your own account. You push your changes to this account and then you ask the author if he is ok with your changes via a pull request. It is all really easy and kind of fun.
Anyway now you can move your issues from one system to another with this application if you need to.
Cheers!

sâmbătă, 20 octombrie 2012

My first open source project - Seringa: The SQLi Framework

I publically launched my first open source project today. It's hosted at github.
https://github.com/paratechnical/Seringa
A short description copied from the Wiki:
Seringa(Romanian for seringe) is an SQL injection framework featuring high customizability and a user-friendly interface. It is completely open source. It uses the .NET 4.0 framework and Windows Presentation Foundation(WPF) for the GUI. With regard to design it utilizes the Strategy Pattern to distinguish between various SQLi strategies whilst storing other relevant data such as exploits, payloads and patterns in xml files so that the framework can be easily customized from the outside(a manifestation of the Open-Closed Principle).
Seringa allows you to:
  • scan Google search results given a search string
  • test search results for SQLi vulnerability
  • test a single url for vulnerability
  • extract a database structure(databases,tables,columns) in a tree form
  • execute given payloads and receive results(some predefined queries include current database name, current database user, current database version etc)
  • save your penetration testing process to a file(mapping file) and load it later
  • use a proxy(regular or socks) when testing

Everyone is welcomed to contribute.

Contribuited to my first open source project - jQGrid

I had some problems with a jqGrid that contained a table within it. Turned out there was a bug in jQGrid. I fixed the problem locally and I thought others might be having the same problem as well so I tried to commit my changes to the jQGrid source repository. That didn't exactly go as planned but I managed to push the code throgh eventually. Check it out.
Anyway jqGrid is great. I really recommend it.

luni, 30 ianuarie 2012

JMultiLocGMap - joomla module that I created

I created a new Joomla module.
It's purpose is to display a Google map with multiple locations on it.
Locations can be grouped into categories.
All configuration is done through an xml inside it.
The module was born out of necessity. I needed it for a website I was working on for my father.
The requirement was to have a select box and a Google map and upon changing the selected item in the selection box a new set of Google markers would appear on the map.
For example the selection box could have "schools" and "kintergardens" and when modifying the selection to "schools" the schools on the map would be displayed and when choosing "kintergardens" the kindergartens on the map would be displayed.
The xml handles all configuration for this.
This is the first version(0.0.1). The project is still in it's infancy of course.
Hope it helps someone. Everything is open-source.
The project is hosted here.

sâmbătă, 5 martie 2011

Merging 2 word 2007(open xml) documents

I had to merge 2 word documents once.
They were both in word 2007(open xml) format.
I searched online everywhere for a way to merge them.
All the links were about using the altChunk element. I have a problem with that. The entire document gets put in there and that is a lot of unneeded data(like header and footer etc.).

So I did it another way:
1. cloned the second document's body
2. cloned all it's children and added them to the first document's main part


Radix tree implementation in C#

Update: you can find the entire source code here 

This is my implementation of a radix tree data structure. This is a data structure very similar to a trie. Basically instead of having each one of your nodes hold a char you have each one of your nodes store a string or as wikipedia puts it, it is a trie where "each node with only one child is merged with its child".
As I understand it the names radix tree and crit bit tree are only applied to trees storing integers and Patricia trie is retained for more general inputs.
As usual I am outputting the code for each class.

First, the node:

Then the actual tree
And the test program:

sâmbătă, 26 februarie 2011

PHP sitemap using a tree data structure

I was once asked to create a sitemap given a specific table structure.
This was the structure(the table):




The resulting sitemap as I understood it was supposed to look something like this:



The parenthesis after the titles actually hold the value of the id of the node so they weren't in the original design but whatever.

So I implemented this in PHP using a tree data structure where each node held 3 pieces of information:
id - the current node's id as read from the database
parent_id - the current node's parent id as read from the database
title - the title

A real-world implementation would also have to include a link and print out elements but this is good enough for educational purposes.






CREATE TABLE IF NOT EXISTS `sitepages` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`id_parent` int(11) unsigned NOT NULL,
`title` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `sitepages` (`id`, `id_parent`, `title`) VALUES
(1, 0, 'Home'),
(2, 1, 'Page 1'),
(3, 0, 'Page 2'),
(4, 1, 'Page 3'),
(5, 3, 'Page 4'),
(6, 1, 'Page 5'),
(7, 4, 'Page 6'),
(8, 7, 'Page 7'),
(9, 5, 'Page 8');


I used to php classes to represent the tree. One to represent a node of the tree and one for the tree itself:

This is for the node:



//a node of a tree that is not binary, each nodes has multiple subnodes
class Node
{
public $id;
public $id_parent;
public $childnodes;
public $title;

public function __construct($id,$id_parent,$title)
{
//in the test script I use mysql_fetch_object which populates the members and then //calls the constuctor with all
//parameters as null
//the following checks are here to make sure no members are accidentally overriden

if(!isset($this--->id))
$this->id = $id;

if(!isset($this->id_parent))
$this->id_parent = $id_parent;

if(!isset($this->title))
$this->title = $title;

if(!isset($this->childnodes))
$this->childnodes = array();
}

//check if this node has subnodes or not
public function hasChildren()
{
if($this->childnodes === null)
return false;

if(!isset($this->childnodes))
return false;

if(count($this->childnodes) == 0)
return false;

return true;
}
}



This is the tree:


require("node.class.php");
class Tree
{

private $root;

//construct a tree with only one root node public function __construct()
{
$this->root = new Node(0,0);
}

//recursively traverse the tree looking for the appropiate parent id
//the node to be inserted($node) must have a parent id equal to one of the ids in the nodes of the tree
//if such an id is found in a node the node is included as a subnode of that node
private function addNodeRecursive(&$nextnode,$node)
{
if($nextnode == null)
return false;

if($nextnode->id == $node->id_parent)
{
array_push($nextnode->childnodes,$node);
return true;
}
else
{
if($nextnode->hasChildren())
{
foreach ($nextnode->childnodes as $childnode)
{
$this->addNodeRecursive($childnode,$node);
}
return false;
}
else
return false;
}
}

//add a node to the tree
//check if it is a valid node start at the root and recursively traverse the tree looking for the appropiate parent id
public function addNode($node)
{
if(!($node instanceof Node))
return;

$nextnode = $this->root;

$this->addNodeRecursive($nextnode,$node);
}

//print the tree in sitemap style
public function printSiteMap()
{
foreach($this->root->childnodes as $childnode)
{
$this->printTree($childnode,0);
}
}

//print the tree recursively
//first print the current node's value and then print the cycle through it's subnodes
public function printTree(&$nextnode,$level)
{
$this->printNodeAccordingToLevel($nextnode,$level);

if($nextnode->hasChildren())
{
foreach($nextnode->childnodes as $childnode)
{
$this->printTree($childnode,$level+1);
}
}
}

//add the appropiate number of before each node value so as to obtain a nicely formatted sitemap
//each node value is indented with respect to the node's level
private function printNodeAccordingToLevel($node,$level)
{
for($i=0;$i<$level;$i++) echo " "; echo $node->title."(".$node->id.")";

echo "
";
}

}





And this is a test just to make sure everything works:



require("tree.class.php");
$tree = new Tree();
if(!($conn = mysql_connect("localhost","root","")))
{
echo "Could not connect";
exit;
}
if(!mysql_selectdb("sitemap"))
{
echo "No such database";
exit;
}
if($result = mysql_query("SELECT id,id_parent,title FROM sitepages"))
{
while ($page = mysql_fetch_object($result,'Node'))
$tree->addNode($page);
}

$tree->printSiteMap();

miercuri, 23 februarie 2011

Prefix tree(trie) implementation in F#

Update: all the code can be downloaded from here

This is my implementation of a a prefix tree data structure in F#.
This structure is described in more detail in a previous post of mine.
I just describe the F# related stuff in the comments.
I'm sure this is not the best implementation. I am aware this is not the most functional implementation ever.
I am open to all constructive criticism of course.

luni, 31 ianuarie 2011

Autocomplete functionality using prefix trees

Update: all the source code for this article can be accessed from here

This is a follow up to my previous post on implementing a prefix tree in C#.
This expands on that idea and uses the data structure to generate a list of autocomplete matches for a given input character sequence.
These are the modifications I brought to the code:





This is the main program:

duminică, 30 ianuarie 2011

Implementation of the prefix tree(trie) data structure in C#

Update: all the source code for this article can be accessed from here

A prefix tree is a data structure with many uses. I won't go into details about this data structure Wikipedia does a better job than I could. What I will do is post a C# implementation of this data structure and in a following post I will show how to use this data structure when implementing an auto-complete solution.

These are the properties of a prefix tree:
  • it stores strings(finite sequences of chars)
  • it had nodes :) (doooooooh!)
  • each node stores a character
  • each node can have a number of subnodes(0,1,2, ... infinity)
  • the char stored in a subnode is the one following the char stored by the node in a string
  • each node has a flag which is raised if a word ends there(this doesn't mean it can't have subnodes for example a prefix tree can store the words "cat" and "catastrophy" in this case node 3 stores 't' and has a subnode that stores 'a')

And now the code:
The node:



The tree:



The main program:

marți, 18 ianuarie 2011

How to concatenate data from multiple rows into a single rows

I had a problem once. One table had a lot of data in a column on multiple rows. I had to display it in a view but I needed all the data on that column to be displayed in a single cell on a row. This view was supposed to be the data source for a report ... long story ...
Anyway, the basic idea is this:




SELECT some_column = (your select query
for xml path(''))



this only works in sql server 2008 because earlier versions don't have the "xml" keyword.
Here is an example:




SELECT Documente = (SELECT coalesce(td.Descriere+',','') as [text()] FROM Document d
INNER JOIN TipDocument td ON d.TipDocumentId = td.Id for xml path(''))

duminică, 16 ianuarie 2011

Unable to start debugging on the web server error

Just in case you also received this error:

Unable to start debugging on the web server. Debugging failed because
integrated Windows authentication is not enabled. Please see Help for
assistance

Here is what I did to not get it any more

  1. Open IIS Manager (Internbet Information Services)
  2. Right-click the web site (in case you run it locally you only have
    Default web site) and pick Properties
  3. Choose "Directory Security" tab and click Edit on "Anonymous access and
    authentication control"
  4. In the opening window, uncheck "Allow Anonymous access" and check
    "Integrated Windows Authentication"

Finding the path to an installed application in C#

Well, you can find the path to a locally installed application by querying the windows registry.
The information is here:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\

Example photoshop.exe

I wrote this function in C# to do this for me:




///
/// get the path to a specific application from the registry
///

/// name of the application exe
///
public static string GetApplicationPath(string ExeName)
{
try
{
RegistryKey OurKey = Registry.LocalMachine;
OurKey = OurKey.OpenSubKey(@"Software\Microsoft\Windows\CurrentVersion\App Paths\"+ExeName, true);
if (OurKey != null)
return OurKey.GetValue("").ToString();
else
return "";
}
catch(Exception ex)
{
return "";
}
}

Exporting MS SQL data as sql scripts

In PhpMyAdmin exporting data to sql statements not to .mdb files or other databases is very straight forward. When I first had to do this with Sql Management Studio I didn't know how at first.
Then I found out.
Basically, what you have to do is this:
1.right click on a database item
2.go to tasks -> generate scripts in the menu that appears

You now have a wizard that will help you generate scripts for the objects you need. You can either generate one script for all the objects in your database(tables, triggers, stored procedures etc) or one script for just some of the object(you choose which) or each object you choose in it's own script. You can either generate these resulting scripts in files or in new query windows. Everything is configurable from the options window(the wizard step with the title "Choose script options").

Here is an example:
I wanted to just script the triggers in a db. Each in it's own file.
In the options window I set "script triggers" to true and in the following wizard form(with the title "Output options") I chose "file per object".


Reseting visual studio

Visual Studio needs to be reset(in order to change a setting) / is frozen / doesn’t work right

1. Close all instances of VS

2. Click Start > Run...

3. Type "devenv /resetuserdata"

4. Open task manager and wait until devenv.exe process is finished executing

5. Restart VS2005

In Win 7:

search for the folder in which devenv.exe can be found

follow the steps above but instead of typing “devenv /resetuserdata” type “devenv.exe /resetuserdata”

Finding the first non consecutive value in an identity column

This is an interesting SQL problem that presented itself to me once.
You have this table. It has of course a primary key which is an identity column which has a seed of 1.
Then you insert a some data and then delete some data.
The primary key column(let's call it id from now on) will no longer contain only consecutive numbers.
So the question is: "How do you find the first non consecutive value in this column?"
Well, the first thing that came to my mind at the time was making a cursor. That works too but it's not very efficient.
Here is one implementation using a self join which worked for me:



select top 1 t1.id from [table] t1 left join [table] t2 on t1.id+1 = t2.id where t2.id is NULL

vineri, 14 ianuarie 2011

Opening compressed databases or .mdf files in SQL Server Management Studio

If when you try to open a database or attaching a .mdf file you get the error:

is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

So here is the algorithm I followed to solve this issue:

1. only if an existing database gives this error detach the database – right click -> Detach

2. closed all SQL Server Management Studio instances

3. Stop the SQL Server service.. (Start->Run -> services.msc)

4. Selected .mdf file(should be blue if compressed ) Right click properties -> Advanced -> Uncheck "Compress files to save disk space"

5.(re)attach .mdf file

Setting the week's start day in MS SQL

SET DATEFIRST 1 --start day is Monday

SET DATEFIRST 7 --start day is Sunday – default

To test it PRINT @@DATEFIRST

The Trie: A Neglected Data Structure

From the very first days in our lives as  programmers , we’ve all dealt with data structures: Arrays, linked lists, trees, sets, stacks and...