Microsoft SQL Server Management Studio 2016

I have installed Microsoft SQL Server 2016 in my system and try to open SQL Server Management studio 2016, surprise the management studio is not installed by default with SQL Server 2016.I have googled it and found the answer is Microsoft Split the SQL server and Management studio. Good thing is latest SQL Server management Studio supports SQL server from 2008 to higher version. So let us the download the latest version from below link.

SQL Server Management Studio

Advertisements

Sql server Usefull link

Below link is very full for the sql server for instance reference  from beginner to experts.

Link

Create a unique key that is case sensitive

To apply case sensitive on particular column we modify the column below way.

ALTER TABLE mytable

ALTER COLUMN mycolumn VARCHAR(10)

COLLATE SQL_Latin1_General_CP1_CS_AS

After create constraint on column

ALTER TABLE MYTABLE

ADD CONSTRAINT UK_MYTABLE_MYUNIQUEKEY UNIQUE NONCLUSTERED (MYCOL)

SQL best practices for better performance

In general while developing the queries the required result will retrieve from query.  But the performance will be down after some days in production server due to data .In the scenario we have optimize the queries. So  while developing the sql queries, stored procedures we follow some techniques for better performance. Below links for some of the practices sql queries.

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/10/30/my-stored-procedure-best-practices-checklist.aspx

http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

Get All Tables and Storeprocedure of the Database in Sqlserver

Sometimes we have to   collect all tables or stored procedures of your database with schema name.Use the below script to get all the tables or stored procedures.

 To Get all Tables Below script

 SELECT ”+SCHEMA_NAME(schema_id)+’.’+name+” AS AllTable

FROM sys.tables

 To Get All stored procedures

 SELECT SCHEMA_NAME(schema_id)+’.’+name+” as AllProcedures

FROM sys.procedures WHERE [type] = ‘P’

AND is_ms_shipped = 0AND [name] NOT LIKE ‘sp[_]%diagram%’

Location of Data File Using T-SQL

Some times we need know the location of Data file of databases of sqlserver.Below script display the all the .mdf and .ldf files of sqlserver.

select Name as DataBasename,Physical_name as Location From master.sys.master_files