SQLServer-QA.net - Knowledge Sharing Network (SSQA.net) » All Tags » T-SQL (RSS)

MSMVPS.COM

The Ultimate Destination for Blogs by Current and Former Microsoft Most Valuable Professionals.

Browse by Tags

Blogs

SQLServer-QA.net - Knowledge Sharing Network (SSQA.net)

Syndication

News

Blog Archive List

Proper usage of @@rowcount

Lot of newbies struggle to unserstand how @@Rowcount works in SQL Server. @@Rowcount is used to know the number of rows affected for the last select,insert,update or delete statements. You should use @@rowcount immediately after the statement For example...

Published Mon, Aug 9 2010 0:32 by SqlServer-QA.net

Filed under: tsql, T-SQL, sql_server, @@rowcount
host_name() and user_name() functions - Simple way to auditing

For various reasons you may need to audit some activities on certain tables.Usually you can use triggers for this.Sometimes you may want to know the system from which data are added or the user who added data.In these cases, you can use two functions...

Published Wed, Aug 4 2010 1:00 by SqlServer-QA.net

Filed under: tsql, T-SQL, sql_server, auditing, user_name(), host_name()
Various uses of VALUES Clause in SQL Server 2008

As you know well, VALUES clause is used to insert values to the table A simple example is declare @t table (id int , col1 int, col2 int, col3 int) insert into @t values(1, 14, 12, 24) select * from @t But in version 2008, you can do more things using...

Published Mon, Aug 2 2010 2:10 by SqlServer-QA.net

Filed under: 2008, tsql, T-SQL, values, sql_server
App_name() function

Sometimes back, my friend told me that they had an application where users update their timesheets. He suspected that some users who had access to the database directly update some entries. He wanted to know whether some users update entries using a query...

Published Wed, Jul 28 2010 3:14 by SqlServer-QA.net

Filed under: function, tsql, T-SQL, sql_server, app_name()
CAST vs CONVERT

Both these functions are used to convert values from one datatype to another But there are some differences between them 1 CAST is ANSI standard and CONVERT is specific to SQL Server 2 CAST can't be used for formating purposes. But CONVERT can be...

Published Thu, Jul 22 2010 7:19 by SqlServer-QA.net

Filed under: convert, tsql, T-SQL, sql_server, cast
InitCap function

Often people ask how to use InitCap function in SQL Server Initcap is used to capitalise each first letter of the word and keep rest as non-capital Pinal Dave has a post at http://blog.sqlauthority.com/2007/02/01/sql-server-udf-function-to-convert-text...

Published Mon, Jul 19 2010 5:11 by SqlServer-QA.net

Filed under: tsql, T-SQL, sql_server, initcap, proper_case
Fun with dot

The symbol dot (.) plays a major role when you write queries It is mainly used to speicify the columns for the respective tables when a join is used as you see in the following example select t1.col1,t1.col2,t2.col3,t2.col4 from table1 as t1 inner join...

Published Mon, Jul 12 2010 5:15 by SqlServer-QA.net

Filed under: tsql, T-SQL, sql_server, dot
Happy Holidays

In the Query Analyser, set the Result mode to Text (Press CTRL+T) and run the following code set nocount on select space ( 17 - len ( replicate ( char ( 94 ), no )))+ replicate ( char ( 94 ), no * 2 - 1 ) from ( select top 10 row_number () over ( order...

Published Thu, Dec 31 2009 3:58 by SqlServer-QA.net

Filed under: sql server, T-SQL, happy holidays
SQL Server 2008 features that work only in Enterprise Edition

One of the new features available in SQL Server 2008 is compressed backup which takes backup of a database with mimimum possible size. The syntax is backup database db_name to disk = 'backup_path' with init , compression But this works only in...

Published Tue, Nov 3 2009 7:18 by SqlServer-QA.net

Filed under: sql server, 2008, T-SQL, enterprise edition
Finding out problamatic data in Bulk Insert data truncation error

Suppose you want to import data from a text file to a table, one of the options is to use Bulk Insert command It allows to import data to the table, though one/more columns don't have enough size to get the maximum length of data from text file.It...

Published Mon, Oct 19 2009 5:32 by SqlServer-QA.net

Filed under: sql server, bulk insert, T-SQL, data truncation
How to Generate Script along with Data?

One of the new features available in SQL Server 2008 is scripting the tables along with data (in the form of INSERT statements). Here are the useful links that can help you generating script with data SQL Server 2008 http://www.kodyaz.com/articles/sql...

Published Fri, Aug 21 2009 7:49 by SqlServer-QA.net

Filed under: sql server, script, T-SQL
Regular expression

One of the posters asked a question in the forums about finding a number in an expression consists of alphabets and number if a particular digit is provided Consider the following set of data this is test place located at no 123456 test foo 35234 bar...

Published Mon, Aug 3 2009 6:06 by SqlServer-QA.net

Filed under: sql server, T-SQL, regular expression
Removing unwanted characters

Sometimes when you import data from other system , you may want to clean up data by removing unwanted characters You can use Replace function . Suppose you want to remove the character ^ from the string declare @str varchar ( 100 ) set @str = 'test...

Published Mon, May 11 2009 8:00 by SqlServer-QA.net

Filed under: sql server, T-SQL, clean data
How to find size of backup files in a folder?

I was asked to findout total size of all the backup files in the folder As I didnt have access to view the folder of the server , I used this . I hope it may be helpful create table #t ( files varchar ( 1000 )) Insert into #t EXEC master .. xp_cmdshell...

Published Thu, May 7 2009 5:14 by SqlServer-QA.net

Filed under: sql server, T-SQL, backup size
Import from Text file - Single column to multiple columns

One of the users asked this question in the SQL forum I have data in the text as follows AccountNo: 00234543 AccountName: Kickser City: Chicago Country:USA AccountNo: 00234543 AccountName: Annis City: Seatle Country:USA AccountNo: 12234456 AccountName...

Published Tue, May 5 2009 5:18 by SqlServer-QA.net

Filed under: sql server, bulk insert, T-SQL, import text file
Tables with no Primary and Foreign keys

John Paul Cook has posted in his blog to show the list of tables that have no Referential Integrity. What if you want to know list of tables that have no primary and foreign keys? Here is the query select table_name from INFORMATION_SCHEMA.TABLES where...

Published Fri, Apr 10 2009 9:44 by SqlServer-QA.net

Filed under: sql server, T-SQL, no references
Last Backup/Restore date of database

If you want to know the last Backup/Restore date of database, use the following --Last Backup date of database --Method 1 In Management Studio,Expand database,Right Click on the database and click properties Refer Last Batabase Backup which is availalbe...

Published Fri, Apr 3 2009 5:58 by SqlServer-QA.net

Filed under: sql server, T-SQL, backup/restore date
Table level Backup/Restore

It may be very useful if we have feature to take backup or restore a table I have posted my suggestion here Table level Backup/Restore What do you think of it?...( read more ) Read More...

Published Thu, Apr 2 2009 9:56 by SqlServer-QA.net

Filed under: sql server, T-SQL, table backup/restore
Filling random passwords - Set based approach

In this post , I showed a way to generate 8 characters random password If you have table with data and add a column for password and want to fill that column with random passwords , you can use this approach. I have seen the usage of a function that generates...

Published Mon, Mar 30 2009 9:23 by SqlServer-QA.net

Filed under: sql server, random password, T-SQL
Behavior Changes to Database Engine Features in SQL Server 2008

Updated Point on BOL is available at http://msdn.microsoft.com/en-us/library/ms143359.aspx...( read more ) Read More...

Published Sat, Mar 7 2009 4:24 by SqlServer-QA.net

Filed under: sql server, behaviour changes, T-SQL

1 2 3 Next >

Questions? Contact Susan at Susan-at-msmvps.com. Each post's copyright held by the original author. All rights reserved. Blog site is an independent site not sponsored by Microsoft.
Our servers would like to thank www.ownwebnow.com and www.exchangedefender.com. We wouldn't be here without the generosity of Vlad Mazek and his companies.

Powered by Community Server (Commercial Edition), by Telligent Systems

zines:andrewbelon.com

Browse by Tags

Blogs

Syndication

Recent Posts

Tags

News

Archives

Blog Archive List

Blog Archive List

Blog Archive List

Blog Archive List

Blog Archive List

Blog Archive List

Blog Archive List

Blog Archive List

Blog Archive List

Proper usage of @@rowcount

host_name() and user_name() functions - Simple way to auditing

Various uses of VALUES Clause in SQL Server 2008

App_name() function

CAST vs CONVERT

InitCap function

Fun with dot

Happy Holidays

SQL Server 2008 features that work only in Enterprise Edition

Finding out problamatic data in Bulk Insert data truncation error

How to Generate Script along with Data?

Regular expression

Removing unwanted characters

How to find size of backup files in a folder?

Import from Text file - Single column to multiple columns

Tables with no Primary and Foreign keys

Last Backup/Restore date of database

Table level Backup/Restore

Filling random passwords - Set based approach

Behavior Changes to Database Engine Features in SQL Server 2008