Hi Folks, I am sure that all of you would be aware about the role of Identity column in a table, i.e., it is column which keeps on incrementing without supplying the value explicitly during insertion. So in the figure below, CustomerID is an identity column: Now, let say I have a requirement to insert a CustomerID with value as ‘10’, just below of this value. How can I perform that? There are two ways: Either you turn off the Identity property by going into design, and opting for col...
Read More
Hello Folks, You might be wondering about how to handle NULLS if it’s a part of XML result set. Well, I have some keynotes about it, so please see it carefully: Till now, the default implementation is simply to remove the attribute, if the value is NULL. Although for most of the applications, it didn’t make much difference, but for some application it does. NIL in XML nearly equal to NULL in database. Will explain you this by an example: Here is the SELECT statement, which will give ...
Hey Folks, What do think when did FOR XML introduced? To know about all this, please go to the link, where you will find an article post: http://www.sqlservergeeks.com/articles/sql-server-bi/102/sqhow-to-generate-xml-output-using-for-xml-%E2%80%93-part-1 If you liked this post, do like us on Facebook at https://www.facebook.com/SQLServerGeeks And also comments on this!! Regards Piyush Bajaj @piyushbajaj007 piyush.bajaj.2007@gmail.com www.sqlservergeeks.com/blogs/piyush.bajaj.2007
Hello Folks, You would be very much aware of the View in SQL Server. But do you really know about Inline Table-Valued Functions? Well you don’t have to worry further more on this, because I will let explain you in a brief. With the introduction of SQL Server 2000, Microsoft has offered two types of table-valued functions, i.e., Inline and Multi-Statement Table-Valued Functions. But now I am going to deal with the Inline table-valued functions . As you would had seen in my previous blog-pos...
Hello Friends, It’s been a while that I have posted any blog. So here it is- Well with the arrival of SQL Server 2000, Microsoft has introduced the concept of User Defined Function (UDF). So there are basically three types of UDF’s: Scalar Functions Inline Table-Valued Functions Multi-Statement Table-Valued Functions In this blog-post, I would like to go on for Scalar Functions, and will take the other types in the next blog-post :) I have made some keynotes about the Scalar function...
Hello Folks, You would have heard about Cell-Level Encryption which was introduced earlier with the arrival of SQL Server 2005. It was built for the developers with a granular level of encryption for their applications that have specific data security requirements. While on the other side, Column-Level Encryption provides much flexibility in terms of performance and space costs. The main difference between the Column-Level Encryption and Cell-Level Encryption is that the expense of column-level ...
Hello Folks, You would have heard about Sparse Column . It has been introduced earlier with the arrival of SQL Server 2008. Well I have made some keynotes about it, so please go through it carefully: Sparse columns allow for the optimized storage of null columns. It reduces the space requirements for null values at the cost of more overhead to retrieve non-null values. Sparse columns do not take any space in database at all. As we know that the maximum column allowed per table in SQL Server is 1...
Hello Folks, FileStream data type has been introduced with the arrival of SQL Server 2008. Before that, we had to use the BOLB data type of SQL Server which allows us to store the data up to 2GB. But one disadvantage with that is file streaming becomes slow and performance of the file stream can be affected very badly. Well I have made some keynotes about FileStream data type: FILESTREAM allows large binary data (documents, images, videos, etc.) to be stored directly in the Windows file system. ...
Hello Folks, Table-Valued Parameter is a new parameter type which has been introduced with the arrival of SQL Server 2008. We can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function , without creating a temporary table or many parameters. Steps for Creating Table-Valued Parameters: Following are the steps as well as with the appropriate code- Step 1: Create a table type and define the table structure. USE Scho...
Hello Folks, You would have heard about this one. Well it was introduced first time with the arrival of SQL Server 2008. I have noted down some keynotes about Change Data Capture (CDC) , so please follow it carefully: CDC can be used to capture insertions, updates, and deletes in an SQL table in a database and place the changes in another table. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed...
Hello Folks, You would have heard this with the arrival of SQL Server 2005. Well I have made several keynotes on this, so please see it carefully: A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. It is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. The Copy Only Backup is especially useful for taking “one-off” backups for development or testing...
Hello Folks, You would have heard about Compressed Backups. The Compressed Backups is being introduced in SQL Server 2008 Enterprise and in later versions, but since the commencement of SQL Server 2008, every edition can restore a compressed backup. Well I am going to give some heads up on this: With the help of compressing backups, we can save time and disk space. The backup time backup time is usually reduced since the less data is being written to the disk. It’s a relatively simple proc...
Hello Folks, Have you heard this name? If not, you don’t have to worry at all because I am going to give you some heads up: The main usage for the SPACE function is to replicate spaces for a string. Its syntax can be shown as: SPACE ( integer_expression ) Here, integer_expression is a positive integer that indicates the number of spaces. If integer_expression is negative, a null string is returned. The following example trims the last names and concatenates a comma, two spaces, and the fir...
Hi Folks, As you would have seen, I have discussed about DBCC previously. But this blog-post is purely on TRACEON. DBCC TRACEON is being used to enable the specified trace flags. Well, you can see the syntax for the TRACEON: DBCC TRACEON (trace# [,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ] And for the TRACEOFF: DBCC TRACEOFF (trace# [,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ] Here, ‘trace#’ is for the number of trace flag to turn on or off, ‘n’ is the placeholder that indicates mu...
Hi folks, You might have seen my blog posts which was all about DBCC CHECKDB and how to repair the database using DBCC CHECKDB. But I have tried to focus this blog on the various DBCC Commands: DBCC CHECKALLOC (‘database’): A subset of DBCC CHECKDB that checks the allocation of all pages in the database. The report is very detailed, i.e., listing the extent count (64 KB or 8 data pages) and data-page usage of every table and index in the database. DBCC CHECKFILEGROUP (‘filegrou...
Hello Folks, You might have seen my last blog post, which was a brief introduction about DBCC CHECKDB. If you want to refer it again, then please check the link: http://www.sqlservergeeks.com/blogs/piyush.bajaj.2007/sql-server-bi/315/sql-server-%E2%80%93-dbcc-checkdb Well I have concentrated this blog-post mainly on how to repair the database with the help of DBCC CHECKDB. I have made some keynotes about it: If there’s an error and DBCC CHECKDB can fix it up, then DBCC CHECKDB indicates th...
Hello Folks, You might have heard about DBCC. If not, I am going to give you some heads up: DBCC Commands stands for Database Console Commands. They are being used for: Checking database integrity Performing Maintenance Operations on databases, tables, indexes, and filegroups Collecting and displaying information during troubleshooting issues The main function of DBCC CHECKDB is to check the logical and physical integrity of all the objects in the specified database by performing the following o...
Hello Folks, Do you know how to import CSV file into SQL Server? Or, How to load comma delimited file into SQL Server? It’s with the help of Bulk Insert. Well I have noted down some keynotes about this: Bulk-Insert can be used within any T-SQL script or stored procedures to import the data into SQL Server. It is being used to import the data into a database table or view in a user-specified format. The parameters of the command specify the table receiving the data, the location of the sour...
Hello Folks, You might have heard about it or maybe not. Don’t worry at all because I will be going to give you some of the heads up: Logon triggers were introduced earlier with the arrival of SQL Server 2005 SP2. It is somewhat similar to the DDL trigger which I had discussed it earlier in my blogs. Here’s the link for it: http://www.sqlservergeeks.com/blogs/piyush.bajaj.2007/sql-server-bi/306/sql-server-%E2%80%93-similarities-and-differences-between-dml-ddl-triggers Some main point...
Hello folks, You would have heard about this function while dealing with DDL triggers. If you would have seen my last blog post, it was about “Similarities and Differences between DML and DDL triggers”; I mentioned it while making out the differences between DML and DDL triggers. Well if you want to refer, then follow this link: http://www.sqlservergeeks.com/blogs/piyush.bajaj.2007/sql-server-bi/306/sql-server-%E2%80%93-similarities-and-differences-between-dml-ddl-triggers As you wou...
Hi Folks, You might have known about DML and DDL triggers as an individual, but do have any idea about their similarities and the differences between them? Well I have made some keynotes about it. SQL Server triggers fire once per data-modification operation, not once per affected row as in Oracle. SQL Server has two types of trigger: DML Triggers DDL Triggers What is Data Manipulation Language (DML) Triggers? A DML trigger can be defined as the action programmed to execute when a Data Manipulat...
Hello Folks, We are very much glad to announce that SQLServerGeeks.com is organizing SQL Server Day in Bangalore (India) on 15th October (Saturday). I just want to make you aware of the event. In case you are in Bangalore, do attend. The event will have speakers from Microsoft and the SQL MVP community. We will deliver 3 sessions. The event is absolutely free. Registration is required to book a seat. Find the registration link and all the details here: http://www.sqlservergeeks.com/default-categ...
Hi Folks, You would have seen my blog which was a brief description about the significant system functions. Well if you want to review it, than just click on the link: http://www.sqlservergeeks.com/blogs/piyush.bajaj.2007/sql-server-bi/282/sql-server-%E2%80%93-system-functions I have made some keynotes about the @@error system function. They are as follows: It returns the error number for the last Transact-SQL statement executed. The return type is Integer, and while 0 indicates success. This sy...
Hello Folks, You would have seen my blog-post which was about the Temporary Tables. If you want to follow to that post, please click the link below: http://www.sqlservergeeks.com/blogs/piyush.bajaj.2007/sql-server-bi/289/sql-server-%E2%80%93-temporary-tables This blog-post is all about Table Variables and the difference between the Table Variables and Temporary Tables. Table Variables: With the arrival of SQL Server 2000, Microsoft has introduced table variables as an alternate to use temporary ...
Hello Folks, You would have heard about this table earlier. I have make some keynotes on this, might make you feel easy about this. Microsoft SQL Server provides the concept of temporary table which helps the developer in a big way. Temporary tables are a useful tool in SQL Server provided to allow for short term use of data. These tables can be created at runtime and can do the all kinds of operations that one normal table can perform. It acts as a vehicle for passing data between objects. Acco...