October 30, 2012

How to install MongoDB as a Windows Service

At the first date I heard about NoSQL DBMS, I felt insane. Then I got an eager to learn. But I didn’t pay any attention to learn. Later I got to know that MongoDB is a scalable one. Then I got a chance to follow free MongoDB course. Although I registered to both Developer and DBA courses I had no time to watch developer tutorials. But I finished DBA 1st Week. In that video tutorial they explain how to install MongoDB on Windows as well as on UNIX. But when we are using Mongo in Windows Development Environment it’s a headache to start service again and again. Because of that I wanted to install MongoDB as a Windows Service.

In that video tutorial they explain how to just run MongoDB on Windows Environment. First of all you should have a data directory. For that you should create two folders in the drive that you are planing to install MongoDB. Lets assume that we are going to install MongoDB on D: Drive.

Then you should create two directories(Folders) in D: drive


  • D:\data


  • D:\data\db


You can manually change data path but for a beginner its better to use default path. And if you are trying to install it as Service you need to create a log folder.


  • D:\log


Then open a command prompt and go to the MongoDB bin. I assume you have pasted it on D:\ drive. your directory path will look like this D:\mongodb-win32-x86_64-2.2.0\bin.

After that you should run mongod.exe to install MongoDB as a windows service.

mongod.exe --install --logpath D:\log


When you have type and enter this command on CMD. It will create a Service for MongoDB. You can start MongoDB service with below command.

net start MongoDB


Enjoy Mongo!!!!

October 24, 2012

Microsoft SQL Server පටන් ගමු!!

මම කලින් දාපු පෝස්ට් වල තියන SQL ස්ටේට්මන්ට් රන් කරලා පුරුදු වෙලා බලන්න විදිහක් මට පෝස්ට් කරන්න බැරි වුනා. ඇත්තටම සරල SQL ස්ටේට්මන්ට්ස් හැම එකක්ම වගේ ඕනම ඩේටාබේස් මැනෙජ්මන්ට් සිස්ටම් එකක රන් කරලා බලන්න පුලුවන් (Oracle DB, SQL Server, Access, MySQL). එත් මම පාවිච්චි කරන්නේ SQL Server ඉතින් මම හිතුවා SQL Server ඉගෙන ගන්න කැමති අයට ඒකට අවශ්‍ය දේවල් ඩව්න්ලෝඩ් කරගන්න තැන් පෝස්ට් කරන්න.

http://www.microsoft.com/en-us/download/details.aspx?id=29062

ඉහත දීලා තියන ලින්ක් එකෙන් ගිහින් ENU\x64\SQLEXPRWT_x64_ENU.exe හරි ENU\x86\SQLEXPRWT_x86_ENU.exe ඩව්න්ලෝඩ් කරගෙන ඉන්ස්ටෝල් කරගන්න. ඇත්තටම ඉන්ස්ටෝල් කරන එක එච්චර අමාරු නෑ. ඒවගේමයි මේ එඩිෂන් එක ඉගෙන ගන්න අයටම වෙන් කරපු එකක් ඒක හන්දා ලයිසන් ප්‍රශ්නයකුත් නෑ. තමුන්ගේ මෙහෙයුම් පද්ධතියට ගැලපෙන විදිහට 64බිට් හරි 32 බිට් හරි ඩව්න්ලෝඩ් කරගෙන ඉන්ස්ටෝල් කරගන්න. මේකේ ඇත්තටම වැදගත්ම දේ SQL Server Management Studio Express එක SSMS එකෙන් ලේසියෙන්ම අවශ්‍ය Queries ලියන්න පුලුවන්.

ඊට පස්සේ SQL Server ඉගෙන ගන්නවානම් ගොඩක් වැදගත් වන දෙයක් තමා සාම්පල් ඩේටාබේස් එක. ඇත්තටම AdventureWorks කියලා ගොඩක් ප්‍රසිද්ද සාම්පල් ඩේටාබේස් එකක් තියනවා. ඒක හැමෝම පාවිච්චි කරනවා. ඉතින් ඒකත් ඩව්න්ලෝඩ් කරගෙන SQL Server එකට ඇටැජ් කරගත්ත නම් SQL Server ඉගෙන ගන්න ගොඩක් ලේසි.

http://msftdbprodsamples.codeplex.com/releases/view/55330

ගිහින් පුලුවන් ඒ සාම්පල් ඩේටාබෙස් එක ඩව්න්ලෝඩ් කරගන්න. AdventureWorks2012 Data File කියන එක ඩව්න්ලෝඩ් කරගත්තා නම් ලේසි. නැත්නම් AdventureWorks2012-Full Database Backup ඩව්න්ලෝඩ් කරගෙන රිස්ටෝර් කරන්නත් පුලුවන්

SSLUG October 2012

Although it was a rainy day so many attendees were there to participate SQL Server Universe Monthly User Group Meeting. At the beginning Dinesh’s TV interview was on the screen until the meeting begins. And then we had 3 sessions one on Data migration to Dynamics, one on execution plan myths, and last one was about Paging Results in SQL Server.

_DSC5732

If you never knew about SQLServerUniverse User Group Meeting Please go to the site http://sqlserveruniverse.com/ and follow on twitter, linkedin and facebook. Don’t forget to register on the site. Usually it happens on 3rd Wednesday on every month.

Three Speeketeers

SELECT–SQL

SELECT ස්ටේට්මන්ට් එක ඩිවලොපර්ලා ඇතුලු හැමෝටම වැදගත් වෙන ස්ටේට්මන්ට් එකක්. වැඩියෙන්ම භාවිතා වෙන ස්ටේට්මන්ට් එක බොහෝ වෙලාවට මේක වෙන්නත් පුලුවන්. අපට අවශ්‍ය විදිහට ඩේටාබේස් එකේ ටේබල් කිහිපයකින්ම දත්ත ගැනීමට මේ ස්ටේට්මන්ට් වලට පුලුවන්. ඒවගේම ගණිත කර්ම කරන්නත් පුලුවන්.

උදාහරණයන් සදහා මම පහල තියන tblMarks ටේබල් එක දිගටම පාවිච්චි කරනවා.

ID Name Marks
1 Dedunu 67
2 Satheeq 87
3 Dinesh 98
4 Shamil 67
5 Hasitha 87
6 Abinandana 76

අපි හිතමු අපිට අවශ්‍යයි කියලා තියන දත්ත ඔක්කොම ටේබල් ගන්න අවශ්‍යයි කියලා. අපි පාවිච්චි කරන්න ඕන ස්ටේට්මන්ට් එක මේ වගේ එකක්.

SELECT * FROM dbo.tblMarks

මේ ස්ටේට්මන්ට් එක රන් කලොත් අපිට ලැබෙන්නේ මුලින්ම තියන වගුව වගේම ප්‍රතිඵලයක්. අපි හිතමු දැන් අපිට අවශ්‍ය වෙලා තියනවා කියලා නම් ලැයිස්තුවක් ඩේටාබේස් එකෙන් අරගන්න. එතකොට අපි රන් කරන්න ඕනේ පහත විදිහේ ස්ටේට්මන්ට් එකක්.

SELECT Name FROM dbo.tblMarks

නම් සමග ලකුනුත් අවශ්‍ය නම්  පාවිච්චි කරන්න ඕනේ මේ පහල තියන ස්ටේට්මන්ට් එක.

SELECT Name, Marks FROM dbo.tblMarks























NameMarks
Dedunu67
Satheeq87
Dinesh98
Shamil67
Hasitha87
Abinandana76

ලැබෙන ප්‍රතිඵලය ඉහත වගේ එකක්.  අපි හිතමු කාටහරි අවශ්‍ය වෙනවා නම් ලැයිතුව අකාරාදි පිලිවෙලට අරගන්න. එතකොට රන් කරන්න අවශ්‍ය පහත ස්ටේට්මන්ට් එක.

SELECT Name 
FROM dbo.tblMarks
ORDER BY Name ASC

ස්ටේට්මන්ට් අතර අලුතෙන් ලයින් කොච්චර තිබුනත් ගැටලුවක් වෙන්නේ නැ. පහත ආකාරයට තමයි ඉහත තියන ස්ටේට්මන්ට් එකේ රිසාල්ට් සෙට් එක ලැබෙන්නේ.

















Name
Abinandana
Dedunu
Dinesh
Hasitha
Satheeq
Shamil

අපි හිතමු ඒවගේම අපිට අවශ්‍ය වෙනවා ලකුනු වැඩියෙන්ම ගත්ත කෙනාගේ නමත් ලකුනුත් අරගන්න. ඒ සදහා අපිට පහත තියන ස්ටේට්මන්ට් එක පාවිච්චි කරන්න පුලුවන්.

SELECT Name, Marks
FROM dbo.tblMarks
ORDER BY Marks DESC

එතකොට ඇත්තටම ලැබෙන්නේ පහත ආකාරයේ රිසාල්ට් සෙට් එකක්.
























NameMarks
Dinesh98
Satheeq87
Hasitha87
Abinandana76
Dedunu67
Shamil67

SQL වල කොටස්

SQL එක එක කාර්යන් වලට වෙන්කරන ලද කොටස් තියනවා. ඇත්තටම එකම එක භාෂාවක් වුනාට එක එක කොටස් තියනවා එක එක දේවල් වලට.

DML - Data Manipulation Language

වැඩියෙන්ම SQL වල භාවිතා වෙන්නේ මම හිතන විදිහට DML Operation වෙන්න ඕන. මොකද Database එකක තියන දත්ත Database එකෙන් අරගන්න, Database එකේ තියන දත්ත වෙනස් කරන්න ඒවගේම දත්ත අලුතෙන් එකතු කරන්න තියන දත්ත මකන්න කියන හැමදේටම SQL වලදි අපි පාවිච්චි කරන්නේ DML.

උදා:-

  • SELECT
  • DELETE
  • INSERT
  • UPDATE

DDL – Data Definition Language

Database වල තියන දත්ත අපි ගබඩා කරලා තියන්නේ ටේබල් වල. ඒවගේ ටේබල්, ඉන්ඩෙක්, වීවිස් වගේ දේවල් ඩේටාබේස් එකේ හදන්න වෙනස් කරන්න මකන්න වගේ දේවල් වලට DDL භාවිතා කරනවා.

උදා:-

  • CREATE
  • DROP
  • ALTER

DCL – Data Control Language

DCL භාවිතා කරලා අපි කරන්නේ දත්ත පාවිච්චි කරන්න දත්ත මකන්න වෙනස් කරන්න එක එක පරිශීලකයන්ට අවශ්‍ය බලය ලබා දෙන්න. එවගේම දැනට තියන අයිතීන් ඉවත් කරන්න වෙනස් කරන්නත් මේ DCL භාවිතා කරනවා.

උදා:-

  • GRANT
  • REVOKE

SQL ගැන හැදින්වීමක්!

SQL යනු දත්ත සම්බන්ධයෙන් ඇති කාර්යන් ඉටු කරගැනීමට භාවිතා කරන භාෂාවකි. 5th Generation ලැන්ග්වෙජ් එකක් විදිහට SQL සලකනවා. ඒවගේම SQL කියන එක “SEQUEL” කියලා තමයි ගොඩක් අය කියන්නේ දැන් SQL කියන්නේ Structured Query Language වුනාට මේ ලැන්ග්වෙජ් එකට ඉස්සර Structured English Query Language කියලා තමා කියලා තියෙන්නේ. පස්සේ කාලෙක මේක SQL කරලා තියනවා. ඇත්තටම SQL එක්ක වැඩ කරන්න ලේසි.

SQL වලට Standard එකක් තිබුනත් සමාගම් තමුන්ගේ නිෂ්පාදන වලදි අලුත් අංග එකතු කරලා වෙනම භාෂාවල් විදිහට නම් කරනවා. Oracle පාවිච්චි කරන්නේ P-SQL එවගේම SQL Server පාවිච්චි කරන්නේ T-SQL. කොහොම වුනත් ගොඩක් වෙලාවට මූලික දේවල් එක වගේමයි.

October 22, 2012

Programming Microsoft SQL server 2012

catBefore few weeks I got a book from Oreilly Blogger Review program to review a book. I took enough time to read this book. This book covers most of the new trends and features in SQL Server 2012 with good explanation.

I was addicted to first chapter to get to know about the SQL Server Data Tools. Because previously I was studying development things like C# and Visual Studio because of that I was not be able to find out about SQL Server Development Tools. With SQL Server 2012 SQL Server Data Tools were introduced. In first chapter completely describes about SSDT from the point of Developers and Dev DBA’s.

All over the book I didn’t feel like reading a tech book. It is well organized and I felt that I read a story book. But this book is not good for absolute beginners who is looking for SQL Server Development. And this book also balanced one, It doesn’t cover only XML or CLR. Its covering most of the topics with useful things. This is like a essence for database developers who uses SQL Server 2012 or who is planning to use SQL Server 2012.

Each chapter had lot of things to learn. Actually this is a good book. Read it if you are a dev DBA in SQL Server 2012!!! Programming Microsoft SQL Server 2012 was a pretty interesting book for me.

October 17, 2012

How to compare data in two tables?

In the beginning of this week I got a task from Susantha to modify a stored procedure. After the modification I had to test to compare the result sets before modification and after modification. That procedure only returns a data table. So I had so many test data on another table. Then I wrote a script to go through the script one by one. Actually I wanted to check whether those results are identical. I mean not the table structure. I wanted to perform a data comparison.

DECLARE @COUNTN INT;
DECLARE @COUNTO INT;
DECLARE @COUNTU INT;

CREATE TABLE #TEMPN
(
--Columns Here
)

CREATE TABLE #TEMPO
(
--Columns Here
)

CREATE TABLE #TEMPU
(
--Columns Here
)

INSERT INTO #TEMPN
--Select From new table

INSERT INTO #TEMPO
--Select From old table

SELECT @COUNTU = COUNT(1)
FROM #TEMPU
SELECT @COUNTN = COUNT(1)
FROM #TEMPN
SELECT @COUNTO = COUNT(1)
FROM #TEMPO

IF @COUNTN <> @COUNTO
BEGIN
SELECT
*
FROM #TEMPN
SELECT *
FROM #TEMPO
PRINT 'ERROR'
END

IF
@COUNTN <> @COUNTU
BEGIN
SELECT
*
FROM #TEMPN
SELECT *
FROM #TEMPO
PRINT 'ERROR'
END

IF
@COUNTU <> @COUNTO
BEGIN
SELECT
*
FROM #TEMPN
SELECT *
FROM #TEMPO
PRINT 'ERROR'
END

DROP TABLE
#TEMPN
DROP TABLE #TEMPO
DROP TABLE #TEMPU


This will check whether your data in two tables are identical or not. if there’s any mismatch it will print error and show the both result sets. And there’s another issue on this script if one table returns NULL and the other table returns empty table it will take it as matched. Then I corrected it again.

DECLARE @COUNTN INT;
DECLARE @COUNTO INT;
DECLARE @COUNTU INT;

CREATE TABLE #TEMPN
(
--Columns Here
)

CREATE TABLE #TEMPO
(
--Columns Here
)

CREATE TABLE #TEMPU
(
--Columns Here
)

INSERT INTO #TEMPN
--Select From new table

INSERT INTO #TEMPO
--Select From old table

SELECT @COUNTU = COUNT(1)
FROM #TEMPU
SELECT @COUNTN = COUNT(1)
FROM #TEMPN
SELECT @COUNTO = COUNT(1)
FROM #TEMPO

IF @COUNTN <> @COUNTO
BEGIN
SELECT
*
FROM #TEMPN
SELECT *
FROM #TEMPO
PRINT 'ERROR'
END

IF
@COUNTN <> @COUNTU
BEGIN
SELECT
*
FROM #TEMPN
SELECT *
FROM #TEMPO
PRINT 'ERROR'
END

IF
@COUNTU <> @COUNTO
BEGIN
SELECT
*
FROM #TEMPN
SELECT *
FROM #TEMPO
PRINT 'ERROR'
END

--New Code Block here

IF EXISTS(#TEMPO)
BEGIN
IF
NOT EXISTS(#TEMPN)
BEGIN
SELECT
*
FROM #TEMPN
SELECT *
FROM #TEMPO
PRINT 'ERROR'
END
END


IF
EXISTS(#TEMPN)
BEGIN
IF
NOT EXISTS(#TEMPO)
BEGIN
SELECT
*
FROM #TEMPN
SELECT *
FROM #TEMPO
PRINT 'ERROR'
END
END



DROP TABLE
#TEMPN
DROP TABLE #TEMPO
DROP TABLE #TEMPU

October 12, 2012

Easy way to analyse results

I have seen in many video tutorials they have different tabs for Editor, Results, Messages and Execution Plans. And they are streched over complete screen. Most of the times need to resize the result area to see our results. Actually its annoying me. Then I noticed in on of Paul Randal’s video he uses Separate tabs for Results editor and messages in SQL Server Management Studio (SSMS). Then I wanted to find how to get those tabs to my SSMS (Often I call it SMSS don’t know why Winking smile).

1. Open you SQL Server Management Studio (SSMS)

2. Go to Tools –> Option

3. Check “Display results in a separate tab” and “Switch to results tab after the query executes” both.

image

4. Click ok and enjoy Results like this.

image

If you don’t like to use it always you can use this option to single script by click on Query option on tool bar and follow 3rd step.

image

SQL Server is not releasing memory?

I got few questions to answer quickly in last week just like a test. Then I suddenly answered then after that I tried to find correct answers to them. I found answers for most of them. And one of them was there as it was.

A Windows server has 32 GB of memory and dedicated for SQL Server database. Every time you start the server the memory utilization of SQL Server gradually increases, until it takes almost all the memory and remains there for days even when there is no database activity. What will you do ?

Actually I answered

We can set maximum memory limit. Some how gradually SQL Server uses available memory.

I knew that SQL Server uses and usually doesn’t release memory, although he don’t need to use it. But I didn’t knew why is that. Then I got to know that Not releasing memory is a feature of SQL Server. On servers we don’t run our day to day applications and most of the times we have allocated separate box for SQL Server. Then nobody will use that memory. If nobody uses that why should we release and allocate again and again. When we are discussing about this problem our production server had reached its maximum memory too Winking smile. And this is not our production servers Smile.

sql server memory leak

Preethi told that this happens often. People who don’t know about SQL Server behavior when saw “task manager” like above they are suggesting to double the memory of server. Then until it takes few days they are happy. Again they are having same issue. Sad smile. Actually it’s not a problem with SQL Server.

If you really want to release that memory you can easily restart SQL Server. Then it begins everything from the beginning. Other wise you can execute those commands.

But even in the TechNet they haven’t mentioned that it is recommended running those commands against production servers. Somehow as I think theirs no need to flush memory manually. Because if you are using SQL Server on your laptop, every time that you restart your laptop it will flush again and again. In production servers we don’t need to flush it manually. Let SQL Server to use that memory as he want.

If you are using one box to install SQL Server and Application both you can set maximum memory limit to SQL Server. Then it will not exceed that limit. But I found this thread from SQLServerCentral.

http://www.sqlservercentral.com/Forums/Topic982342-1550-1.aspx

In that thread he is saying that SQL Server exceeds that maximum memory limit. But technically it should not happen. And I haven’t tested it also. But there may be a reason for that too. If you are setting maximum memory limit you should restart SQL Server. If you don’t restart it, it uses previous memory limit. Somehow below link says another thing. I have to learn about it further. Smile

Yup. Perfectly normal.
Max server memory is the max size of the buffer pool, the memory area that contains the data cache, plan cache and a whole bunch of other caches. SQL also uses memory outside the buffer pool for things like backup buffers, thread stack, linked server drivers, CLR and a few other things. This is outside of the buffer pool, so it's not part of 'max server memory'
On 32-bit SQL, that's referred to as MemToLeave (memory to leave unallocated when assigning the buffer pool). On 64 bit that term has no meaning.

http://www.sqlservercentral.com/Forums/Topic1197388-1550-1.aspx

Picture: http://piglings.blogspot.com/2009/10/sql-server-2008-memory-leak.html

October 04, 2012

Be careful if you are using CTE!

Previously I wrote a post about CTE (Common Table Expressions). And Common Table Expressions are valid for a single statement only. But single statement can use many CTEs. And statement also should be within same batch.

USE AdventureWorks2012
GO

--Defining CTE
WITH ctePerson
AS
(
SELECT *
FROM Person.Person
)
--First Statement will run
SELECT FirstName FROM ctePerson
--Second statement will occur an error
SELECT FirstName FROM ctePerson

And in my previous post I have mentioned about many CTEs for one statement. And there is another important thing that is be careful when you are naming CTEs. Take a look on below example.


USE tempdb
GO

--Creating two tables
CREATE TABLE cteSampletbl1
(
id int,
name varchar(50)
)
GO

CREATE TABLE
cteSampletbl2
(
id int,
name varchar(50)
)
GO

--Populating tables
INSERT INTO cteSampletbl1 VALUES
(1,'Satheeq'),
(
2,'Preethi'),
(
3,'Dinesh'),
(
4,'Angelo')
GO

INSERT INTO
cteSampletbl2 VALUES
(1,'Shamil'),
(
2,'Hasitha'),
(
3,'Abhinandana'),
(
4,'Susantha')
GO

/*
This CTE is defined with existing table name without any error
*/
WITH cteSampletbl2
AS(
SELECT * FROM cteSampletbl1
)
--This statement uses CTE
SELECT * FROM cteSampletbl2
--This statement uses existing table
SELECT * FROM cteSampletbl2

--Dropping tables
DROP TABLE cteSampletbl1
DROP TABLE cteSampletbl2
GO

Below example it lets us to create a CTE with a name of existing table without any warnings or errors. And it gives priority to CTE in first statement. Then it uses existing table in second statement. So it would be a best practice to use a separate prefix for CTEs. Then take a look into next sample which is taken from Dinesh’s presentation for SQL Server Universe Group.


USE tempdb
GO

--Creating two tables
CREATE TABLE cteSampletbl1
(
id int,
name varchar(50)
)
GO

--Populating tables
INSERT INTO cteSampletbl1 VALUES
(1,'Satheeq'),
(
2,'Preethi'),
(
3,'Dinesh'),
(
4,'Angelo')
GO

--This CTE got error
WITH cteSampletbl1
AS(
--Same name could not be used as name and in CTE
SELECT * FROM cteSampletbl1
)
SELECT * FROM cteSampletbl1

--This CTE don’t have any error
WITH cteSampletbl1
AS(
--full name of object should provide
SELECT * FROM dbo.cteSampletbl1
)
SELECT * FROM cteSampletbl1

--Dropping tables
DROP TABLE cteSampletbl1
GO

You can’t use same table name as the name of the CTE and inside the CTE.

How varchar data is stored?

In SQL Server we use varchar, varbinary to save disk space in our tables. But its not saving data always as it sounds. When the data stores it takes few extra bytes to describe the data length. Because of that there is no gain using varchar(2). If you use varchar(2) it will take 4 or 3 bytes to store data. varchar(8000) or below should have 2Byte offset to store the variability of character count. If you are using varchar(max) is different. It should have 4 Bytes offset. But I’m still looking for that to confirm it takes 4 Bytes. And it is stored in Variable Column Offset Array.

062911_1840_Themysteryo1

Picture: http://www.sqlservercentral.com/blogs/aschenbrenner/2011/06/29/the-mystery-of-the-null-bitmap-mask/

October 03, 2012

Are you going to save space using Bit Data Type? - Part 2

Yesterday I wrote a post with same title. In that post I mentioned using char(1) is also same like using one bit column in table. But it is from the perspective of storage space. But using numbers is less burden to Database engine. It means using 1 and 0 is better than using ‘Y’ and ‘N’. Also in comparing data or in sorting data it is easier.


If you are storing numbers as a text its not a good thing although you don’t do any mathematical operation with that text. First thing it takes more space. It takes 1 byte for every character, while integers take very less. And second thing if you store them as string it will have extra cost to manipulate, compare, sort, whatever you are going to do with them.

SA Account

As I read after installing SQL Server on a System it is a best practice to disable SA account. Then I asked from our architect, and one of prod DBAs. Then I got to know Yes it is a good practice if you disable SA Account, because it is a well known account. And also we can rename that account then there is no point to disable it.


Somehow finally I got to know that nobody should you SA account.

October 02, 2012

Are you going to save space using Bit Data Type?

If you have Yes or No data to store in DB, what will you select as the data type? Most of the times you will select “Bit”. Because it only takes 1 bit to store data. And it is the best data type for this situation. But it takes more than bit in SQL Server. If you have only one bit column in your table it will allocate 7 more extra bits to store your bit data.

And if you have two bit typed fields in your table it will waste 6 more bytes. But if you are using 8 bit typed fields in your table, it will not waste any space. And if you have only one such a binary values column in your table. Otherwise it will take space same as char(1) or tinyint. Although its name is bit it is not small like bit always.

Data Type details from sphelp

I wanted to learn about data types. Then I went to technet some how most of the important things were there in technet documentations. But I found another way to get figures from SQL Server. We can have details of data types from “sphelp”. You just have to give your data type as a parameter like below.

EXEC sp_help int

Then I executed this to every data type except few system data types. And I listed figures on categories which was in technet.


Exact Numerics
































































Type NameLengthPrecisionScaleNullable
bigint8190Y
bit11NullY
decimal173838Y
int4100Y
money8194Y
numeric173838Y
smallint250Y
smallmoney4104Y
tinyint130Y

Approximate Numerics






















Type NameLengthPrecisionScaleNullable
float853NullY
real424NullY

Date and Time














































Type NameLengthPrecisionScaleNullable
date3100Y
datetime28277Y
datetime8233Y
datetimeoffset10347Y
smalldatetime4160Y
time5167Y

Binary Strings

 



























Type NameLengthPrecisionScaleNullable
binary80008000NullY
image162147483647NullY
varbinary80008000NullY

Character Strings




















Type NameLengthPrecision
char80008000
varchar80008000
text162147483647

Unicode Character Strings




















Type NameLengthPrecision
nchar80004000
nvarchar80004000
ntext161073741823

Other Data Types


































Type NameLengthPrecisionNullable
hierarchyid892892Y
sql_variant80160Y
timestamp88N
uniqueidentifier1616Y
xml-1-1Y

If I haven’t specified any attribute of those data types usually they have their default values. One special thing was there were only one data type which was not nullable. It is “timestamp”. And I was unable to take data of cursor and table from this method.

October 01, 2012

Did you tried to install SQL Server 2012 on Windows Server 2012?

Recently I tried to install SQL Server 2012 in Windows Server 2012 box. Unfortunately it was not easy to do that for me because setup generated few errors. And then I it displayed that .NET Framework 3.5 SP1 Feature was not able to activate. There were few reasons to occur that problem.

  • Windows Server 2012 was not activated.
  • Windows Server 2012 was unable to access internet (When it is activated).

So first I realized when I tried to manually activate this feature, that I have to activate my Windows Server 2012. Then I activated my Windows Server 2012. Then I again tried to activate .Net Framework 3.5. But unexpectedly again I got failed. Then I checked what was wrong with my computer and why this installation got failed although I had inserted the installation media.

Error was like this.

Troubleshooting101-01

Then I found in the internet that we have to show the path of the binaries to activate this feature.

1. Then I opened Server Manager and then Manage –> Add Roles and Features.

Troubleshooting101-02

2. I followed the wizard normally.

Troubleshooting101-03

Troubleshooting101-04

Troubleshooting101-05

Troubleshooting101-06

3. Then I selected .NET Framework 3.5 from features window.

Troubleshooting101-07

4. Then I got this dialog box.

Troubleshooting101-08

5. But be careful and don’t click “Install”. If you click Install it will look up for internet connection. If there is not internet connection it will fail your setup.  Click on “Specify an alternate source path”.

net3installsourcesrvmanager

6. And then give your installation media folder plus “\sources\sxs”. It may be a share or DVD no matter whatever it is. Then Click OK and Install. It will activate .NET Framework 3.5 on your Server 2012 installation.

I found those pictures from sqlcoffe.com. And I saw that he has also tried in this way and get failed because he hasn’t specify the path of installation media. But he has an alternate method. I’'ll share it here too.

He had opened a cmd window. and had typed this command.

dism /online /enable-feature /featurename:NetFx3 /source:d:\sources\sxs

Troubleshooting101-09

You can replace “d:\sourced\sxs\” with your path of installation media. Then I tried to install SQL Server 2012. Installation was successfully finished.

Pictures: http://www.sqlcoffee.com/Troubleshooting101.htm