0%
Loading ...

Category: MS SQL

  • Big Data / NoSql

    Big Data / NoSql

    MongoDB Tutorials Tutorial 1 (guru99) Terms Quick Reference show dbs – show all dababases show collections – show all collections in database. use [db name] – switch to the specific database. db.[collection].find() – find records in the collection. db.[collection].find({email:”ints-ints@mail.ru”}) – find specific value db.[collection].findOne() – find one record in the collection. db.[collection].findOne({email:”ints-ints@mail.ru”},{password:”12345″}) – find specific…

    Continue reading

  • Measure Ms Sql Execution time in resolution of milliseconds

    Measure Ms Sql Execution time in resolution of milliseconds

    DECLARE @t1 DATETIME; DECLARE @t2 DATETIME; SET @t1 = GETDATE(); /* Put here the query to measure */; SET @t2 = GETDATE(); SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;

    Continue reading

  • Temporary tables and Table variables – MsSQL

    Temporary tables and Table variables – MsSQL

    Temporary Tables The simple answer is yes you can. Let look at a simple CREATE TABLE statement: CREATE TABLE #Yaks ( YakID int, YakName char(30) ) Temporary tables are created in tempdb. If you run this query: CREATE TABLE #Yaks ( YakID int, YakName char(30) ) select name from tempdb..sysobjects where name like ‘#yak%’ drop…

    Continue reading

  • Ranking Functions

    Ranking Functions

    This article refers to AdventureWorks2014 of Microsoft. You can download AdventureWorks2014 DB here. At the following picture there are 4 ranking functions at MS SQL: 1. Row_number – Numbering each row. 2. Rank – According to example below when postal code change then the function displays the value of row_number at that position. 3. Dense_Rank –…

    Continue reading

  • How to install PhpMyAdmin on C9.IO

    How to install PhpMyAdmin on C9.IO

    here is a link of how to: https://docs.c9.io/docs/setting-up-phpmyadmin

    Continue reading

  • Wise Owl: Microsoft computer training videos

    Wise Owl: Microsoft computer training videos

    At the following link you will find Video Tutorials of: 1. C# 2. SQL 3. SSIS 4. SSRS http://www.wiseowl.co.uk/videos/

    Continue reading

  • How to search text at all Ms SQL tables, fields and all object?

    How to search text at all Ms SQL tables, fields and all object?

    1. Create the following Stored Procedure at the database you want to search: CREATE PROCEDURE SearchTables @Tablenames VARCHAR(500) ,@SearchStr NVARCHAR(60) ,@GenerateSQLOnly Bit = 0 AS /* Parameters and usage @Tablenames — Provide a single table name or multiple table name with comma seperated. If left blank , it will check for all the tables in…

    Continue reading

  • A Beginner’s Guide to SQL

    A Beginner’s Guide to SQL

    A great guide for learning SQL from the start, from Udemy: https://blog.udemy.com/beginners-guide-to-sql/ What Wikipedia has to say about UDemy: Udemy.com is a platform or marketplace for online learning. Unlike academic MOOC programs driven by traditional collegiate coursework, Udemy provides a platform for experts of any kind to create courses which can be offered to the public,…

    Continue reading

  • SSMS / Visual Studio Sql server database project

    SSMS / Visual Studio Sql server database project

    Database good practice Tools to use: SSMS – sql server management studio Visual Studio The following video is the best to describe the process How To Create a Database Deployment Project in 10 minutes In short: Using VisualStudio connect to Database. Right click on the DB and select create new project. At this point there…

    Continue reading

  • TECHNOLOGY TUTORIALS – kudvenkat

    TECHNOLOGY TUTORIALS – kudvenkat

    From kudvenkat: Over 12 years of experience, with Microsoft .NET technologies like ASP.NET, C#, SQL Server, AJAX, WCF, JQuery, SSIS, SSAS and SSRS. Currently working as a Technical Architect in central London. Love to share knowledge as I believe in “TO TEACH IS TO LEARN”. https://www.youtube.com/user/kudvenkat Got the opportunity to work on world’s largest e-commerce and…

    Continue reading

  • The Connection Strings Reference

    The Connection Strings Reference ConnectionStrings.com help developers connect software to data. It’s a straight to the point reference with connection strings, a knowledge base of articles and database connectivity content and a host of Q & A forums where developers help each other in finding solutions. http://www.connectionstrings.com/    

    Continue reading

  • MS SQL Server Editions

     MS SQL Server Editions Also include Express editions http://www.microsoft.com/en-us/server-cloud/products/sql-server-editions/sql-server-express.aspx

    Continue reading

  • SQL – How To Convert String To Datetime

    How To Convert String To Datetime http://www.sqlusa.com/bestpractices/datetimeconversion/

    Continue reading

  • How to find Object ID by name at SQL Server

    How to find Object ID by name at SQL Server Example of how to find ID of tables at database according to tables name: SELECT * FROM   sys.columns WHERE  object_id = Object_id(’employees’)

    Continue reading

  • What is SSAS? Analytic services learning resources

    What is SSAS? Analytic services learning resources

    Microsoft SQL Server Analysis Services, SSAS, is an online analytical processing (OLAP) and data mining tool inMicrosoft SQL Server. SSAS is used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Microsoft has included a number of services in SQL…

    Continue reading

  • All about SQL

    All about SQL

      Here you will find all you need regarding MS SQL: During the following Scripts, the following Databases will be used: 1. ACDB 2. hr_script 3. Northwind 4. AdventureWorks 2012 5. AdventureWorks 20146. AdventureWorks_2017 SQL Basics: 01 – SELECT-ALIAS-DISTINCT 02 – WHERE – ORDER BY – TOP 03 – SCALAR_FUNCTIONS 04 – GROUP BY – HAVING 05 -…

    Continue reading

  • Instant SQL Formatter

    Instant SQL Formatter

      This site will format your code and add colors a according to your source compiler. Here is an example: http://www.dpriver.com/pp/sqlformat.htm  

    Continue reading

  • Multiplication Table – Stored Procedure

    ALTER PROCEDURE [dbo].[Mulsp] @Size INT AS BEGIN DECLARE @X INT=2 DECLARE @Y INT=1 BEGIN try DROP TABLE [dbo].[multable] END try BEGIN catch END catch CREATE TABLE [dbo].[multable] ( c1 INT ) –create the rows  DECLARE @CN NVARCHAR(max)=” DECLARE @TSql NVARCHAR(max) WHILE @x <= @Size BEGIN SELECT @cn = ‘c’ + Cast(@x AS NVARCHAR) PRINT @cn SET @TSql=‘alter table MulTable add ‘ + @cn + ‘ int’ PRINT @Tsql EXEC sys.Sp_sqlexec @Tsql SET @x=@x + 1 END –at this point build each line query  SET @X=1 DECLARE @EachLineQuery NVARCHAR(max) WHILE @Y <= @Size BEGIN SET @EachLineQuery=‘insert into MulTable values (‘ WHILE @x < @Size BEGIN SET @EachLineQuery=@EachLineQuery + Cast(@x*@y AS NVARCHAR) + ‘,’ SET @x=@x + 1 END SET @EachLineQuery=@EachLineQuery + Cast(@x*@y AS NVARCHAR) + ‘)’ EXEC sys.Sp_sqlexec @EachLineQuery PRINT @EachLineQuery SET @EachLineQuery=” SET @y=@y + 1 SET @x=1 END SELECT * FROM   multable END

    Continue reading

  • w3schools.com – The world’s largest web development site

    w3schools.com – The world’s largest web development site

    Offline version mediafire.com – w3schools.com.rar thecrazyprogrammer.com – w3schools-offline-version-download-full-website   HTML HTML TutorialHTML Tag Reference CSS CSS TutorialCSS Reference JavaScript JavaScript TutorialJavaScript Reference SQL SQL TutorialSQL Reference PHP PHP TutorialPHP Reference JQuery JQuery TutorialJQuery Reference http://www.w3schools.com

    Continue reading

  • Temp table Local / Global

    — Create TEMP Table Local — Disappears when windows of query is closed.  CREATE TABLE #tmp_table  ( id   INT, NAME VARCHAR(20) ) SELECT 1      AS ID, ‘John’ AS NAME — Create And Insert Into Temp Table INSERT INTO #tmp_table SELECT * FROM   #tmp_table — Temp Table Global  CREATE TABLE ##tmp_global_table — Create TEMP Table   (      id   INT,      NAME VARCHAR(20)   ) INSERT INTO ##tmp_global_table VALUES      (1,’One’) SELECT * FROM   ##tmp_global_table 

    Continue reading

  • Dynamic SQL – Demo of EXEC and sp_executesql

      This demo s pretty self explained, you just copy and paste it into SQL Management studio: DECLARE @cmd NVARCHAR(30) = ‘SELECT * FROM Person.Address’ EXEC (@cmd) go — Using Dynamic SQL With Parameter DECLARE @cmd_param VARCHAR = 1 DECLARE @cmd VARCHAR(100) = N’Select * from employees where employeeid = ‘   + @cmd_param EXEC (@cmd) — DDL – Create Table DECLARE @cmd VARCHAR(100) SET @cmd = ‘Create table TBL (col1 int)’ EXEC (@cmd) SELECT * FROM   tbl; — DROP DROP TABLE tbl — sp_executesql — DECLARE @cmd NVARCHAR(100) SET @cmd = ‘SELECT * FROM Person.Address where AddressID = @1 and PostalCode = @2′ EXEC Sp_executesql   @cmd,   N’@1 int, @2 nvarchar(15)’,   1,   ‘98011’ go

    Continue reading

  • Can’t browse SDF files in Visual Studio 2013

    In Visual Studio 2013 you could double click a SDF database file within Solution Explorer and it would automatically open the database file and allow you to view the database tables, add tables, edit data etc. In Visual Studio 2013 this native support seems to have ‘fallen off’, double clicking an SDF file simply assumes it…

    Continue reading