SQL Tips and Tricks Archives - Page 511 of 536 - SQL Authority with Pinal Dave

SQL SERVER – Fix : Error : Server: Msg 131, Level 15, State 3, Line 1 The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000)

June 30, 2007

Error: Server: Msg 131, Level 15, State 3, Line 1 The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000) When the the length is specified in declaring a VARCHAR variable or column, the maximum length allowed is still 8000. Fix/WorkAround/Solution: Use either…

SQL SERVER – Recompile All The Stored Procedure on Specific Table

June 29, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Best Practices, SQL Stored Procedure

I have noticed that after inserting many rows in one table many times the stored procedure on that table executes slower or degrades. This happens quite often after BCP or DTS. I prefer to recompile all the stored procedure on the table, which has faced mass insert or update. sp_recompiles…

SQL SERVER – 2005 Improvements in TempDB

June 28, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

Best Practices, SQL Download, SQL TempDB

Following are some important improvements in tempdb in SQL Server 2005 over SQL Server 2000 Input/Output traffic to TempDB is reduced as logging is improved. In SQL Server 2005 TempDB does not log “after value” everytime. E.g. For INSERT it does not log after value on log as that will…

SQL SERVER – Running Batch File Using T-SQL – xp_cmdshell bat file

June 27, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Computer Network, SQL Scripts, SQL Utility

In last month I received few emails emails regarding SQL SERVER – Enable xp_cmdshell using sp_configure. The questions are 1) What is the usage of xp_cmdshell and 2) How to execute BAT file using T-SQL? I really like the follow up questions of my posts/articles. Answer is xp_cmdshell can execute…

SQL SERVER – 2005 List All Tables of Database

June 26, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL System Table

This is very simple and can be achieved using system table sys.tables. USE YourDBName GO SELECT * FROM sys.Tables GO This will return all the tables in the database which user have created. Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL

SQL SERVER – Explanation and Example Four Part Name

June 26, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

What is four part name? Explanation : ServerName.DatabaseName.DatabaseOwner.TableName Example : localhost.AdventureWorks.Person.Contact Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL SERVER – Repeate String N Times Using String Function REPLICATE

June 25, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL String

I came across this SQL String Function few days ago while searching for Database Replication. This is T-SQL Function and it repeats the string/character expression N number of times specified in the function. SELECT REPLICATE( ' https://blog.sqlauthority.com/ ' , 9 ) This repeats the string https://blog.sqlauthority.com/ to 9 times in…