Geeks With Blogs
Nagendra Prasad
In most of the DBs in the server will have more space, when the backup is used to store it in the development environment or testing environment, it might not have much data, but it will occupy more space physically. To reduce the database size, then there is a way to reduce it. Shrink the Transactional Log. There are two ways of shrinking the Transaction ......

Basically, we need two files to restore the database. They are .mdf and .ldf files. There is another way to restore the database, if we have the database backup file(.bak). But we can restore using one file(.mdf file) as well. Here's how it will be restored using .mdf file. -- Restore Database using MDF File alone EXEC sp_attach_single_file_db @dbname ......

To find a position of particular string/Char in the field of table, it is done using patindex. It might be well known for most of them. But what i found in that may be known to only few people. I want to share with everyone. Table: tblNames SELECT Name FROM tblNames Name Nagendra John Edward 3 row(s) affected. Example 1: To find Char or String, here ......

Lets see how to export SQL Server data into Excel. First we have to enable the Ad Hoc Distributed Queries option. Enable Ad Hoc Distributed Queries EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO After enabling it, we can proceed further to write the query for exporting ......

If you have two queries and you have to execute one after another with some amount of delay, then that is possible in SQL Server. Lets say, your first query fills up the table with some records. Lets assume that, you will get some responses based on this records which will fill other table. Now your second query should run based on the responses table. ......

If you want to get the n number of rows dynamically, then we need use variable. It can be achieved by two different methods. Method 1: DECLARE @MyCount VARCHAR(5) DECLARE @sql NVARCHAR(200) -- note nvarchar datatype should be used, since it is for dynamic query SELECT @MyCount =2 SELECT @sql = 'SELECT TOP ' + @MyCount + ' * FROM TBLNAMES' EXEC sp_executesql ......

Lets see how to copy an existing table to new table in SQL Server. There are two options. They are Copy only the structure of an existing table into new table Copy only the structure with data of an existing table into new table Copy only the structure of an existing table into new table: SELECT * INTO tblNew FROM tblOld WHERE 1=2 The above query will ......

As everyone knows, DATE can be formatted in different ways and the usual way will be using CONVERT Syntax: SELECT CONVERT(VARCHAR(30), GETDATE(), 106) Where as 106 can be replaced by 101, 110, etc according to the requirements. Custom Format: If we need custom date format(19-March-2009), which is not a pre-defined format in CONVERT, then consider the ......

Formatting numbers to add leading zeros can be done in SQL Server. It is just simple. Lets create a new table and see how it works: CREATE TABLE Numbers(Num INT); Table Created. Lets insert few values and see: INSERT Numbers VALUES('12'); INSERT Numbers VALUES('112'); INSERT Numbers VALUES('12'); INSERT Numbers VALUES('122'); INSERT Numbers VALUES('122'); ......

I have to concatenate first name with last name. I got too many NULL values which it should not. Then I found the reason behind it. Data in TblNames : FIRST_NAME LAST_NAME Edward Jones Jason NULL John Smith Robert NULL 4 row(s) affected. Expected Result: NAME Edward Jones Jason John Smith Robert 4 row(s) affected. Before finding the solution: SELECT ......

Copyright © nagendraprasad | Powered by: GeeksWithBlogs.net