DotNetCode

Hi All,

Here i have tried to install SSDT in Visual studio 2015 through online.But am not able to make it success.Everytime it has thrown below error at the some point, then i have tried various method to fix this issue but nothing get helped.

Finally i have found solution form the below link.Here you can download standalone installer for SSDT.After i tried this SSDT has installed Successfully.

https://docs.microsoft.com/en-us/previous-versions/mt186501(v=msdn.10)?redirectedfrom=MSDN

Direct Link to Download the file.


https://go.microsoft.com/fwlink/?linkid=832313&clcid=0x409

Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Advantage:

• Do not acquire locks for the initial population of data from a client.
• Provide a simple programming model.
• Enable you to include complex business logic in a single routine.
• Reduce round trips to the server.
• Can have a table structure of different cardinality.
• Are strongly typed.
• Enable the client to specify sort order and unique keys.
• Are cached like a temp table when used in a stored procedure. Starting with SQL Server 2012, table-valued parameters are also cached for parameterized queries.

If you have a datetime column in a WHERE clause, and you need to convert it or use a data function, try to push the function to the literal expression.

For the below two query , the First one take more query cost rather than the Second one,

SELECT OrderID FROM dbo.Orders WHERE DATEADD(day, 15, 
OrderDate) = '07/23/1996'



SELECT OrderID FROM Orders WHERE OrderDate = DATEADD(day, 

-15, '07/23/1996')

In below Figure you can check the query cost

Here below code, which describes to create Dynamic Pivot view from the Table.




Create table yourtable (itemID INT, part CHAR(1))



INSERT INTO yourtable VALUES(1,'A'),(1,'B'),(2,'A'),(2,'A'),(2,'A'),(3,'C')

DECLARE @colsSorted AS NVARCHAR(2000), @sql AS NVARCHAR(4000)

select @colsSorted = STUFF((select DISTINCT ', '
+ quotename( Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) ,']')
FROM yourtable
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
--Print @colsSorted

Set @sql=N' if object_id(''anewtable'',''U'') is not null drop table anewtable ; with mycte as (SELECT ItemID, '+ @colsSorted + ' FROM (
Select ItemID,Part, Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) as Cols

FROM yourtable
) src
PIVOT (Max(part) for Cols IN ('+ @colsSorted +')) pvt )
Select *   into aNewtable
from mycte;'
 --print @sql
 exec sp_executesql @sql;


 select * from aNewtable

 select * from yourtable

drop table yourtable








Happy coding!!!!!!!!!!!!!!!!!!

Non-Unicode	Unicode
(char, varchar, text)	(nchar, nvarchar, ntext)
Stores data in fixed or variable length	Same as non-Unicode
char: data is padded with blanks to fill the field size. For example, if a char(10) field contains 5 characters the system will pad it with 5 blanks	nchar: same as char
varchar: stores actual value and does not pad with blanks	nvarchar: same as varchar
requires 1 byte of storage	requires 2 bytes of storage
char and varchar: can store up to 8000 characters	nchar and nvarchar: can store up to 4000 characters
Best suited for US English: "One problem with data types that use 1 byte to encode each character is that the data type can only represent 256 different characters. This forces multiple encoding specifications (or code pages) for different alphabets such as European alphabets, which are relatively small. It is also impossible to handle systems such as the Japanese Kanji or Korean Hangul alphabets that have thousands of characters."1	Best suited for systems that need to support at least one foreign language: "The Unicode specification defines a single encoding scheme for most characters widely used in businesses around the world. All computers consistently translate the bit patterns in Unicode data into characters using the single Unicode specification. This ensures that the same bit pattern is always converted to the same character on all computers. Data can be freely transferred from one database or computer to another without concern that the receiving system will translate the bit patterns into characters incorrectly.

Important SQL Server Interview question

What is RDBMS?
What is Normalization?
What is De-normalization where is it used?
What are different normalization forms?
What is Stored Procedure?
What is Trigger?
What are different types of triggers?
What is View?
What is an Indexed View?
What is “Rollup” clause?
How to decide the sequence of fields in a compound index?
Is it possible to declare a clustured index on non-primary field?
What are the commands to compare two result sets in 2005?
What are the pre-requisits to create an indexed view?
What are the advantages and disadvantages of NO LOCK option in select statement?
Can we insert/update rows in system tables?
CTE for deleting duplicate rows from a table
What are 3 different ways to execute a dymnamic SQL?
How to sychronize data with foxpro DB from SQL 2005?
How to read excel data directly into SQL server 2005?
ANS:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Source\Addresses.xls',
'SELECT * FROM [Sheet1$]')
What are magic tables or what are inserted and deleted tables?
How to update multiple tables from a view?
What is the use of instead of trigger?
How to shrink DB files?
How to truncate transaction log?
What are DBCC commands?
How to reset value of identity column in a table?
How to create a linked server in SQL Server?
How to import data from MySQL db to SQL Server?
What are full text indexes?
What are advantages and disadvantages of Dynamic Query in a stored procedure?
How to get value of last inserted identity column?
What is the difference between a table type variable and temp table, which is better?
Can we update data in the parent table using view?

What is the need of re-indexing?
What is BCP?
What is Index?
What is a Linked Server?
What is Cursor?
What is Collation?
What is Difference between Function and Stored Procedure?
What is sub-query? Explain properties of sub-query?
What are different types of Join?
What are primary keys and foreign keys?
What is the difference between primary and unique key?
What is User Defined Functions? What kind of User‐Defined Functions can be created?
What is difference between DELETE & TRUNCATE commands?
What is difference between UNION & UNION ALL commands?
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
What is SQL Profiler?
What is SQL Server Agent?
What is an execution plan? When would you use it? How to you view the execution plan?
What is Policy Management?
What are Sparse Columns?
What is MERGE Statement?
What is Filtered Index?
Which are new data types introduced in SQL SERVER 2008?
What are synonyms?
What is EXCEPT clause?
How to handle errors in SQL Server 2008?
What is data compression?

1. The user input is not enclosed inside the single quotes. It is rather being passed as parameter to the SQL statement.
2. The function sp_executesql is being used to execute with the parameter list and the parameterized SQL statements.

1. Validate all input coming from the user on the server.
2. Avoid the use of dynamic SQL queries if there an alternate method is available.
3. Use parameterized stored procedure with embedded parameters.
4. Execute stored procedures using a safe interface such as Callable statements in JDBC or CommandObject in ADO.
5. Use a low privileged account to run the database.
6. Give proper roles and privileges to the stored procedure being used in the applications.

Hi..
This is the below sql statement mostly used to fomat data and time in SQL .

strcmd = New SqlCommand(query, con) strcmd.CommandTimeout = 120

<add connectionString="Data source=;database=;uid=g;pwd=; Connection Timeout=120;" name=" " />