FORVO.com - All the words in the world, pronounced

FORVO.com - All the words in the world, pronounced

T-SQL - DBCC SHRINKDATABASE

The following command uses to shrink data and log files in a database and releasing unallocated spaces:

DBCC SHRINKDATABASE
( database_name | database_id | 0
   [ , target_percent ]
   [ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]

target_percent
   this parameter is the percentage of free (unallocated) space which you want left in the database file.

NOTRUNCATE
   Just relocate allocated spaces from end of the file to unallocated spaces in front of the file without shrinking and resizing the file.

TRUNCATEONLY
   it causes to release all free space at the end of the file.

WITH NO_INFOMSGS
   This option suppresses all informational messages.

Notes:

• Database can not be smaller than initial size which you mentioned when you created the database.
• DBCC SHRINKDATABASE without either the NOTRUNCATE option or the TRUNCATEONLY, runs first with NOTRUNCATE followed by running with TRUNCATEONLY.
• During backup you can not shrink a database and during shrinking you can not back up a database.
• Most effective time for shrinking a database is after an operation that creates lots of unused space like TRUNCATE or DROP TABLE

Disadvantage of shrinking a database is about increasing fragmentation.

Examples:
-- It shrinks and keeps 15 percent free space in the database
DBCC SHRINKDATABASE (myDataBase, 15);
GO

-- The following command, shrinks the database to the last allocated extent.
DBCC SHRINKDATABASE (myDataBase, TRUNCATEONLY)
GO

To shrink a database using SSMS, you can right click on database name and choose TASKS from context menu then select SHRINK and point on Database.
            Right click
Database -----------> TASKS -> SHRINK -> Database

T-SQL - DBCC SHRINKDATABASE

T-SQL - TRUNCATE and IDENTITY

If you want to delete all records in a table, the fastest way is through TRUNCATE. It will deallocate data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. but "DELETE FROM tablename" removes records one by one.

Syntax: TRUNCATE TABLE tablename

• When the table referenced by a FOREIGN KEY constraint, TRUNCATE TABLE is not applicable; instead, use DELETE statement without a WHERE clause.
• TRUNCATE TABLE doesn't do log, then it cannot activate a trigger.
• TRUNCATE TABLE will reset IDENTITY value to its base value then if you want to have another value for your IDENTITY enabled column, you need to call DBCC CHECIDENT

T-SQL - TRUNCATE and IDENTITY

T-SQL How to drop connections and detach a database?

sp_detach_db system stored procedure do the detaching job on a database from server, also It will run UPDATE STATISTICS on all tables before detaching.

Note: To be able to execute sp_detach_db you need to have right permission and only members of the sysadmin role can execute it.

sp_detach_db
   [@dbname =] 'dbname'
   [, [@skipchecks =] 'skipchecks']

[@dbname =] 'dbname'
It's the name of the database which you want to detach it, it's default value is NULL.
[@skipchecks =] 'skipchecks'
Default value is NULL.
If true, UPDATE STATISTICS will be skipped.
If false, UPDATE STATISTICS will run.
skipchecks data type is nvarchar(10)

If you want to move your database to a read onlu media, this option is useful

0 returns If the procedure executes with success
1 returns If the procedure encounter with error (failure)

An important matter is that, if there is a connection to database, you can't detach it, the following code will drop connections and the detach your database from server:

USE master
GO
ALTER DATABASE myDbName
   SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC sp_detach_db @dbname = 'myDbName', @skipchecks = 'true'
GO

T-SQL How to drop connections and detach a database?

T-SQL - DBCC CHECKIDENT

Set a column in a table as IDENTITY will guarantee to have unique value in that column which increment by inserting new records, then such a column is a good candidate to be PRIMARY KEY., but the question is, how to reseed the value or find what's the latest value now?

A table with a column which is set to IDENTITY can take advantage of DBCC CHECKIDENT.
The syntax for this command is as following:

DBCC CHECKIDENT ('table_name'[,{NORESEED |{RESEED[,new_reseed_value]}}])

• NORESEED returns back the current identity value for the column.
• RESEED with a value which comes after it with a comma, sets the column to a specific value and increment for the next inserted rows with predefined IDENTITY increment.

Example 1:
DBCC CHECKIDENT ( '[FBtst].[dbo].[Product]', NORESEED )

Output message:
Checking identity information: current identity value '101', current column value '101'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Example 2:
DBCC CHECKIDENT ( '[FBtst].[dbo].[Product]', RESEED, 200 )

Output message:
Checking identity information: current identity value '101', current column value '200'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Find more here:
http://technet.microsoft.com/en-us/library/ms176057.aspx

T-SQL - DBCC CHECKIDENT

100 meters long Photo - We are all gonna die!

This image is 100 meters long (100m x 78 cm)
http://www.simonhoegsberg.com/we_are_all_gonna_die/slider.html

178 people, 20 days, Berlin, Summer 2007

By Simon Hogsberg
http://www.simonhoegsberg.com/

Thanks Farzad (My brother) to send it to me.

100 meters long Photo - We are all gonna die!

OpenOffice.org 3.0.1

Home page: http://www.openoffice.org/index.html
Download: http://download.openoffice.org/
Extensions: http://extensions.services.openoffice.org/
Download charts: http://marketing.openoffice.org/marketing_bouncer.html

Extensions by Application

• OpenOffice.org
• Writer
• Base
• Calc
• Chart
• Draw
• Impress
• Math

OpenOffice.org 3.0.1

How to open a database connection to Microsoft SQL Server 2008 using the Microsoft Visual Studio 2005 design tools

When you try to open a connection with SQL Server 2008 through sever explorer in Visual Studio 2005 you will encounter with this error:
"This server version is not supported. Only servers up to Microsoft SQL Server 2005 are supported"

To fix it, you need to download and run VS80sp1-KB954961-X86-INTL.exe on your system:
Microsoft Visual Studio 2005 Service Pack 1 Update for Microsoft SQL Server 2008 Support
http://www.microsoft.com/downloads/details.aspx?FamilyID=e1109aef-1aa2-408d-aa0f-9df094f993bf&displaylang=en

To get some more info around it, take a look at the following page:
The Visual Studio 2005 CTP lets developers use Visual Studio 2005 together with SQL Server 2008
http://support.microsoft.com/default.aspx/kb/942246

How to open a database connection to Microsoft SQL Server 2008 using the Microsoft Visual Studio 2005 design tools

T-SQL - Add a "NOT NULL" new column to table which has rows of data

When you want to add a new column to an existing table that has rows, in each row it expect a value for the new column, and the default value is NULL. If you try to add a new column which is NOT NULL, you will encounter with error.

The easiest solution is to alter the table and define the column to allow NULLs, then add in the default data values using the UPDATE T-SQL command, and next, alter the column to NOT NULL.

ALTER TABLE AdventureWorksLT.SalesLT.Customer
ADD temp int NULL

GO

UPDATE AdventureWorksLT.SalesLT.Customer
SET temp = 0

GO

ALTER TABLE AdventureWorksLT.SalesLT.Customer
ALTER COLUMN temp int NOT NULL

GO

T-SQL - Add a "NOT NULL" new column to table which has rows of data

T-SQL - Random password generator

After creating a database in SQL server I created an
Application Role: DBname->Security->Roles->Application Roles

Then when I selected to create the script of that Application Role
in a new query Editor window, I found a part of code to generate a
random password, with some changes, I decided to write it here. To
run it, you can copy paste the code as a query in query editor window
of SSMS and execute the line of codes to see the output which is a
32 character length random generated password.

DECLARE @iCntr AS int
DECLARE @rndmPwd AS nvarchar(32)
DECLARE @rndm AS float

SET @iCntr = 0
SET @rndmPwd = N''

-- @@CPU_BUSY Shows the No. of busy CPU milliseconds
-- since the SQL Server instance was last started.
--
-- @@IDLE Displays the total idle time of the SQL
-- Server instance in milliseconds, since the
-- instance was last

SET @rndm =
   rand(
   ( @@CPU_BUSY % 100 ) +
   ( (@@IDLE % 100) * 100 ) +
   ( DATEPART(ss, GETDATE()) * 10000 ) +
   (
      (cast(DATEPART(ms,GETDATE()) as int) % 100) *
      1000000
   )
   )

WHILE @iCntr < 32
BEGIN
   SET @rndmPwd =
   @rndmPwd +
   char( ( cast( (@rndm * 83) as int ) + 43 ) )

   SET @iCntr = @iCntr + 1
   SET @rndm = rand()
END
PRINT @rndmPwd

T-SQL - Random password generator