Most voted "tsql" questions
T-SQL (Transact Structured Query Language) is the extension of the SQL functionality supported by the Sybase ASE and Microsoft SQL Server databases. There are differences between language versions, so questions about T-SQL should be marked appropriately with tag [sql-server] or [Sybase], and include the version as well.
Learn more…107 questions
Sort by count of
-
34
votes1
answer33629
viewsWhat is the difference between ISNULL and COALESCE in a survey?
I’m having second thoughts about using the ISNULL and COALESCE. Currently creating a query in SQL Server, I was left with doubt about ISNULL and COALESCE, I did some research and I was able to find…
-
33
votes8
answers48170
viewsHow to calculate a person’s age in SQL Server?
Suppose the table Pessoa and the countryside DataNascimento. In an SQL query, what is the best way to calculate a person’s age in integer format in the T-SQL language of SQL Server?…
-
32
votes5
answers41630
viewsMysql Limit Equivalent in SQL Server
Someone knows how to relate the differences between Mysql and SQL Server, including using LIMIT n,n? Or if you have a link that lists the differences would be nice. Example SELECT * FROM tabela…
-
19
votes1
answer11373
viewsHow to use a specific index in a SQL Server query?
I have a non-standard table in SQL Server installed on my PC which is fed monthly by Integration Services (SSIS) from a report in an Excel spreadsheet. I mean, I’m keeping track of a certain report.…
-
16
votes2
answers15027
viewsHow can I refer to the heaviest queries in SQL Server?
In the SQL Server database, how is it possible to know which queries have consumed the most resources of the database in its execution?
-
11
votes3
answers1559
viewsHow to condition the insertion of a record in SQL Server?
How can I condition the insertion of a record in a table in SQL Server using the T-SQL language considering the following rules: If the record to be inserted already exists in the table, only update…
-
10
votes4
answers40379
viewsHow to replace column text in SQL Server (tsql)?
I have a text column in SQL Server 2008 R2, which is filled with wrong typed text, in thousands of records. I would like to make a UPDATE replacing the wrong text with the correct one.…
-
8
votes3
answers3242
viewsHow to get the last date of a day of the week in a given month in SQL Server?
Using the T-SQL language of SQL Server, how to get, for example, the latest wednesday of the month of May of a given year?
-
8
votes1
answer9490
viewsWhat is the difference between TRUNCATE and DELETE + CHECKIDENT?
Both perform the same action (delete + reset the PK value), but in performance what is the difference between them? Example: When you have more records it is recommended to use which form? TRUNCATE:…
-
7
votes2
answers787
viewsHow to build a complete XML in T-SQL?
I am working on a project that returns an XML field, so far not bad. The problem arises when I have initial data coming from a query and intermediate data coming from another query. I tried to solve…
-
7
votes3
answers12428
viewsWhat’s the difference between Cast and Convert?
In a Transact-SQL (Ramalho Series) book it is said: CAST AND CONVERT It implicitly converts one expression of data types to another. CAST is synonymous with CONVERT. What do you mean by CAST is…
-
6
votes2
answers345
viewsSQL Server 2005 and line version control
In my current scenario, every Friday, I need to list all rows of a table that have been added or changed. In this table we have a field DataDeInclusao which obviously receives the date of inclusion…
-
6
votes1
answer221
viewsWhat is a T-SQL query?
In the Stack Exchange Data Explorer, specifically in the first line it says: Click the Compose Query button up there and start Typing your T-SQL query [...] That in free translation would be: Click…
-
6
votes1
answer662
viewsSQL Server Data Dictionary
Hello, I come from Oracle culture and want to learn a little SQL Server, I’m having difficulty finding the data dictionary of SQL Server, I’ve searched in other forums and found nothing. In Oracle…
-
5
votes5
answers36671
viewsHow to get the total character size of an SQL column?
I want to take the total size of characters that a column supports, example INT supports 10 characters. I tried to use DATALENGTH that does not return the number of characters, but the amount of…
-
5
votes3
answers2237
viewsChange column to indentity using T-SQL in SQL Server
I need to change an existing column in a table so that it stays with the property identity using T-SQL and then entering data into this table. The column already contains data and after this…
-
5
votes4
answers1917
viewsHome Time in SQL (Years, months and days)
I need to create a SQL function that returns the employee’s home time, example: years old, months and days So far I’ve managed to bring the years old and months, but I couldn’t get the logic of day…
-
5
votes2
answers32603
viewsHow to get the size of each table in the database?
Through a query, how can I get the disk space that the tables (in a database) are occupying? I can list all tables using sys.tables, but, and the size? SELECT NAME AS NomeTabela FROM sys.tables…
-
5
votes1
answer531
viewsPivot T-SQL Dynamic Sequential
Breaking my head here to create a proc using pivot for this transformation: There is the possibility? *Using sql server 2008R2 EDIT: CREATE TABLE #Teste5 (name VARCHAR(50),message…
-
4
votes3
answers882
viewsHow to insert an extended value into a table in a varbinary column?
A table has a varbinary(max) column, I need it to have a 400MB input of data in this column. How I create this data? The Insert statement has to have the data in full or I can use a loop or a…
-
4
votes2
answers2443
viewsT-SQL with case sensitive like
Is it possible to make a query in SQL Server 2008 with sensitive like? Actually, I’m looking in a column with a list of acronyms, which represent system flags, which differentiate uppercase and…
-
4
votes1
answer127
viewsBest practices when using Sqlcommand? Text or Storedprocedure?
I’m wondering which of the following approaches is best? 1 - Add a file *.sql as Resource, then run the Sqlcommand with the CommandType.Text. 2 - Add a Store Procedure to the Database, then run the…
-
4
votes2
answers81
viewsSimple SQL query
I have a proc that gets a product ID. If it gets NULL, then it should list all products. If it gets the specific ID, it should list only that product. There is some way to do this without having to…
-
4
votes1
answer3721
viewsInsert X amount of characters in SQL Server field
Hello, I have two banks BDMCOM1 and BDMCOM1_V3_ALEA, both of which have a table called Saldodeproducts and the field called Codigodoproduto, in the table of the bank BDMCOM1, Codigodoproduto has 5…
-
4
votes5
answers12287
viewsHow to perform SQL concatenation
I need to concatenate year and month. How could I do that? select cast(year(orderdate) as int) + ' ' + cast(month(OrderDate)as int) as Year_Month From sales.SalesOrderHeader…
-
4
votes3
answers158
viewsDoubt about sql query
I have a question. I believe it is nothing complex, but I could not solve. For example, I have this query: select * from Paciente where ClinicaID = 3 Your result would be these 3 records:…
-
4
votes1
answer385
viewsHow to PIVOT a column by concatenating strings into Sqlserver
Hello, I’m making a query in a database to identify all the columns that are primary key's of the tables of a database and also identify whether or not they are identity. For this I use the query…
-
3
votes2
answers2614
viewsHow to get the index creation date in SQL Server?
By SQL Server Management Studio I did not find in the properties of an index its creation date. How can I get the creation date of a specific index by just referring by name?
-
3
votes1
answer152
viewsCatch max value inside a while that is inside a cursor
My doubt is the following: I have my cursor for the interaction line by line and within it I have one while for horizontal interaction. I am trying to popular a table whose PK does not have…
-
3
votes1
answer199
viewsHow to launch custom T-SQL exception?
I have a Tigger for CPF validation, and I want to make an exception if the reported CPF is invalid. About the release of exceptions I’ve been reading this publishing, but in the author’s examples he…
-
3
votes1
answer193
viewsShow warning message without exception
I would like to display an alert message to the user without causing exception in the Database. I am using SQL Server. So I cannot use RAISERROR, nor PRINT, because it is not shown to the user.…
-
3
votes1
answer85
viewsIs there a loop loop behind the code of a CTE?
I’m racking my brain to understand how such a simple command can generate values from 1 to 100. Is there a loop behind a CTE? For in the code below there is a loop WHERE and not a WHILE. WITH…
-
3
votes1
answer47
viewsHow to concatenate columns identified by surname?
I have the following appointment: select case when datediff(...) > 0 then month(tblData.data1) when day(tblData.data2) > 10 then month(tblData.data2) when month(tblData.data2) = 1 then 12 else…
-
2
votes1
answer593
viewsHow to use LEAD and LAG using a condition
My question is, if I can use LAG and LEAD, using a condition in the query, for example: I have the spine cep.. I want to take the value of the next row, and the value of the previous row of that…
-
2
votes1
answer46
viewsReplace in Date inside a FOR
I want to return the last 12 months, the day should come as 01 and need month and year. ex: 2014-12-01 2014-11-01 NOTE: I put yyyy-mm-dd only by a pattern of mine. I’m doing like this: DECLARE…
-
2
votes1
answer209
viewsIF in Storedprocedure within a select, remove sp_executesql @query
I have a Storedprocedure that unfortunately only because of an IF has ceased to be a select and turned everything into a VARCHAR statement and then I run it, losing one of the main benefits of MSSQL…
-
2
votes3
answers2032
viewsSelect from the last records sorted by 1 field
I have the following table structure The goal is to get the last 3 oldest records (oldest date) ordered by Name. Today I’m using subselect: Select * from (Select top 3 from TB order by Data desc) A…
-
2
votes2
answers2828
viewsCURSOR vs table variable type
I’m facing one of those situations where I need to perform an action for each line resulting from a query. This way I have two options, use a Cursor or a Table Variable, but the two seem very…
-
2
votes5
answers1818
viewsGroup records per day
I have the following table ID | Início | Fim | Horas 333 | 01/01/2017 | 03/01/2017 | 5 333 | 02/01/2017 | 05/01/2017 | 1 333 | 05/01/2017 | 07/01/2017 | 3 333 | 01/01/2017 | 07/01/2017 | 6 I need a…
-
2
votes1
answer6162
viewsTraverse row by row of a table
I have a temporary table that I fill with the result of a particular query. I need to go through the data of this temporary table, row by row, and take certain fields and insert them into another…
-
2
votes1
answer48
viewsOnly spend a year in a proc of sql server 2014
I made a filter in a PROC of my own and I’m not getting any results. Running proc did not give any error, but it does not bring any result and I do not know if it is correct. The colleague said I…
-
2
votes2
answers625
viewsQuery SQL - CASE with LEFT JOIN
I need to create a query that returns all active students and their grades for each subject. If the student has no grade for a particular subject should be returned the student, the subject and in…
-
2
votes1
answer674
viewsAssign a variable a Dynamic Date?
My goal is to create a SQL Server, which has by default two variables, one with the initial date of (hoje - 3 anos) and a final date equal to hoje. I already use for other operations (for example a…
-
2
votes1
answer197
viewsSQL SERVER odd line queries
I need to create query script that returns from the table below only its odd lines, ordered upwards: DECLARE @table TABLE (coluna1 varchar(50)) INSERT INTO @table VALUES…
-
2
votes2
answers1443
viewsGROUP BY with 2 tables
I need to make a select with JOIN in two tables but I am not able to group to not repeat the lines. Follows the expression SQL: SELECT Andamento.NumeroProcesso, Andamento.Chave, Andamento.Codigo,…
-
2
votes1
answer142
viewsRatio 1:N check date, difference 6 month
I am developing a SQL Server Database, which lists two tables: Where a process has N movements, I need to return the following data: all Process that have the column Irrecuperavel(int) = 1 and that…
-
2
votes2
answers7816
viewsConsult in all tables of the database
It is possible to make a query in all tables containing the same column their respective values? For example, I have the Bank called SGE, it has 230 tables, and all these tables have the column…
-
2
votes0
answers283
viewsGet Street and number in a string in an irregular expression
I have a table that I have all the address in one column, but it is irregular. Below are some of the occurrences: BR 459 KM 153 RODOVIA ITAJUBA MF KM 05 Rodovia MG353 2Km 55 RUA 37 QUADRA 4 LOJA 611…
-
2
votes1
answer45
viewsHow to sum the first 2 occurrences of a column using a key as reference in Sqlserver
Hello, I have the following table: create table events ( type int not null, value int not null, time datetime unique(type, time) ); Where in the same I have the following records:…
-
2
votes1
answer671
viewsSQL save and return Base64 image
I am saving an image in SQL Server using the varbinary field(max). However, when I run the query to return to the front, I can only return by converting the field to sweep. My question is how to…